PostgreSQL中基于起止日期过滤mon_year列数据的问题

哈哈，这个问题我之前也踩过坑！用date_part单独取年、月来过滤确实容易翻车——它完全忽略了日的部分，导致你的截止日期（2018-01-20）没法生效，最后两行2018年1月的数据就被错误地保留了。咱们来拆解一下问题，给你几个针对性的解决方案：

为什么date_part达不到预期？ #

你之前大概率写了类似这样的错误代码：

-- 错误示例：会包含2018年1月的所有数据
SELECT *
FROM your_table
WHERE 
  date_part('year', to_date(mon_year, 'YYYY-MM')) >= 2017
  AND date_part('month', to_date(mon_year, 'YYYY-MM')) >= 6
  AND date_part('year', to_date(mon_year, 'YYYY-MM')) <= 2018
  AND date_part('month', to_date(mon_year, 'YYYY-MM')) <= 1;

这个逻辑只判断了年和月的范围，完全没考虑截止日期的“20号”限制，所以2018年1月的所有数据都会被包含，自然达不到排除最后两行的需求。

解决方案分情况讨论 #

情况1：表中有具体的日期字段（比如actual_date） #

这是最简单的场景，直接用完整日期范围过滤就行，根本不需要碰mon_year或者date_part：

SELECT *
FROM your_table
WHERE actual_date >= '2017-06-22'::DATE 
  AND actual_date <= '2018-01-20'::DATE;

情况2：只有年月列（year_col/month_col）或mon_year字符串，无具体日期 #

如果每条数据是按年月汇总的，且你要排除2018年1月的所有记录（因为截止日期是20号，整个月的汇总不符合要求），可以直接用年月的组合比较：

SELECT *
FROM your_table
WHERE (year_col, month_col) < (2018, 1)
  AND (year_col, month_col) >= (2017, 6);

如果你的mon_year是'2017-06'这类格式的字符串，也可以转换成日期后再比较：

SELECT *
FROM your_table
WHERE to_date(mon_year, 'YYYY-MM') < '2018-01-01'::DATE
  AND to_date(mon_year, 'YYYY-MM') >= '2017-06-01'::DATE;

情况3：有年月列，且每条数据对应具体的日（存在day_col） #

这时候需要结合日的条件，精准过滤：

SELECT *
FROM your_table
WHERE 
  -- 2017年7-12月的所有数据直接保留
  (year_col = 2017 AND month_col BETWEEN 7 AND 12)
  -- 2017年6月只保留22号及以后的数据
  OR (year_col = 2017 AND month_col = 6 AND day_col >= 22)
  -- 2018年1月只保留20号及以前的数据
  OR (year_col = 2018 AND month_col = 1 AND day_col <= 20);

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