Order By Date AND Time Functions
I have two fields in my database...
DATE > YYYY-MM-DD
TIME > HH:MM:SS
I want to order by date AND time, how can i go about this without making a TIMESTAMP table using the UNIX timestamp??
View Complete Forum Thread with Replies
Related Forum Messages:
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 !
Using Time Functions Within A Query
I'm currently working on a script that searches through a database of restauarant information. One of the things it does is allow the user to search only for restaurants that are open at the time of the search, however i get errors when trying to implement this. Here is the query im using:
View Replies !
Aggregate Functions + ORDER BY
I want to find the sum of some rows, which I can do fine. Then, I want to order by that sum. Is this possible? I know ms access does it the following way, which I've read will work with mysql also: SELECT nav_result_id, SUM(timingpoints) AS TimingPoints FROM leg_results GROUP BY nav_result_id ORDER BY SUM(timingpoints) However, I get this error message: "#HY000Invalid Use of Group Function" Can anyone tell me why, and possibly give me a hint how to correct my syntax to prevent this, and make it work
View Replies !
Select Date Date Functions
i do very elementary sql and am having trouble with SELECT i have a database of the chinese years, they start at different days and months so what i did was create a table called year: year_id (int) year_name (var) ex:RAT year_starts (date) ex: 1917-01-23 year_ends (date) ex: 1918-02-10 so when i want to find out what year somebody was born ($birthdate), i wanted the database to do something like: "SELECT * FROM year WHERE year.year_starts >= $birthdate AND year.year_end <= $birthdate" I can get it to select from either year.year_starts >= $birthdate (alone) or year.year_end <= $birthdate
View Replies !
Date Functions
I've read the Data and Time functions docs and any other relevant docs that I could find, plus tried googling, but I don't see the kind of examples I'm looking for. Can anyone see whats wrong with this mysql query? Code: mysql> SELECT id, file, alias, MONTH(mydate) AS thismonth, DAY(mydate) AS thisday, YEAR(mydate) AS thisyear, catagory FROM news WHERE id=6; ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '(mydate) AS thisday, YEAR(mydate) AS thisyear, catagory FROM ne the following query works so I don't know why this one MONTH/DAY/YEAR one doesn't. Code: SELECT id, file, alias, DATE_FORMAT(mydate, '%m.%d.%y') AS newdate, catagory FROM news WHERE catagory = 'special' ORDER BY mydate DESC
View Replies !
Date Functions For Days
This time i looked on the mysql site first, found the answer but it doesnt work I am trying to get the days between two dates with the following: SELECT DATEDIFF(now(), date_joined) AS 'joined', DATEDIFF(now(), trial_end_date) AS 'trial' FROM JSPCustomers WHERE username like 'qwerty' And i get the folllowing error: Syntax error or access violation: You have an error in your SQL syntax near '(now(), date_joined) AS 'joined', DATEDIFF(now(), trial_end_date) AS 'trial' FRO' at line 1
View Replies !
Date Functions, Grouping
I have a table in witch one column is of type "date", the rest of the columns are id, name etc. I would like to select everything from this table, and then group it according to their dates in a special way. The date column may look like this (when sorted): 2006-01-01 2006-01-02 2006-01-03 2006-01-05 2006-02-02 etc If there is date diff greater than 3 days between two "rows", I would like them to be treated as two different "groups" when the result is displayed.
View Replies !
Date Functions In Group By
I've got a table with visitor statistics where one of the fields is a timestamp generated with UNIX_TIMESTAMP(). Each hit on the website is recorded as a row. Now i'd like to get the busiest day, the day with the most hits. Is this anyway possible with the Date and Time functions from Mysql? GROUP BY DAYOFYEAR(ts) or something similar?
View Replies !
How To, Mysql Date Functions
I have a voting system and I want to limit how often a user can cast votes, like make them wait 20 seconds between casting votes. I can do this with php date and mktime and came across the mysql date functions. I was wondering if there was a way to achieve the delay using something like "interval" or "extract" to make sure 20 seconds has passed but I am not really making any sense of the documentation. It needs to have the ability to return true if it is todays date and another true event if 20 seconds has passed. my timer table has username and timestamp.
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 !
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 !
Order By Time
I have three colums related to time in a table.(There are many other fields) 1. intHrs - To store Hour value of time 2. intMins - To store Minute value of time 3. vchAmPm - To store wheather it is AM or PM I want to select the rows order by time with 24 hour format something like SELECT * FROM table WHERE condition ORDER BY ........
View Replies !
ORDER BY :: Time
The column duration in the reports table is type = time. The following query produces the results below it. My question is why does the 9th entry appear where it does? Or better yet, How can I get it to sort "correctly?" That's "correctly" by my understanding, not necessarily SQL's.....
View Replies !
Order By Time Problem
Im have a Time field that im using to sort the records chronologically. Im storing the 12pm-24hr time as 00hr, since there isnt a real time of 24hr. So, the problem is that when i Order the info by the time field the 00hr records appear at the start of the list instead that at the end (after the 11:59pm). I want them at the end because im using them as the End of the day, not the start of one. Im ordering the field with this: ORDER BY coalesce(time, 24) What can i do to fix this? Should i be storing the 12pm as 24hr? (i tried that, but the order by showed another value for the 24hr time)
View Replies !
Truncated Time Values Using TIMEDIFF With ORDER BY Query
I'm using the following query: SELECT glider, timestamp, TIMEDIFF(timestamp, UTC_TIMESTAMP()) AS last_contact FROM surfacings INNER JOIN (SELECT MAX(timestamp) AS most_recent FROM surfacings GROUP BY glider) AS tmp WHERE surfacings.timestamp = tmp.most_recent; to calculate the amount of time that has elapsed since the last inserted timestamp for each glider. Everything works fine: ....
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 !
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 !
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 !
ORDER BY U.S DATE
I need to ORDER my query by US date. The date format is set to mm.dd.yy Is this possible or does my date have to be in mm/dd/yy format. If it is possible what would the code be.I have PHP Code: SELECT * from $getrow ORDER BY YEAR ('date') DESC MONTH ('date') DESC, DAYOFMONTH ('date') DESC" Where date is my column, I am aware that date is a function in sql and I need to alter the name of this column.
View Replies !
SQL Order By Date
Ive an ODBC link from an access DB table to MYSql DB table, and the only way I could get the dates in access to upload correctly was to make the MYSql date field a VARCHAR. This field now contains the value DD/MM/YYYY, but its a text value. When trying to do an order by date, as the field is a text field, its obviously only ordering by DD, and the ordering is not correct. So my question is simply, how do I format the varchar representation of the date, into something like YYYYMMDD, so that I can order it correctly?
View Replies !
SQL Can't Order By Date
The script SELECT * FROM tutorials, scripts ORDER BY date ASC LIMIT 6 Will not work...i don't quite no why... I can however order by with each table one by one, but I'm trying to show tut's and script all at the same time...and all order by date. I get this error: Column: 'date' in order clause is ambiguous
View Replies !
Date Order
i'm using this code: $postSql = mysql_query("SELECT *, DATE_FORMAT(date, "%F %d%S, %Y %h:%i %A") FROM `post` WHERE `threadid` = '$id' ORDER BY `date` ASC"); but it orders the items by month first, then the date/time This is for a forum so the order needs to be oldest to newest, any ideas?
View Replies !
Indexing Date / Time Fields.
If we have a date/time field and are doing frequent queries WHERE {date/time field} BETWEEN TimeA AND TimeB Does it make sense, query speed wise, to create an index on the date/time field? The reason I ask is that TimeA and TimeB are significant down to seconds. My *assumption* is that with a large amount of data scatter very few records will contain duplicate date/time points. And that under these circumstances, indexing will be of little value since every date/time point will need to be examined in detail. Date/time fields are actually floating point numbers after all. In other words, the assumption I am making is that if most of the data in a field is unique, then indexing will yield little or no query performance benefit. Is my "assumption" valid? Would an index on the date/time field speed up this type of query?
View Replies !
|
TRACKER
