MySQL How to select time between hours and minutes-CodePudding

I have a table like this

UserID INT	TimeToReset 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 "timeToReset" between 23:50 AND 0:10.

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

CodePudding user response：

I suggest properly typing your columns. There is a TIME datatype in MySQL.

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