mysqlcurrent-time

Current time equal or less than in MySQL at 24 hour cycle


What's the best way to search less than current time at 24 hour cycle?

Database

+----+----------+-----------+
| id | time     | name      |
+----+----------+-----------+
|  1 | 07:00:00 | Breakfast |
|  2 | 12:00:00 | Lunch     |
|  3 | 18:00:00 | Dinner    |
|  4 | 21:00:00 | Supper    |
+----+----------+-----------+

My Solution

When the current time is 15:00:00

SELECT * FROM schedules where time < CURRENT_TIME() ORDER BY time DESC LIMIT 1
+----+----------+-------+
| id | time     | name  |
+----+----------+-------+
|  2 | 12:00:00 | Lunch |
+----+----------+-------+

But my solution not Work at 02:00:00

+----+----------+-------+
| id | time     | name  |
+----+----------+-------+
+----+----------+-------+

How to searching 02:00:00 and the result is:

+----+----------+--------+
| id | time     | name   |
+----+----------+--------+
|  4 | 21:00:00 | Supper |
+----+----------+--------+

Solution

  • You can force include the last row in result using union all:

    (
        -- when current time is gte 07:00
        select *
        from schedules
        where time <= current_time()
        order by time desc
        limit 1
    )
    union all
    (
        -- union last row if no matching row exists for previous query
        select *
        from schedules
        where not exists (
            select *
            from schedules
            where time <= current_time()
        )
        order by time desc
        limit 1
    )