如何按任意时间间隔对timestamp类型字段进行分组聚合？

核心思路 #

将时间戳转换为epoch时间数值（秒/毫秒），通过对该数值做「除以间隔值→取整→乘以间隔值」的运算，得到每个分组的起始时间，以此作为分组字段进行聚合。这种方式支持任意长度的时间间隔（秒、分钟、小时、天等单位均可），完全替代date_trunc()的固定单位限制。

具体实现（分数据库示例） #

1. PostgreSQL 示例 #

假设你的表名为weather_data，时间字段为record_time（timestamp类型），要按2小时（7200秒）分组求air_pressure的最大值：

SELECT
  -- 将计算后的epoch时间转回timestamp，作为分组的起始时间
  to_timestamp(
    FLOOR(EXTRACT(EPOCH FROM record_time) / 7200) * 7200
  ) AS group_start_time,
  MAX(air_pressure) AS max_air_pressure
FROM weather_data
GROUP BY group_start_time
ORDER BY group_start_time;

如果需要匹配示例中的毫秒精度时间格式，可改用毫秒级epoch计算：

SELECT
  to_timestamp(
    FLOOR(EXTRACT(EPOCH FROM record_time) * 1000 / 7200000) * 7200000 / 1000
  ) AS group_start_time,
  MAX(air_pressure) AS max_air_pressure
FROM weather_data
GROUP BY group_start_time
ORDER BY group_start_time;

2. MySQL 示例 #

用UNIX_TIMESTAMP()获取秒级epoch，按2小时分组：

SELECT
  FROM_UNIXTIME(
    FLOOR(UNIX_TIMESTAMP(record_time) / 7200) * 7200
  ) AS group_start_time,
  MAX(air_pressure) AS max_air_pressure
FROM weather_data
GROUP BY group_start_time
ORDER BY group_start_time;

毫秒精度版本：

SELECT
  FROM_UNIXTIME(
    FLOOR(UNIX_TIMESTAMP(record_time) * 1000 / 7200000) * 7200000 / 1000
  ) AS group_start_time,
  MAX(air_pressure) AS max_air_pressure
FROM weather_data
GROUP BY group_start_time
ORDER BY group_start_time;

3. SQL Server 示例 #

用DATEDIFF()计算秒数，按2小时分组：

SELECT
  DATEADD(SECOND, 
    FLOOR(DATEDIFF(SECOND, '1970-01-01', record_time) / 7200) * 7200,
    '1970-01-01'
  ) AS group_start_time,
  MAX(air_pressure) AS max_air_pressure
FROM weather_data
GROUP BY 
  FLOOR(DATEDIFF(SECOND, '1970-01-01', record_time) / 7200) * 7200
ORDER BY group_start_time;

灵活调整间隔 #

只需修改上述SQL中的间隔数值（单位为秒/毫秒）即可：

• 10秒间隔：将7200替换为10（秒级）或10000（毫秒级）
• 5小时间隔：替换为5*3600=18000（秒级）
• 1天间隔：替换为86400（秒级）

结果示例（匹配需求） #

当设置2小时（7200秒）间隔时，查询结果如下：

group_start_time       | max_air_pressure 
-----------------------|------------------
2022-11-22 00:00:00    | 978.81666667
2022-11-22 02:00:00    | 978.53
2022-11-22 04:00:00    | 987.23333333

内容的提问来源于stack exchange，提问作者Piotr Krześniak