by mysql 按每分钟group,MySQL Group按特定的24小时间隔

I have a MySQL table, with timestamps values in each row. My objective is to sum values of the amount column, and group by a custom 24 hour interval, starting from 05:30:00 every day.

input:

timestamp amount

-------------- ------

2015-01-19 08:30:12 4

2015-01-19 15:37:40 2

2015-01-20 01:57:38 2

2015-01-20 07:10:07 4

2015-01-20 22:10:38 2

2015-01-21 08:35:55 4

expected:

interval SUM(amount)

------------- -----------

2015-01-19 05:30:00 - 2015-01-20 05:30:00 8

2015-01-20 05:30:00 - 2015-01-21 05:30:00 6

2015-01-21 05:30:00 - 2015-01-22 05:30:00 4

I have tried implementing the solution(s) presented here Groupinginto interval of 5 minutes within a time range and here Mysql Group By 24 hour intervals - but without success.

解决方案

You can do this by subtracting 5.5 hours and aggregating by the date:

select date_add(date(timestamp - interval (5*60 + 30) minute), interval (5*60 + 30) minute) as interval_start,

date_add(date(timestamp - interval (5*60 + 30) minute), interval (24*60 + 5*60 + 30) minute) as interval_end,

count(*) as cnt

from t

group by date(timestamp - interval (5*60 + 30) minute)

order by interval_start;