MySQL Group按特定的24小时间隔 [英] MySQL Group by specific 24 hour interval

查看:632
本文介绍了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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆