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