MySQL如何按自定义1小时时间间隔分组统计datetime数据？

我有一个MySQL表，每行包含datetime类型的字段，希望按以下两个自定义时间间隔分组统计：

• 时间范围：当前时间至当前时间前1小时
• 时间范围：当前时间前1小时至当前时间前2小时

我尝试了两种SQL语句，但结果不符合预期：

第一种尝试SQL #

SELECT MIN(date), MAX(date), count(stats.ID) as c FROM stats WHERE date BETWEEN (NOW() - INTERVAL 2 HOUR) AND NOW() GROUP BY UNIX_TIMESTAMP(date) DIV 7200

执行结果 #

第二种尝试SQL #

SELECT count(ID), MIN(date), MAX(date), sec_to_time(time_to_sec(date)- time_to_sec(DATE)%(60*60)) as intervals from stats WHERE date BETWEEN (NOW() - INTERVAL 2 HOUR) AND NOW() group by intervals

执行结果 #

期望结果 #

之前的两种方法都是按整点小时区间分组（比如18:00-19:00），但你需要的是以当前时间为基准的滑动1小时窗口。可以通过计算每条记录与当前时间的时间差，按小时数分组来实现：

SELECT
  MIN(date) AS `MIN(DATE)`,
  MAX(date) AS `MAX(DATE)`,
  COUNT(ID) AS count
FROM stats
WHERE date >= NOW() - INTERVAL 2 HOUR
GROUP BY FLOOR(TIMESTAMPDIFF(SECOND, date, NOW()) / 3600)
ORDER BY `MIN(DATE)`;

逻辑说明 #

1. TIMESTAMPDIFF(SECOND, date, NOW()) 计算每条记录的时间与当前时间的秒数差
2. 除以3600（1小时的秒数）后取整，得到该记录属于当前时间前第几个小时区间：
   • 结果为0：记录在当前时间至前1小时区间内
   • 结果为1：记录在前1小时至前2小时区间内
3. 按这个整数值分组，就能得到你需要的滑动时间窗口统计结果

如果需要明确显示每个区间的起止时间，可以扩展SQL：

SELECT
  DATE_FORMAT(NOW() - INTERVAL (FLOOR(TIMESTAMPDIFF(SECOND, date, NOW())/3600)) HOUR, '%Y-%m-%d %H:%i:%s') AS interval_start,
  DATE_FORMAT(NOW() - INTERVAL (FLOOR(TIMESTAMPDIFF(SECOND, date, NOW())/3600)-1) HOUR, '%Y-%m-%d %H:%i:%s') AS interval_end,
  MIN(date) AS `MIN(DATE)`,
  MAX(date) AS `MAX(DATE)`,
  COUNT(ID) AS count
FROM stats
WHERE date >= NOW() - INTERVAL 2 HOUR
GROUP BY interval_start, interval_end
ORDER BY interval_start;

内容的提问来源于stack exchange，提问作者Александр Грешников