Jump to content


Photo

MySQL Sorting Question


  • Please log in to reply
2 replies to this topic

#1 zebe

zebe
  • Members
  • PipPip
  • Member
  • 24 posts

Posted 12 July 2006 - 05:53 PM

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:
ORDER BY REPLACE(bus_leaves_uri, ':', '')"

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!




#2 jworisek

jworisek
  • Members
  • PipPipPip
  • Advanced Member
  • 112 posts

Posted 12 July 2006 - 06:10 PM

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.

#3 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 12 July 2006 - 06:39 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users