MySQL How to select time between hours and minutes

Issue

I have a table like this

UserID INT resetTime VARCHAR
2 20:00
3 21:00
4 22:00
5 23:30
6 23:50
7 0:00
8 0:10
9 5:00

I want to select all userID that have "resetTime" between 23:50 AND 0:10.

example here: http://sqlfiddle.com/#!9/174565/1

Solution

Declare the column as TIME instead of VARCHAR:

ALTER TABLE test MODIFY resetTime TIME;

Then you can query like this:

SELECT * from test 
WHERE 
  resetTime >= "23:50"
  OR resetTime <= "0:10" 

Or:

SELECT * from test 
WHERE 
  resetTime >= "05:00"
  AND resetTime <= "05:30" 

Note the different and/or logic, depending on wether end of your timeframe is after midnight or not.

See updated SQL fiddle

Alternatively, you can also convert the strings on the fly for each query, but it unneccessarily costs performance. If you can, modify the column definition. With explicit type conversions, a query would look like this:

SELECT * from test 
WHERE 
  TIME(resetTime) >= TIME("23:50")
  OR TIME(resetTime) <= TIME("00:10")

See this working in in SQL fiddle too

Answered By – Kaii

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published