Postgres按时间间隔统计行数并获取对应时间戳的SQL方法

算力阿峰

2026-8-29

PostgreSQL 按指定时间间隔聚合统计时段新增记录数

直接用PostgreSQL原生的date_trunc函数做时间粒度截断即可，不需要写复杂的时间格式转换逻辑，性能最好，能直接命中dateCreated字段上的索引。

基础实现（仅返回有数据的时段）

把SQL里的表名替换成你自己的业务表名就能直接用，默认按自然日统计：

SELECT
  date_trunc('day', dateCreated) AS date,
  COUNT(id) AS numberOfRowCreated
FROM 你的业务表名
-- 需要限定统计时间范围可放开下面的WHERE条件
-- WHERE dateCreated >= '2024-01-01' AND dateCreated < '2024-07-01'
GROUP BY 1
ORDER BY 1 ASC;

要切换聚合间隔，只需要修改date_trunc的第一个参数，常用可选值：

'hour'：按小时聚合，返回对应整点时间戳
'day'：按自然日聚合，返回对应日期0点时间戳
'week'：按自然周聚合，返回对应周周一0点时间戳
'month'：按自然月聚合，返回对应月份1号0点时间戳
'quarter'：按季度聚合
'year'：按年聚合

进阶实现（补全无数据时段，显示0值）

上面的写法如果某个时段完全没有新增记录，结果集里就不会返回这个时段的行。如果需要返回连续时间序列、无数据时段numberOfRowCreated显示0，搭配generate_series生成连续时间轴做左连接即可，以下是按日统计2024年上半年数据的示例：

WITH continuous_time AS (
  SELECT generate_series(
    '2024-01-01'::timestamp,
    '2024-06-30 23:59:59'::timestamp,
    '1 day'::interval -- 这里的间隔值要和date_trunc的粒度匹配，按周统计就写'1 week'
  ) AS date
)
SELECT
  ct.date,
  COUNT(t.id) AS numberOfRowCreated
FROM continuous_time ct
LEFT JOIN 你的业务表名 t
  ON date_trunc('day', t.dateCreated) = ct.date
GROUP BY ct.date
ORDER BY ct.date ASC;