Sorting Time Strings

OK, so I’m working on a MySQL database of tour reservations that has time values stored as strings in an AM/PM time format. The hour portion of the value does not contain a leading 0, so “6:00 AM”, “11:35 AM”, “4:45 PM” are all possible values. I’ve written a query to pull a sorted list of the unique tour times from the database that looks something like this:

SELECT DISTINCT reservation_time,
FROM reservations
WHERE DATEDIFF(reservation_date, '2006-12-11')=0
AND is_canceled=0
ORDER BY SUBSTRING(reservation_time, -2),
CAST(SUBSTRING_INDEX(reservation_time,':',1 ) AS UNSIGNED),
CAST(SUBSTRING(reservation_time,-5, 2) AS UNSIGNED);

My problem is what follows the ORDER BY statement. I’m sorting the list of time values 3 times: first by the last 2 digits of the time (AM or PM), then by the hour, then by the minutes. I can’t use SUBSTRING( reservation_time, 2 ) to get the hour value because some have one digit and some have 2. This also means I have to get the minutes by counting from the right side instead of from the left. My question, to all my SQL fluent friends out there is whether or not this is the best way to get this data. Ordering the data 3 times seems a bit inefficient, but then I’m no SQL wizard.

2 comments on “Sorting Time Strings

Thanks to a tip from Nathan, I was able to reduce the ORDER BY Clause down to: “ORDER BY SUBSTRING(reservation_time, -2), CAST(reservation_time AS TIME)”. Duh.

Brian says:

Is the data in the db as a time?
If not, cast to time and then order by. Nathan sent you down the right track. It defineately would help if the data were in the right format to begin with.

Leave a Reply

Your email address will not be published. Required fields are marked *

Back to the top!