PostgreSQL v13.3环境下如何按5秒时间间隔对数据分组

架构老林

2026-10-6

兼容PostgreSQL 13的5秒间隔分组实现方案

你可以通过时间戳转epoch秒数取整的方式实现任意间隔的时间分组，不需要依赖高版本才支持的date_bin函数，逻辑简单易懂：

核心逻辑说明

用extract(epoch FROM ts)将时间戳转为从1970-01-01 00:00:00开始的秒级时间戳
除以分组间隔秒数（5秒就除以5）后向下取整，再乘回间隔秒数，就能得到对齐到间隔起始点的秒数
最后用to_timestamp将计算后的秒数转回时间戳格式即可

改造后的完整查询语句

SELECT
    to_timestamp(floor(extract(epoch FROM ts) / 5) * 5) AS ts,
    instrument,
    -- 此处保留你原来的聚合计算逻辑即可
    count(*) AS trade_count
FROM binance_trades
-- 建议添加过滤条件缩小扫描范围，大幅提升查询性能
WHERE instrument = 'ETHUSDT'
  AND ts >= '2021-08-20 00:00:00'
  AND ts < '2021-08-20 00:01:00'
GROUP BY 1, 2
ORDER BY ts;

自定义时间对齐的扩展写法

如果需要从指定的时间点开始对齐5秒间隔（而非默认的1970年起点），可以用以下写法调整对齐基准：

SELECT
    -- 此处将'2000-01-01'替换为你需要的对齐基准时间即可
    '2000-01-01'::TIMESTAMP + FLOOR(EXTRACT(EPOCH FROM (ts - '2000-01-01'::TIMESTAMP)) / 5) * INTERVAL '5 second' AS ts,
    instrument,
    count(*) AS trade_count
FROM binance_trades
GROUP BY 1, 2
ORDER BY ts;