按15分钟间隔分组mysql查询
按15分钟间隔分组mysql查询
提问于 2010-05-08 20:13:02
我有一个监控系统，它每隔n秒收集一次数据(n大约是10，但有所不同)。我想以15分钟为间隔汇总收集到的数据。有没有办法将时间戳值合并到15分钟的块中，以便分组工作？
回答 8
Stack Overflow用户
回答已采纳
发布于 2010-05-08 20:22:08
SELECT FLOOR(UNIX_TIMESTAMP(timestamp)/(15 * 60)) AS timekey
FROM table
GROUP BY timekey;Stack Overflow用户
发布于 2011-06-09 17:27:22
试试看，记录的分组间隔为15分钟，您可以将15*60更改为所需的间隔(秒
SELECT sec_to_time(time_to_sec(datefield)- time_to_sec(datefield)%(15*60)) as intervals from tablename
group by intervalsStack Overflow用户
发布于 2010-05-08 20:23:01
方法1)的改编如下：
select Round(date_format(date, "%i") / (15*60)) AS interval
from table
group by interval 方法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;我发现了一些here的方法
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)
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)
SELECT substring(postdate, 1,10) AS dd, COUNT(id) FROM MyTable GROUP BY dd;(另请访问：http://www.bradino.com/mysql/dayparting-on-datetime-field-using-substring/)
编辑:在包含大量记录的表上，所有解决方案的性能都很差。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/2793994
复制相关文章
相似问题