How can I sort by time of day in a SQL field? (ie: 11:00 AM or 1:00 PM)

Vigilante

The topic about says it all. I'm trying to get events onto a calendar in the right order but SQL is sorting them so they come out in this order:

10:30 AM
11:31 PM
12:00 AM
1:30 PM

2:00 PM
9:00 AM
9:00 AM
9:00 AM

I'm currently storing these in a varchar field and sorting with ORDER BY timeofday ASC. Any assistance on this problem would be greatly appreciated.

Any ideas?

mrhappiness

are you able to use a column of type datetime?

otherwise you will have to do combine some string-functions

Vigilante

I don't see why I wouldn't be able to use any column type but haven't used the datetime column type before.

If I just changed the varchar field could I save strings like 11:00 AM to it?

Is converting them to 24 hour format the only way to make mysql sort these dates?

Weedpacket

Um, I'd guess you'd have to read your database server's documentation to find the answers to things like that.

hearn

As long as your dates aren't going to be before 1970 I would use a UNIX timestamp to sort your dates and times. This can allow for easy formatting and very fast sorting via an INT.

See the php functions time() and date() to see what I'm talking about.

Cheers.