sql group by days，但是从某个小时开始

Question

I want to select rows from table, grouped by days (I have my date written in unixtime in field created_at). So I am using GROUP BY DATE(FROM_UNIXTIME(created_at)). And it works fine. But I need to group my rows not from 00:00 to 23:59, I need it to group from 09:00 to 08:59. I tried GROUP BY DATE(FROM_UNIXTIME(created_at) - 9/24), but with this some rows duplicates (I have 2 rows in table with same data). What do I do wrong?

Answer 1

You could use a date_sub

GROUP BY date(DATE_SUB(FROM_UNIXTIME(created_at), INTERVAL 9 HOUR)