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

Question

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 |
+----+----------+--------+

Is 12:00PM exact lunch time or breakfast? – Salman A Mar 30 '22 at 19:41 — Salman A, Mar 30 '22 at 19:41
12:00PM is lunch time ready – Andrew Kin Fat Choi Mar 31 '22 at 02:13 — Andrew Kin Fat Choi, Mar 31 '22 at 02:13

Salman A · Accepted Answer · 2022-03-30T20:32:42.333

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
)

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

1 Answers1