MySQL Group按特定的24小时间隔 [英] MySQL Group by specific 24 hour interval
本文介绍了MySQL Group按特定的24小时间隔的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个MySQL表,每行都有时间戳值.我的目标是对金额"列的值求和,并按自定义的24小时间隔(每天从05:30:00开始)进行分组.
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.
输入:
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
预期:
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
我尝试实施此处提出的解决方案 Mysql分组依据每隔24小时-但没有成功.
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.
推荐答案
您可以通过减去5.5小时并按日期进行汇总来做到这一点:
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;
这篇关于MySQL Group按特定的24小时间隔的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文