Query To Process Date/time Stamps To Delineate And Report On User Sessions
I should read up on that would be used to create a query which takes data formatted like the following: ....
View Complete Forum Thread with Replies
Related Forum Messages:
Adding User Defined Date Range For Report
Hello I have a very simple query that list customer info within a date range. I would like to have a interactive form that would allow the customer to choose between two dates and query the DB for information between the two dates entered. it is coded like (WHERE `date_purchased` > '2005-08-01 00:00:00' AND `date_purchased` < '2005-09-01 00:00:00') below is the code but I need help or guidence on how to get the customer input into the query instead of the hard coded area that is there now....
View Replies !
How To Retrieve Mean Time Between Time Stamps
I have a table that stores failure information of external application. The time stamp of every failure event is stored. Now I'd like to make some system performance & reliability statistics calculations based on time stamps in certain recordset; mean time between failure MTBF, down time DT, etc. Example: 2007-09-27 12:23:52 2007-09-27 08:50:23 2007-09-26 23:31:34 2007-09-26 21:45:33 Here the times between are (hours:mins): 3:33, 9:18, 1:46 and MTBF=4:52 Any effective possibility to make this example within a single query - or a procedure is needed?
View Replies !
TIMESTAMP :: Query By Time And Date
I have a field in my table that holds a timestamp. Below is a pseudo-query to describe what I would like to do. SELECT * FROM `mytable` WHERE `saved_date` <= `6/24/05 10:22:34 AM` `saved_date` is the field holding a TIMESTAMP. If anyone could give me the query that would actually do what I am trying to do above,
View Replies !
Report Query Load
Is there a way in PHP to have the MYSQL report the load that is placing on the mysql server. I am trying to optimize some queries and I was hoping the server could give me some feedback so I know if I'm going in the right direction.
View Replies !
Report Of Query Execution Times
Does mysql have a tool to record and report a list of all queries executed and how long it took to execute each query? If not can anyone suggest a tool I can use to do this? I've heard of mysql query profiler but does it capture queries from all sessions?
View Replies !
Report Writing Query Problem (joins Again...)
Hola database type folks, I have been trying to get this right for sometime but now must admit defeat I have 3 tables artists -> holds details on the artists card_gallery -> hold details of card available related to artists via artist_name scart -> shopping basket related to card_gallery via card_image_id I am writing a reporting facility that allows the administrator to view all the artists in a table along with their number of cards online and cards sold figures. Currently I have something like select card_gallery.artist_name as cg_an, count(card_gallery.artist_name) as cg_ca, count(scart.card_image_id) as s_ci, from card_gallery left join scart on card_gallery.card_gallery_id=scart.card_image_id group by card_gallery.artist_name but that doesnt give the correct number of cards sold. Cards sold are registered in the scart table by setting the processed field to 1 Ideally I would like to be able to view all the artists on one table with the number of cards online and the number sold even if it's 0
View Replies !
PHP/MySQL Sorting By Date & Time (using Non Military Time)
This is probably a simple issue but I have searched online and can't find an answer. I am using PHP/MySQL and I guess the most intuitive would be to have three select boxes containing HOUR / MINUTE / AM,PM option. Then store the time into mysql using there functions so I can output the data sorting them all by date and time. All the examples I have seen are for military time only but I'm sure there has got to be a simple mysql function or php function that converts non military to military and a formatting function to display with the AM / PM. But I have not found it.
View Replies !
2 Query Process Into 1?
I'm trying to access data from 2 separate tables using the following query: SELECT items.productid, stores.id FROM items, stores WHERE items.storeid = stores.id AND stores.state = 'any given state' I need to retrieve the stores.id regardless of if there are any items.productid's returned. Right now, I have to run this query, check to see if any results are returned, and if not run a regular query to get the store id for the given state. Is there any way to modify this query to return the stores.id no matter what (there will only be 1) to save myself the extra query?
View Replies !
Associating A Query With A Process
Is it possible to find out which MySQL query is associated with a particular server process? Some mystery query on our server is causing massive CPU usage issues that last for upwards of 5-10 minutes. Unfortunately this query is not showing up in the MySQL slow query log. In fact, the problem usually doesn't start until after all the PHP code that utilitizes the queries is finished executing. That's usually the point where MySQL goes postal on the system resources.
View Replies !
Date-time Overlaps Another Time
had a time question in mysql... i have a start and end time for a meeting table and i am comparing whether a new meeting conflicts with another meeting in same room on the same date. i was wondering if i could check whether the 'date-time duration' in anyway overlaps another meetings date-time duration. visualized below... ............|____compareToThisMeeting__| .....|_________meetingNewOverlaps__________| i was doing something dumb before i realized i needed soemthing more complex (checking whether the start time of new meeting was 'BETWEEN' the start and end times of a meeting and whether the end time time is as well but i forgot the duration/middle value hehe). (using asp) and just returning booleans with a mySQL SELECT statement is what i am looking for like... //within a loop in asp for meetings on same date/room "SELECT '"+ year+"-"+month +"-"+day +" " + startHourString +":" + startMinuteString +":00''"+ " BETWEEN '"+ thisMeetingStart +"' AND '"+ thisMeetingEnd +"'"; (this doesnt cover the 'middle' overlap areas though)
View Replies !
Date Field, Time Field, Sort By Date And Time
I have a MySQL date field (e.g 13/12/2006) and a MySQL time field (e.g.13:00) in the same row. I'd like to join these two fields to make a date/time field (e.g. 13:00 13/12/2006) on the fly and to be able to sort the query results on the resulting field.
View Replies !
Php Sessions ...
I am wanting to store php sessions in the mysql database. What are the repracutions of this? Are there many writes and reads or only when it has to? I mean if we have 1000 users on the site at once will it do a 1000 reads every page load? Is it wise to store them in there or will it tax the server to much. I am basically interested in this method for a load balanced server, is it wise to use it?
View Replies !
Sessions On The Same Server?
I have a landing page. Lets call it a "mall". It is going to be connected to different online stores. Users will go to the landing page "mall" and click on a store that interests them. I want an id number from the mall they came from to go to the store and follow it through the checkout process. I want that id number to store into the database of the online store. Everything is going to be on the same domain and server and I am only using one store per product/ category. How would I get started on this?
View Replies !
First Time User Need Help Connecting To Local Db
I've worked a bit with creating and connecting to databases online using PHPAdmin but I'm trying to set up a local environment and I know NOTHING about that. I installed MySQL 5.0 yesterday and it seems to be OK. At least I can bring up the command line client and get the mysql prompt. (I'm on XP Pro, btw.) I really don't know what to do next, though. I've built my db model in DBDesigner4 and supposedly, you can use that program to create and synch your database. I tried doing that and I keep getting the invalid user name/pw error message. I don't know if I have to create an empty db first or if there's just something wrong with the user name/pw. I'm using the pw I created when I set up MySQL and using "root" as the username. Anyone have any idea what I might be doing wrong? Do I need to create the db on my machine first? If so, how do I do that? Any ideas why I keep getting the invalid username/pw message?
View Replies !
After Date And After Time
I would like to make a memo script that once you have entered the date and time and note in a memo intto the database the memo is displayed once both date and time have passed. I dont want the memo to disappear until it is told to. I have an idea how to make it disappear, but I dont know what the syntax is for the sql for AFTER the date has passsed and AFTER the time has passed.
View Replies !
Time AND Date
i want to store the time in my field typ time. i DON`T want to store date and time in one field. in the field i see the time with an admin tool, but when i want to put out the value in an ASP page i get time AND date .how can i configure the field typ to store ONLY the time ?
View Replies !
About Date And Time
i have created a table using the following code: ------------------------------------------------------------------------------ create table tt(name varchar(10), enddatetime timestamp, startdatetime timestamp, difference timestamps); ------------------------------------------------------------------------------ after i have created, i insert the following into it: ------------------------------------------------------------------------------ insert into tt values('bob',null,'2006-10-1 12:12:12',null); ------------------------------------------------------------------------------ and i want to find the difference between the enddatetime and the startdatetime. so i tried the following: ----------------------------------------------------------------------------- select enddatetime,startdatetime, sec_to_time(unix_timestamp(enddatetime)-unix_timestamp(startdatetime)) as difference from tt; ------------------------------------------------------------------------------ but the value came out from the difference column was xxxxx:xx:xx *(where x is an integer). as you can see, the year,month,date and the hour are all mix up together(xxxxx). so i was wondering whether my code or my variable was wrong?? and by the way, how can i show the difference in terms of seconds?? like converting the enddatetime and the startdatetime into seconds and substract them together?
View Replies !
Date Time
what I want to do is very simple however it seems I'm having trouble figuring out how :D I got a date_time field in a table. I want to retreive all the records that match yesteday. Something like: select * from table where datetimefield = now() - interval 1 day HOWEVER because it's a date_time field (and I can't split it in 2 fields for other reasons), the above query won't return anything. Seems I could do a select * from table where datetimefield between yesterday at 00:00:01 and yesterday at 23:59:59 but I can't find the functions for that.
View Replies !
Date & Time
i want to ast if i insert into table the value of now() in a datetime formated column does tha value it inserts if from my pc or from the server?
View Replies !
UTC Date/time
A data file I am trying to import has the date/time field in UTC format: YYYYMMDD HHMMSS+0. When I define my column field as datetime and import I only see zeros in all positions. Can MySQL import data that are in the UTC format and if so what data type or command should I use?
View Replies !
SQL Date Time
I've been having trouble trying to figure out the best way to select only the rows with timestamps within the last X amount of hours, days, weeks, months. So I guess my SQL statement in plain english would be: Give me all the rows that have a timestamp between NOW and X days/months ago and count how many times a row was submitted per hour/day/week/month within those rows. I'm pretty sure this is possible but I can't seem to wrap my brain around it.
View Replies !
Delete Inactive Sessions Every 60 Seconds?
I have a session table, where I stored user sessions in the database in a memory table. My question is, performance wise, would it be smarter to delete inactive sessions every 60 seconds via client http requests, or would it be wiser to select active sessions via a select that uses a where clause and searches a timestamp index column for active sessions, and then a cron job is scheduled to delete every 30 minutes. Which of these would be the better choice? On each page request I am going to have to select the number of sessions that exist and a second query that displays the active users. So if I go with my second choice, I will have to include a WHERE clause that scans the index on both those queries. But if I am issuing a delete every 60 seconds, then I won't have to do that, but then the delete is taking place every 60 seconds on a client request.
View Replies !
Tracking The Total Time Each Day A User Spends On Site
My DB is currently setup like this: SQL Code: id   userid   timecheck      visit_type 1    255   1205896013     IN 2    255   1205896017     refresh 3    255   1205896025     refresh 2    255   1205896150     IN I basically need to grab the difference between the time from the first IN of the day to the row marked refresh just before the next in, and then repeat wherever the value in the timecheck column = the same day. PHP Code: $sql= "select * from timetable where timecheck = '".date("M d, Y", $row['timecheck'])' and visit_type between 'IN' and 'refresh'";
View Replies !
Design For Stock Items And Checkin/checkout Process (was "Query Help")
I'm currently in the process of trying to bodge together a simple(ish) stock system to be used on a company intranet but I've ground to a halt with a particular problem. Using a MySQL database I've got tables with 11 fields, including 'Quantity' (self explanatory) and BookOut. Now this BookOut field contains information on the user who has booked out an item including the users name, the date/time etc The problem I have is this. If an item has a quantity of 7 and a user books out 1, then his/her info is stored in BookOut but when another user books out another 1 of said item at a later stage then the previous BookOut info is overwritten. Is there a way in that everytime another item is booked out that a new line is created within a specific field? } if(!sizeof($error)){ $update_SQL = "Update joystick Set BookOut = '" . mysql_real_escape_string($BookOut) . "', PCserial = '" . mysql_real_escape_string($PCserial) . "', Quantity = Quantity - " . $Quantity . ", MachineNo = '" . mysql_real_escape_string($MachineNo) . "', Date = now() WHERE ID = " . $ID; if(! @mysql_query($update_SQL)){ $error[] = "Update SQL failed"; } The above is the query I am currently using. I'm not totally proficient with .php and have already had a lot of help getting this far.
View Replies !
Time And Date Fields
I have a form which insert data into a MYSQL table. In this MYSQL table I have a field called "DateEntered". Is it possible to have this field automatically updated with the current date when the form is submitted. What do I need to do in Mysql or on the html form?
View Replies !
Mysql Date Time
This is probably an easy one but i cannot seem to solve it, what I have is a database with a lastlogin datetime field in the form: 2005-01-17 00:14:30 I need to do a select on each entry to find the number of rows later than the date of the current row datetime, as I have users that log log in at different times. I tried something like in a loop but this does not work select count(*) as total from user_table where (NOW()>DATE_ADD(LastLogin, INTERVAL 0 DAY)) and iUserId != '$iUserId'
View Replies !
ORDER BY Date And Time
I am using php to extract variables from a MYSQL database that get loaded into a calendar application. Most everything works great. The only problem occurs when more than one event occurs on the same day. The event date and time are stored as separate variables in the database, thereby making it difficult to ORDER BY date and time. The result is that all of my events appear on the proper day, but they do not appear in chronological order. Is there a way to order by date and time? I currently have the following: Code: $SQL_Query = "SELECT eventID, eventDate, startTime, endTime, title, description, linkName, hyperlink FROM events ORDER BY eventDate";
View Replies !
Select Between Date And Time
I have field date and time respectively. I want to select date between (date1 and time1 > 17) and (date2 < 17) I tried two ways but doesn't work, please see code below. SELECT * from purchases_suppliers WHERE (date_invoice >= '2009/02/25' and time_invoice > 17) AND (date_invoice <= '2009/02/27' AND time_invoice < 17) SELECT * from purchases_suppliers WHERE date_invoice between ('2009/02/25' ANd time_invoice > 17) AND ('2009/02/27' AND time_invoice < 17 )
View Replies !
Server Date Time
I have mysql server running in one machine. I have a query in my program 'SELECT CURRENT_TIME', it will retreive the system time. I installed my program in the client machine and it is connecting to the mysql server. If i execute the SELECT CURRENT_TIME, it is giving the client machine date and time. How can i retreive the server machine means on which mysql running, its date and time from the client machines.
View Replies !
Subtract Date And Time
is there a way that I can subtract date/time in MySQL? For example I have this dates and I want to know the number of hours between this dates: I want to perform 2006-12-05 22:00 - 2006-12-07 08:00. Can this be possible in a mysql query? $date1= '2006-12-05 22:00'; $date2= '2006-12-07 08:00';
View Replies !
Sort Date And Time
I have a simple question about sorting records based on seperate date and time fields. Take the following example query: SELECT some_id FROM datatable WHERE date >= '2007-01-01' and time >= '05:00:00' This returns all of the records with a date greater than 2007-01-01 and then reduces the records to only those with a time greater than 5:00. I want all records after 2007-01-01 5:00.
View Replies !
DATE Format :: How To Put Time?
I am using PHP myadmin to setup my mySQL database. The format I choose to store my date text field was DATETIME. How can I choose a different format for the DATE? All I want is a: 12-7-2007 format and don't want to put in a time but it's expecting the time as well.
View Replies !
Need To Include Time With The Date
I have a table called 'date_accessed' ... When I query that table for the date, how do I include the time along with it? Here is my query: SELECT user_agent, ipaddress, date_accessed FROM access_tracker;
View Replies !
Date/Time Format From CSV
I have a rather large csv file that includes many dates that I would like to import into MySQL. The problem that I am having is that the dates are DD/MM/YYYY HH:MM:SS, and MySQL does not accept them in its YYYY-MM-DD HH:MM:SS format. Any ideas how to convert this properly?
View Replies !
Date/Time To The 6th Microsecond
I am trying to import a field in a table on a server to which I am connected via ODBC. The field is in the following format 2008-08-11-00.00.00.000000 I would like for the format of the field locally to be the same but when I import it only goes to the seconds part of the format. Even when I try to import it as a text with 50 characters it doesn't help. Is there a way for the imported file to be in the exact same date/time format?
View Replies !
Date/time Function
I need to write a query which looks for certain criteria, then returns all rows which occur 0-30 seconds after. Can you please help with a generic query to accomplish this?
View Replies !
Date/Time Issue
I am very new to MySQL. I have used other databases before so I am trying to figure out how to do things I have done before. I want to create a table with a column of type DATE. When a row is inserted in the column I want the date the transaction took place to be inserted as the default in the column. I have tried a couple of things, but everytime I execute anything like: SELECT CURDAT (); MySQL reports Error 1146 Table mysql.proc doesn't exist. What have I not setup correctly? Same thing happens when I execute: SELECT VERSION (); I am using version max-5.0.18 on a Linux system.
View Replies !
Conditionals With Date/time...
I am trying to figure out how to use conditionals with date/time using PHP with MySQL. For instance i want a time limit on my site to restrict people clicking a link within so many minutes. So for example if they click the link once thats fine, but if they do it again say within the next 5 minutes i want one output, but if they wait the alotted 5 mins then another output.
View Replies !
Date Time Question
first post: Hello, I'm trying to show what users are currently logged in on my site. what I've done is update a timestamp everytime they hit a page. what i want to do is select users where the current time Now() is within 15 minutes of the timestamp, to see if any users are on. but I'm not sure how to do this. any ideas? examples would be grate, I haven't done any asp in a long time. thanks in advance for you time! -Ken second post: ok, i've tried this, SQL = "Select user from accounts WHERE DateDiff(n, Now(), date) < 15 ORDER BY user" but i'm getting an error: ADODB.Recordset.1 error '80004005' SQLState: 42000 Native Error Code: 1064 [TCX][MyODBC]You have an error in your SQL syntax near '(n, Now(), date) < 15 ORDER BY user' at line 1 /admin/date.asp, line 6 also i'm using MySQL as the data base.
View Replies !
Database Sessions Lead To Clogged Connections
I had a system where when a user logs in, a CURL request is sent to the other http server which creates its own php session with the POST data (lets avoid all the security mumbo jumbo). This worked, but when one session expired on one server, then the other server had no idea of it. So I switched to putting sessions in my database for a cross-server solution (the two http servers share the same database). This solved the issue of mirrored session data, but then I started to get an excess amount of mysql connections (I'm using persistent connections) and as a result my website crashed. No matter by how much I increased the max_connections, the database server kept on reaching its limit (not a performance limit, but a connection limit). OK so now I've switched back to PHP sessions, but I made the session directory available to the other http server with NFS. This solution works fine.
View Replies !
|
TRACKER
