[Resolved] timestamp, date_format and ORDER BY

etoast

hello
i am trying to order the timestamp field to show records in order using the following query:

$result = mysql_query("SELECT name, date_format(DATE_ADD(time, 
INTERVAL 6 HOUR), '%M %e, %Y at %H:%i') AS time FROM table ORDER BY time ASC") or die (mysql_error());

however the date_format seems to be messing it up. they order by month but not full date, so rather than in order they look like this:

november 01
november 02
november 05
october 27

does anyone know how to get around this?
thank you.

tekky

$result = mysql_query("SELECT name, date_format(DATE_ADD(time, 
INTERVAL 6 HOUR), '%M %e, %Y at %H:%i') AS ftime, time FROM table ORDER BY time ASC") or die (mysql_error());

you are referencing time to a string not the timestamp column... change your AS time to AS ftime and ad ,time to the select and you should be good 😉

etoast

thanks, that does order them correctly but the formatting goes out of the window. it just returns the timestamp.

what is AS ftime, time?

tekky

Originally posted by etoast
thanks, that does order them correctly but the formatting goes out of the window. it just returns the timestamp.

what is AS ftime, time?

alright, to make it work correctly with your script you will need to do 1 of 2 things

replace all instances of $row['time'] with $row['ftime'] OR you can do this

replace the 'AS ftime, time' with 'AS time, tstamp' and then ORDER BY tstamp

AS ftime just renames the formatted date to ftime when you do mysql_fetch_assoc() and the ,time just has it also pull the time column out of the DB

etoast

thank you!