如何在PostgreSQL中自动获取指定历史月份的全量数据？

直接用PostgreSQL的日期截断函数date_trunc就能实现你要的自动整月范围查询，核心是精准定位目标月份的第一天和下一个月的第一天，彻底避免按当前日期往前推的区间误差。

核心逻辑拆解 #

• date_trunc('month', current_date)：把当前日期截断到当月第一天（比如今天是2024-08-11，返回2024-08-01 00:00:00）
• 减去interval '4 months'：得到4个月前的当月第一天（比如2024-04-01 00:00:00）
• 加上interval '1 month'：得到目标月份的下一个月第一天（比如2024-05-01 00:00:00），用这个作为<的条件，能确保包含目标月份的所有时间戳（包括最后一天的23:59:59）

完整查询语句 #

SELECT issued_at 
FROM issuances_extended 
WHERE issued_at >= date_trunc('month', current_date) - interval '4 months'
  AND issued_at < date_trunc('month', current_date) - interval '3 months' -- 等价于目标月份+1个月
  AND status = 'completed' 
  AND network = 'ethereum';

变体：动态调整月份偏移量 #

如果需要灵活切换偏移的月份数（比如有时候要3个月前，有时候要6个月前），可以用CTE定义变量实现：

-- 用CTE定义偏移量，修改month_offset的值即可切换
WITH params AS (SELECT 4 AS month_offset)
SELECT issued_at 
FROM issuances_extended, params
WHERE issued_at >= date_trunc('month', current_date) - (params.month_offset || ' months')::interval
  AND issued_at < date_trunc('month', current_date) - ((params.month_offset - 1) || ' months')::interval
  AND status = 'completed' 
  AND network = 'ethereum';

关键注意事项 #

• 原查询里的issued_at < '2022-07-31'存在逻辑漏洞，会漏掉7月31日当天的所有数据（比如2022-07-31 10:00:00），必须用下一个月的第一天作为上限
• current_date取的是当前日期，若要基于当前完整时间（含时分秒），可以换成now()，但date_trunc对两者截断到月份的结果一致

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