Sorting times (ex: 10:30 PM)

rwfresh

Hi all,

I have a script that access a MySQL table and grabs a set of times in the 00:00 PM/AM format. These times are then arranged into a select list. I need to sort the times. the db column that the times are stored in are not of type time and can't be because other non-time values are stored there for other select lists..

Is there a way to sort the times from the select without adding a sort field? Is there a quick way to sort the times after the select and before the select list is created? Thanks!

Weedpacket

If you can write a function that looks at two times and figures out which one is later than the other, then [man]usort[/man] is your friend.

nemonoman

You can also use MySQL functions to do the sort. Just put the computation in the ORDER BY clause.

First sort hour
Then AM/PM
THen minute

SELECT columnname FROM sometable
WHERE 1
ORDER BY
EXTRACT( HOUR_MINUTE FROM columname )+IF( SUBSTRING( columname , 7, 2 ) = 'PM', 1200, 0)

EXTRACT( HOUR_MINUTE FROM '11:05 PM' )+IF( SUBSTRING( '11:05 PM', 7, 2 ) = 'PM', 1200, 0)

would return 2305

EXTRACT( HOUR_MINUTE FROM '11:05 AM' )+IF( SUBSTRING( '11:05 AM', 7, 2 ) = 'PM', 1200, 0)

would return 1105