如何从指定时间开始按X分钟间隔统计数据表插入记录数？

假设你的数据表包含ID、Date（DateTime类型）及其他字段，需要从指定起始时间（如2022-08-04 08:00:00）开始，按每X分钟（如5分钟）统计插入的记录条数，以下是不同数据库的实现方案：

MySQL 实现 #

通过时间戳计算将记录时间对齐到起始点开始的X分钟间隔，再分组统计：

-- 替换your_table为实际表名，按需修改起始时间和间隔分钟数
SET @start_time = '2022-08-04 08:00:00';
SET @interval_min = 5;

SELECT
  FROM_UNIXTIME(
    UNIX_TIMESTAMP(@start_time) + 
    FLOOR((UNIX_TIMESTAMP(Date) - UNIX_TIMESTAMP(@start_time)) / (@interval_min * 60)) * (@interval_min * 60)
  ) AS 时间段起始,
  COUNT(ID) AS 记录条数
FROM your_table
WHERE Date >= @start_time
GROUP BY 时间段起始
ORDER BY 时间段起始;

这段SQL的逻辑是：计算每条记录时间与起始时间的秒数差，按X分钟的总秒数取整，再还原为时间作为分组标识，最终统计每个时间段的记录数量。

PostgreSQL 实现 #

利用时间函数计算偏移量，对齐到指定的时间间隔：

-- 替换your_table为实际表名，按需修改起始时间和间隔分钟数
WITH params AS (
  SELECT '2022-08-04 08:00:00'::timestamp AS start_time,
         5 AS interval_min
)
SELECT
  start_time + (FLOOR(EXTRACT(EPOCH FROM (Date - start_time)) / (interval_min * 60)) * interval_min * interval '1 minute') AS 时间段起始,
  COUNT(ID) AS 记录条数
FROM your_table, params
WHERE Date >= start_time
GROUP BY 时间段起始
ORDER BY 时间段起始;

这里通过提取时间差的秒数，按X分钟单位取整后转换回时间间隔，实现时间对齐，再分组统计记录数。

补充说明 #

• 如果需要显示没有记录的空时间段（记录条数为0），可以先生成连续的时间序列，再与原表左连接统计。比如MySQL可使用递归CTE生成序列，PostgreSQL用generate_series函数。
• 确保Date字段有索引，避免大数据量下查询过慢。

内容的提问来源于stack exchange，提问作者stighy