Press "Enter" to skip to content

Code Snippet: Extracting a 24-hour time from a 12-hour time string in MySQL

The snippet:

HOUR ( STR_TO_DATE ("2:30pm" , "%h:%i%p") ) = 14

Why I needed it:

I’m currently working on a time and date filter for ClassGet. If you’ve ever wanted to find a morning class that meets only on Tuesdays, this tool is for you! The main problem, however, is that Furman’s course listings file has the start and end dates for classes in this format:

Start Time: "12:30pm", End Time: "1:20pm"

and my import script doesn’t convert these into a DATETIME format in SQL. I want to be able to look at courses from If I want to find a class starting from 12:00 to 14:00, I’ll need to do some conversion. On the front-end, I’m using a jQuery UI slider for my hour control that goes from 5 (5:00 am) to 21 (9:00 pm). Why anyone would have a class starting at 9:00 pm is beyond me, but hey, it could happen. I’m not going to worry about minutes, and I know that no class will ever be offered overnight so I’ll never need to worry about a start time being later than an end time. My script will search for classes that start from one hour to another, so I’ll need to convert the dates to match. You would think I could just do something like this:

SELECT * FROM classes WHERE `Start Time` BETWEEN 12 AND 15

But it doesn’t work like that. The dates are stored as strings, e.g. "2:30pm". We’re lucky, though… MySQL has an HOUR function that will solve that!

SELECT * FROM classes WHERE HOUR(`Start Time`) BETWEEN 12 AND 15

But still, no classes are showing up that start at 2:30. How come? As it turns out, HOUR("2:30pm") returns 2, not 14! How do we fix that? The answer lies in MySQL’s STR_TO_DATE function, which is the reverse of the DATE_FORMAT function. Now, take a look at the final version:

SELECT * FROM classes WHERE HOUR(STR_TO_DATE(`Start Time`, "%h:%i%p")) BETWEEN 12 AND 15

There we go! Now without doing any PHP or JavaScript or modifying the database structure, I was able to create a date filter for class data.

Be First to Comment

Leave a Reply

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