Mysql组以24小时为间隔 [英] Mysql Group By 24 hour intervals

查看:215
本文介绍了Mysql组以24小时为间隔的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

如何将表格的记录按时间间隔分组,每天从19:00开始,到第二天18:59结束?

表格:Test

  id creation_date name 
1 2014-01-01 17:52:27 a
2 2014-01-01 18:50:00 b
3 2014-01-01 19:00:00 c
4 2014-01-03 18:59:00 e
5 2014- 01-03 12:00:00 f

期望的结果是:

 区间发生次数
2013-31-31 19:00:00 - 2014-01-01 18:59:59 2
2014-01-01 19:00:00 - 2014-01-02 18:59:59 1
2014-01-02 19:00:00 - 2014-01-03 18:59:59 1
2014-01-03 19:00:00 - 2014-01-04 18:59:59 1




  SELECT MIN(creation_date),MAX(creation_date) ,COUNT(*)AS发生
FROM测试
GROUP BY DATE(DATE_SUB(creation_date,INTERV AL 19 HOUR))

演示: http://sqlfiddle.com/#!2/aa7583/6



格式化部分,使用此:

  SELECT CONCAT(CONCAT(DATE(DATE_SUB(creation_date,INTERVAL 19 HOUR)),'19: 00:00'),' - ',CONCAT(DATE(DATE_ADD(creation_date,INTERVAL 5 HOUR)),'18:59:00'))AS Interval,COUNT(*)AS发生
FROM test
GROUP BY DATE(DATE_SUB(creation_date,INTERVAL 19 HOUR))


How can I Group the records of the table Test by time interval, beginning everyday at 19:00 and ending on the next day at 18:59?

Table: Test

id  creation_date   name
1   2014-01-01      17:52:27    a
2   2014-01-01      18:50:00    b
3   2014-01-01      19:00:00    c
4   2014-01-03      18:59:00    e
5   2014-01-03      12:00:00    f

Desired result is:

Interval                                    Number of Occurrences
2013-31-31 19:00:00 - 2014-01-01 18:59:59   2
2014-01-01 19:00:00 - 2014-01-02 18:59:59   1
2014-01-02 19:00:00 - 2014-01-03 18:59:59   1
2014-01-03 19:00:00 - 2014-01-04 18:59:59   1

解决方案

Try this:

SELECT MIN(creation_date), MAX(creation_date), COUNT(*) AS Occurrences 
FROM test
GROUP BY DATE(DATE_SUB(creation_date, INTERVAL 19 HOUR))

Working demo: http://sqlfiddle.com/#!2/aa7583/6

For the formatting part, use this:

SELECT CONCAT( CONCAT(DATE(DATE_SUB(creation_date, INTERVAL 19 HOUR)),' 19:00:00') , ' - ' , CONCAT(DATE(DATE_ADD(creation_date, INTERVAL 5 HOUR)), ' 18:59:00') ) AS Interval, COUNT(*) AS Occurrences 
FROM test
GROUP BY DATE(DATE_SUB(creation_date, INTERVAL 19 HOUR))

这篇关于Mysql组以24小时为间隔的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

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