Jump to content

Archived

This topic is now archived and is closed to further replies.

zebe

MySQL Sorting Question

Recommended Posts

Hi,

I am writing a script that needs to sort by a field which contains a time value. I need to sort it in order of earlies to latest. The field type is a varchar. People have been putting in am/pm characters in this field as well and when I go to sort it comes out something like this:

8:45am
8:45am
8:45am
9:15am
9:30am
9:30am
9am
9am

In my query I have the following:
[code]ORDER BY REPLACE(bus_leaves_uri, ':', '')" [/code]

I was trying to take out the ":" thinking that was the problem with the ordering. Does anyone know a better mysql function that will actaully sort this type of data correctly?

Thanks for any help!


Share this post


Link to post
Share on other sites
decide on a new format and adjust the existing data for that...  I would say either use military time (1pm being 13:00) or having a seperate column to check am or pm.  Then you also want to check the input to make sure that people only enter valid times.  If its going to be a headache, make a dropdown for the hours then a dropdown for the minutes and combine them before submitting.

Share this post


Link to post
Share on other sites
The real solution is to use a proper TIME column, and then the sorting is free.  You can always format it nicely on the way out.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.