MySQL: Compare BETWEEN time

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'.

Solution 1:

i think what you are looking for is:

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

I'm not sure if you need to use the CASTs, though this should also work.

SELECT * FROM table WHERE start < '05:00:00' AND end > '05:00:00'

Solution 2:

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

Solution 3:

I just came across this question and I had the same problem. My solution is as follow:

SET @time = '05:00:00';

SELECT * FROM `table`
WHERE IF(
        `start` < `end`,
        CAST(@time AS TIME) BETWEEN `start` AND `end`,
        (CAST(@time AS TIME) BETWEEN CAST('00:00:00' AS TIME) AND `end`) OR 
        (CAST(@time AS TIME) BETWEEN `start` AND CAST('24:00:00' AS TIME))
    ) = 1;

Hope it helps someone in the future.

Thank you Martin for the hint!

Solution 4:

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');