mysql order by hour starting from 22:00

Question

I have a table with a time field. The time format is HH:mm. The hours only go from 22:00 to 06:00. I want to sort the rows so they look like this:

Hour
22:00
22:30
23:15
00:15
01:30
03:50
05:30

But in my current query:

select * from events order by hour

they look like this:

Hour
00:15
01:30
03:50
05:30
22:00
22:30
23:15

Is it possible? thanks!

Answer 1

Try this:

SELECT 
    *
FROM
    events
ORDER BY CASE
    WHEN SUBSTR(hour, 1, 2) >= '22' THEN 0
    ELSE 1
END , hour;