CODEWITHSUNDEEP
mysqlgroup-by
Deepak
Deepak

Reputation: 6802

MySQL Group By custom timestamp

I want to get results from the database which is grouped by date. The resulting touples should be grouped by date which ranges from 5 am to 5 am. In other words the cutoff should be on 5 am and not midnight 12 am. I can do GROUP BY DAY(timestamp) to group by normal day but what if I have to vary the time and each records should be grouped from 5 am to 5 am ? How should I change the query ? Thanks ?

Upvotes: 3

Views: 405

Answers (1)

Gus
Gus

Reputation: 7349

Simply subtract 5 hours from each datetime value you have, and then convert it to a date. Then you can group by the result.

GROUP BY DATE(DATE_SUB(timestamp, INTERVAL 5 HOUR))

Upvotes: 6

Related Questions