PostgreSQL 13.6中查询指定时间间隔结果的SQL语法写法

从MySQL迁移到PostgreSQL时日期计算逻辑不匹配是高频问题，核心差异是MySQL的datediff(结束日期, 开始日期)直接返回两个日期的整数天差值，自动忽略时分秒部分；PostgreSQL没有完全同逻辑的内置函数，直接套用interval + BETWEEN很容易出现边界匹配错误。

推荐写法（性能最优，可命中索引） #

直接通过时间范围做判断，100%匹配「创建已满30天、未满60天」的业务逻辑，同时可以直接使用created_at字段上的普通B树索引，适合百万级以上大表使用：

AND invoices.created_at >= CURRENT_DATE - INTERVAL '59 days'
AND invoices.created_at < CURRENT_DATE - INTERVAL '29 days'

边界逻辑说明 #

以当前日期为2024-06-10为例：

• 下限：CURRENT_DATE - INTERVAL '59 days'对应2024-04-12 00:00:00，包含所有59天前及之后创建的发票
• 上限：CURRENT_DATE - INTERVAL '29 days'对应2024-05-12 00:00:00，通过小于判断排除所有29天内创建的发票，刚好覆盖创建日期在2024-04-12到2024-05-11之间的所有记录，和原MySQL语句逻辑完全一致。

1:1对齐原MySQL逻辑的写法 #

如果表数据量不大，不需要考虑索引优化，也可以直接写和原MySQL逻辑完全对等的语句，可读性更强：

AND (CURRENT_DATE - invoices.created_at::date) BETWEEN 30 AND 59

注意：该写法对created_at字段做了::date类型转换，无法命中created_at字段的普通索引，数据量超过10万行时查询性能会明显下降，优先使用前面的范围判断写法。

常见错误写法避坑 #

• 不要直接用BETWEEN搭配interval写闭区间：如果写created_at BETWEEN CURRENT_DATE - INTERVAL '59 days' AND CURRENT_DATE - INTERVAL '30 days'，会漏掉30天前0点之后创建的所有记录，比如30天前下午2点开的发票就不会被匹配到，结果会少算数据。
• 不要颠倒两个interval的顺序，否则会查到最近30天内的新发票，和需求完全相反。
• 不要用age()函数计算账龄：age()按自然年月计算时间差，不是按绝对天数统计，跨大小月、闰年的时候会出现计算误差。

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