MySQL: Compare BETWEEN time

Question

This may be impossible in the way that I'd like to have it work, but here goes.

SELECT * FROM `table` WHERE CAST('05:00:00' AS time) BETWEEN `start` AND `end`

The entries in the database are:

`start` = '22:59:59'
`end` = '06:00:00'

However, the query returns no results. Of course, this would work if there were dates involved, however there are not. It would also work if the start was = '00:00:00'.

Answer 1

We can compare time using simple time to seconds function. Use the following:

SELECT id
FROM table1
WHERE TIME_TO_SEC('2014-03-05 04:17:47') >= TIME_TO_SEC('2014-03-05 04:17:47');