PostgreSQL中如何提取上月首尾时间戳并高效统计历史表行数

嘿，我完全懂你的痛点！用date_trunc和to_timestamp包裹索引列clock时，PostgreSQL没办法利用你建的索引，只能做全表扫描，速度当然慢得让人抓狂。下面给你一个能完美利用索引的方案，核心就是直接计算出上月首尾的epoch时间戳，用数值范围查询来走索引。

具体实现语句 #

直接用BETWEEN或者>=+<=来限定clock的范围就好，这两种写法都能触发索引扫描：

-- 写法1：使用BETWEEN
SELECT count(clock) 
FROM history 
WHERE clock BETWEEN 
  extract(epoch from date_trunc('month', CURRENT_DATE) - INTERVAL '1 month')::bigint
  AND 
  extract(epoch from date_trunc('month', CURRENT_DATE) - INTERVAL '1 second')::bigint;

-- 写法2：使用>=和<=（效果一致，个人偏好选这个）
SELECT count(clock) 
FROM history 
WHERE clock >= extract(epoch from date_trunc('month', CURRENT_DATE) - INTERVAL '1 month')::bigint
  AND clock <= extract(epoch from date_trunc('month', CURRENT_DATE) - INTERVAL '1 second')::bigint;

语句解释 #

我拆解一下每个部分的作用，方便你理解：

• date_trunc('month', CURRENT_DATE)：获取当前月份第一天的0点时间（比如今天是2024-05-20，这部分就返回2024-05-01 00:00:00）
• 减去INTERVAL '1 month'：得到上个月第一天的0点时间（即2024-04-01 00:00:00），再用extract(epoch from ...)转成秒级epoch，最后转成bigint匹配你的clock类型
• 上个月的结束时间是当前月份第一天0点减去1秒（即2024-04-30 23:59:59），这样所有属于上个月的epoch时间都会被精准包含在内

为什么这个写法更快？ #

因为我们直接对索引列clock做数值范围比较，PostgreSQL可以直接走clock上的B-tree索引，避免了全表扫描，查询速度会有质的提升。

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