order by question with time

[mikefrederick]

i have a field in a table that contains times of the day in the format 11:00 am, 10:20 pm, etc. If I order by that field, then the pm dates will still come up before the am ones. For example, since 10:20 is less than 11 it will come before 11, but I need it to come after since it is actually later in the day. Is there anyway to do this without breaking the am/pm part into another field?

[papaface]

does

order by desc

or

order by asc

in your SQL make a difference?

[mikefrederick]

sure, it will reverse the order but it does not make the field sensitive to am or pm.

[mikefrederick]

anyone?

[lemmin]

I assume that the field is not in a date/time format? Is it an option to change the formatting of the field?

[Jeremysr]

That seems like something impossible to do in SQL, but I'm not sure.

 

It's a good idea to store data in ways that a computer can work with better. I think you should store it as 24-hour time, or even as a number representing the number of seconds past midnight. Then when displaying it on the screen, you can format better it for a human to read.

[PFMaBiSmAd]

To do this the proper way, you need a TIME type field and then format the field in your select query the way you want to output it using the mysql TIME_FORMAT() function.

[schilly]

sounds like a string field. if you are storing time, either use the sql time field or store it as an int using the unix timestamp. I always use the unix timestamp( time() ) then format it in php after( date("date string",time() ).

[mikefrederick]

why doesnt this work?

$getdates=mysql_query("select *, time_format(timestart),'%g:%i %a') as date_f from events order by date_f asc");

[Jeremysr]

It looks like you don't have enough parentheses.

[mikefrederick]

minus the ) in the timestart()

[PFMaBiSmAd]

ORDER BY the TIME field timestart. Using ORDER BY the formatted date_f is right back where you started.