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!

ORDER BY with case `SELECT EVENTS.Hour FROM EVENTS ORDER BY CASE WHEN HOUR(EVENTS.Hour) >= 22 THEN 0 ELSE 1 END ` or ORDER BY with IF `SELECT EVENTS.Hour FROM EVENTS ORDER BY IF ( HOUR(EVENTS.Hour) >= 22 , 0 , 1 ) ` using the HOUR function is somehow more clean then using SUBSTR function — Raymond Nijland, Jan 02 '17 at 17:31

score 1 · Accepted Answer · answered Jan 02 '17 at 17:13

1

Try this:

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

answered Jan 02 '17 at 17:13

Gurwinder Singh

1 Answers1