应使用 DATE_TRUNC('hour', ts) 对齐时间点，而非 EXTRACT(HOUR FROM ts)；需注意时区处理、索引失效风险及空桶补全，通过 GENERATE_SERIES 生成完整时间轴并 LEFT JOIN 实现强制补桶。
直接用 DATE_TRUNC 就行，但必须注意时区、索引和空桶问题——这三个点没处理好，结果要么错、要么慢、要么缺数据。
为什么不能直接 GROUP BY EXTRACT(HOUR FROM ts)
这种写法看似简单，实际会把“2024-01-01 10:15”和“2024-01-02 10:45”都归到 HOUR = 10，但它们根本不在同一个自然小时区间里。更糟的是：EXTRACT 或 FLOOR(UNIX_TIMESTAMP(ts)/3600) 这类计算会让数据库无法使用 ts 字段上的 B-tree 索引，查询一上百万行就明显变卡。
- 真正要对齐的是“时间点”，不是“数值特征”
-
DATE_TRUNC('hour', ts)返回的是一个精确的TIMESTAMP（比如2024-01-01 10:00:00），天然可索引、可范围比较 - 如果原始列是
TIMESTAMP WITHOUT TIME ZONE，不加时区参数会导致跨时区查询结果漂移
如何正确写降采样聚合语句
核心是两步：先对齐，再分组。别在 WHERE 里对时间字段用函数，改用范围条件。
- 基础写法（按小时）：
SELECT DATE_TRUNC('hour', ts) AS hour_bin, COUNT(*), AVG(value) FROM sensor_data WHERE ts >= '2024-01-01' AND ts < '2024-01-02' GROUP BY DATE_TRUNC('hour', ts) ORDER BY hour_bin; - 带时区安全写法（推荐）：
DATE_TRUNC('hour', ts AT TIME ZONE 'Asia/Shanghai')，尤其当ts是TIMESTAMP WITHOUT TIME ZONE时必须显式指定 - 如果要查“最近24小时每小时平均值”，别写
WHERE DATE_TRUNC('hour', ts) = ...，而要用WHERE ts > NOW() - INTERVAL '24 hours'，否则索引失效
怎么保证每小时都有结果，哪怕没数据
原始数据不规则（比如某小时完全没写入），GROUP BY DATE_TRUNC 会直接跳过那一行——这不是缺失值，是整条记录被过滤掉了。要“强制补桶”，得先生成完整时间轴。
- 用
GENERATE_SERIES构建时间序列：SELECT gs.hour_bin, COUNT(t.value), AVG(t.value) FROM GENERATE_SERIES( '2024-01-01 00:00'::TIMESTAMP, '2024-01-01 23:59'::TIMESTAMP, '1 hour'::INTERVAL ) AS gs(hour_bin) LEFT JOIN sensor_data t ON t.ts >= gs.hour_bin AND t.ts < gs.hour_bin + '1 hour'::INTERVAL GROUP BY gs.hour_bin ORDER BY gs.hour_bin;
- 注意
GENERATE_SERIES的上下界是闭区间，所以终点用了23:59避免漏掉最后一小时 - 如果表很大，LEFT JOIN 可能变慢，建议给
ts加 BRIN 索引（适合时间递增写入场景）
什么时候该换 TimescaleDB 的 time_bucket()
当单表超千万行、且高频执行小时/分钟级聚合时，原生 PostgreSQL 的 DATE_TRUNC 会越来越吃力——它每次都要扫描全分区或全表，无法跳过无关 chunk。
-
time_bucket('1 hour', time)是 TimescaleDB 超表（hypertable）的原生分桶函数，能自动下推到分块（chunk）级别执行 - 配合连续聚合（continuous aggregate），可以做到实时更新物化结果，查历史小时均值直接走索引，不碰原始数据
- 但如果只是偶尔跑报表、数据量在百万级以内，硬上 TimescaleDB 反而增加运维负担
最常被忽略的一点：DATE_TRUNC 的结果类型仍是 TIMESTAMP，但很多人把它当字符串用，比如 WHERE DATE_TRUNC('day', ts) = '2024-01-01' ——这会触发隐式类型转换，可能绕过索引。真要比较，就用 DATE_TRUNC('day', ts) = '2024-01-01'::TIMESTAMP 或更稳妥的范围写法。
