如何在PostgreSQL视图中查询最近3个日历年的全量记录

原语句问题说明 #

你当前使用的语句是将当前日期直接减去3年的时间间隔，保留了原日期的月日部分，因此仅能返回3年前同月同日之后的记录，无法覆盖完整自然年的全部数据。

最优实现方案 #

推荐使用DATE_TRUNC函数将日期截断到当年年初，再做时间偏移，写法如下：

SELECT * FROM table1 
WHERE datecolumn >= DATE_TRUNC('year', NOW()) - INTERVAL '3 years';

逻辑说明 #

DATE_TRUNC('year', NOW())会自动将当前时间截断为当年1月1日的0点，在此基础上减去3年的时间间隔，直接得到3年前的1月1日0点，完全匹配“从完整自然年起始统计”的需求。
比如当前日期为2021年9月17日时，该逻辑计算得到的起始时间就是2018-01-01 00:00:00。

可选替代写法 #

如果你的业务仅需要按年份数值比较，也可以使用年份提取的写法：

SELECT * FROM table1
WHERE EXTRACT(YEAR FROM datecolumn) >= EXTRACT(YEAR FROM NOW()) - 3;

注意事项 #

• 优先推荐第一种DATE_TRUNC写法：该写法可以命中datecolumn字段上的普通索引，数据量较大时查询效率更高
• 第二种写法因为对筛选字段做了函数计算，会导致普通索引失效，仅适合小表或者已预先构建年份函数索引的场景

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