Mysql组以24小时为间隔 [英] Mysql Group By 24 hour intervals
本文介绍了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屋!
查看全文