以 15 分钟为间隔对 mysql 查询进行分组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2793994/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Group mysql query by 15 min intervals
提问by gsiener
I've got a monitoring system that is collecting data every n seconds (n is approximately 10 but varies). I'd like to aggregate the collected data by 15 minute intervals. Is there a way to consolidate the timestamp values into 15 minute chunks to allow for grouping to work?
我有一个监控系统，每 n 秒收集一次数据（n 大约为 10，但会有所不同）。我想按 15 分钟的时间间隔汇总收集的数据。有没有办法将时间戳值合并为 15 分钟的块以允许分组工作？
回答by unutbu
SELECT FLOOR(UNIX_TIMESTAMP(timestamp)/(15 * 60)) AS timekey
FROM table
GROUP BY timekey;
回答by BenG
Try this , grouping of records of 15 minutes interval, you can change 15*60 to the interval in seconds you need
试试这个，15分钟间隔的记录分组，你可以把15*60改成你需要的间隔秒数
SELECT sec_to_time(time_to_sec(datefield)- time_to_sec(datefield)%(15*60)) as intervals from tablename
group by intervals
回答by Martin Vseticka
Adaptation of approach 1) below:
对方法 1) 的改编如下：
select Round(date_format(date, "%i") / (15*60)) AS interval
from table
group by interval
Adaptation of approach 3) below:
方法 3) 的改编如下：
SELECT Round(Convert(substring(date_column, 14, 2), UNSIGNED) / (15*60)) AS interval /* e.g. 2009-01-04 12:20:00 */
FROM table
GROUP BY interval;
A few approaches I've found here:
1)
1)
select date_format(date, "%W") AS `Day of the week`, sum(cost)
from daily_cost
group by `Day of the week`
order by date_format(date, "%w")
2)
2)
select count(*) as 'count',
date_format(min(added_on), '%Y-%M-%d') as 'week commencing',
date_format(added_on, '%Y%u') as 'week'
from system
where added_on >= '2007-05-16'
group by week
order by 3 desc;
3)
3)
SELECT substring(postdate, 1,10) AS dd, COUNT(id) FROM MyTable GROUP BY dd;
(Also here: http://www.bradino.com/mysql/dayparting-on-datetime-field-using-substring/)
（也在这里：http: //www.bradino.com/mysql/dayparting-on-datetime-field-using-substring/）
EDIT: All the solutions will perform badly on a table with a large number of records.
编辑：所有解决方案在具有大量记录的表上都会表现不佳。
回答by James
I started with the answer given above by unutbu but didn't get what I needed and had to add a bit to it.
我从 unutbu 上面给出的答案开始，但没有得到我需要的东西，不得不添加一点。
Select Created, from_unixtime(FLOOR(UNIX_TIMESTAMP(Created)/(15*60))*(15*60)) GroupTime,
COUNT(*) as Cnt
FROM issue i
GROUP BY GroupTime
Select Created, from_unixtime(FLOOR(UNIX_TIMESTAMP(Created)/(15*60))*(15*60)) GroupTime,
COUNT(*) as Cnt
FROM issue i
GROUP BY GroupTime
This code divides by the 900 seconds in a 15 minute span then floors the value and multiplies it back up by 900, essentially rounding down to the nearest 15 minute increment.
此代码在 15 分钟的跨度内除以 900 秒，然后将该值下限并将其乘以 900，基本上向下舍入到最接近的 15 分钟增量。
回答by Vineeta Khatuja
Following query groups rows and creates timestamps at 15 min intervals.
以下查询对行进行分组并以 15 分钟为间隔创建时间戳。
Select concat( date(created_dt) , ' ', sec_to_time(time_to_sec(created_dt)- time_to_sec(created_dt)%(15*60) + (15*60)))as created_dt_new from table_name group by created_dt_new
E.g Timestamps
2016-11-09 13:16:29
2016-11-09 13:16:49
2016-11-09 13:17:06
2016-11-09 13:17:26
2016-11-09 13:18:24
2016-11-09 13:19:59
2016-11-09 13:21:17
例如时间戳
2016-11-09 13:16:29
2016-11-09 13:16:49
2016-11-09 13:17:06
2016-11-09 13:17:26
2016-11-18 :24
2016-11-09 13:19:59
2016-11-09 13:21:17
Are grouped into 2016-11-09 13:30:00
分为2016-11-09 13:30:00
sec_to_time(time_to_sec(created_dt)- time_to_sec(created_dt)%(15*60) + (15*60)))
Upper bounds time to nearest 15 min interval. e.g 12:10 -> 12:15concat( date(created_dt) , ' ', sec_to_time(time_to_sec(created_dt)- time_to_sec(created_dt)%(15*60) + (15*60)))
Generates a timestamp taking the date from the timestamp field.
sec_to_time(time_to_sec(created_dt)- time_to_sec(created_dt)%(15*60) + (15*60)))
上限时间到最近的 15 分钟间隔。例如 12:10 -> 12:15concat( date(created_dt) , ' ', sec_to_time(time_to_sec(created_dt)- time_to_sec(created_dt)%(15*60) + (15*60)))
生成从时间戳字段中获取日期的时间戳。
回答by Salman A
Unix timestamps: floor them to nearest 15 minute using one of the following:
Unix 时间戳：使用以下方法之一将它们平铺到最接近的 15 分钟：
timestamp div (15 * 60) * (15 * 60) -- div is integer division operator
timestamp - timestamp % (15 * 60)
Date time: assuming the datatype does not have fractional seconds, floor them to nearest 15 minute using:
日期时间：假设数据类型没有小数秒，使用以下方法将它们划分为最接近的 15 分钟：
date - INTERVAL EXTRACT(SECOND FROM date) SECOND - INTERVAL EXTRACT(MINUTE FROM date) % 15 MINUTE
回答by Chandrakant Thakkar
THis Work for me
这对我有用
SELECT CONCAT (
YEAR(transactionDate)
,'-'
,MONTH(transactionDate)
,'-'
,DAYOFMONTH(transactionDate)
,' '
,HOUR(transactionDate)
,':'
,((floor((MINUTE(transactionDate) / 15)) + 1) * 15) - 1
,':59'
) AS tmp1
,count(*)
FROM tablename
GROUP BY tmp1 limit 20;
回答by Robert Chan
Change "15" to whatever interval you want.
将“15”更改为您想要的任何间隔。
select count(*),
CONCAT(HOUR(col_date),":",(MINUTE(create_date) div 15)*15) as date
from tablename
GROUP BY date
ORDER BY col_date ASC;
回答by Vitaly Nikolaev
This worked for me
这对我有用
mysql> **SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(NOW())- UNIX_TIMESTAMP(NOW())%(15*60));**
+---------------------------------------------------------------------+
| FROM_UNIXTIME(UNIX_TIMESTAMP(NOW())- UNIX_TIMESTAMP(NOW())%(15*60)) |
+---------------------------------------------------------------------+
| 2012-02-09 11:15:00 |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)
