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

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

CodePudding user response：

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
)