[timmerk]

Hi,

I have a table with one of the fields being a string with the name of "time". It contains times throughout one day in the format of things like:

8:55 AM Eastern Time
9:00 AM
9:00 AM
9:00 AM
10:15 AM
11:10 AM
12:00 PM

Notice there can be multiplies. Also, they times are not in that nice order. My question is, how would I make it (using either PHP or MySQL) to sort the time string in chronological order. Keep in mind that 10:10 PM is later then 11:00 AM.

THanks!

[larkost]

The best solution would be to convert the MySQL field to a real time field (stored as a timestamp) and then just format it back to whatever format you would want for display. Then you could simply put in a 'ORDER BY <field name> ASC'. But it sounds like you have a time string stored as a text field (*boo* *hiss*).

So the second option is to try and use PHP's strtotime function. This converts things to a timestamp (number of seconds since the epoc... whatever that happens to be on your system). Then you just need to compare the numbers to sort them. Hope that works for you, because otherwise it will be a tough slog to get the job done.

[Simon Mundy]

Originally posted by timmerk:
Hi,

I have a table with one of the fields being a string with the name of "time". It contains times throughout one day in the format of things like:

8:55 AM Eastern Time
9:00 AM
9:00 AM
9:00 AM
10:15 AM
11:10 AM
12:00 PM

Notice there can be multiplies. Also, they times are not in that nice order. My question is, how would I make it (using either PHP or MySQL) to sort the time string in chronological order.

Indeed. Alter your table to make that field a TIME or DATETIME field which will store it as HH:MM:SS or YYYY-MM-DD HH:MM:SS and let you perform native time conversions on your data instead of mucking around with scripting on the server side.

You could supplement this field with a seperate timezone field if you need to perform timezone conversions.

Also beware of naming your field 'TIME' when that's a) likely to be a MySQL keyword and b) isn't really a helpful description of what that field contains. Perhaps 'end_time' or 'start_time' or 'interval_start' or something like that will be easier for someone else to interpret at a glance.

Keep in mind that 10:10 PM is later then 11:00 AM.

Really?

[timmerk]

Heheh, yes really

Thanks for your help!