CODEWITHSUNDEEP
mysqlsqldatetime
Danil Onyanov
Danil Onyanov

Reputation: 210

SQL query for time range

I have a table with two TIME columns: time_from and time_to. Now I need to select all rows where time_from > currentTime and time_to < currentTime. For example, time_from is 22:00, time_to is 2:00 and now is 23:45. In this case 2:00 is for tomorrow and this row should be selected. Can someone point me right query?

Upvotes: 2

Views: 361

Answers (2)

everag
everag

Reputation: 7662

Since you're dealing with TIME values, I'd trust this Query:

select *
from TEST
where
(time_from < time_to and cast(NOW() as TIME) between time_from and time_to)
or
(time_from > time_to and cast(NOW() as TIME) > time_from)
or
(time_from > time_to and cast(NOW() as TIME) < time_to);

It safely compares the times even when they overpass the day time (e.g.: from 22:00 to 02:00).

Check this SQLFiddle

Upvotes: 1

Andomar
Andomar

Reputation: 238058

select  *
from    YourTable
where   NOW() between time_from and time_to

Upvotes: 2

Related Questions