如何使用PostgreSQL获取每年3月和10月的最后一个周日

核心思路 #

要获取指定月份的最后一个周日，关键步骤是：

1. 确定目标年份中目标月份的最后一天
2. 根据该最后一天的星期值，向前推算到最近的周日（如果最后一天本身是周日则直接使用）

PostgreSQL 实现 #

PostgreSQL 中 EXTRACT(dow FROM date) 返回值为 0（周日）到 6（周六），结合这个特性可以写出如下查询：

1. 获取单一年份指定月份的最后一个周日

以2024年3月为例：

SELECT 
  CASE 
    WHEN EXTRACT(dow FROM last_day) = 0 THEN last_day
    ELSE last_day - EXTRACT(dow FROM last_day)::integer
  END AS march_last_sunday
FROM (
  -- 生成2024年3月的最后一天
  SELECT (date_trunc('year', '2024-01-01'::date) + interval '3 months') - interval '1 day' AS last_day
) AS target_month;

2. 获取连续年份的3月和10月最后一个周日

替换年份范围即可批量查询：

WITH years AS (
  SELECT generate_series(2020, 2030) AS year  -- 自定义需要查询的年份范围
),
target_months AS (
  SELECT 
    year,
    -- 生成每年3月的最后一天
    (date_trunc('year', to_date(year::text, 'YYYY')) + interval '3 months') - interval '1 day' AS march_last_day,
    -- 生成每年10月的最后一天
    (date_trunc('year', to_date(year::text, 'YYYY')) + interval '10 months') - interval '1 day' AS october_last_day
  FROM years
)
SELECT 
  year,
  CASE 
    WHEN EXTRACT(dow FROM march_last_day) = 0 THEN march_last_day
    ELSE march_last_day - EXTRACT(dow FROM march_last_day)::integer
  END AS march_last_sunday,
  CASE 
    WHEN EXTRACT(dow FROM october_last_day) = 0 THEN october_last_day
    ELSE october_last_day - EXTRACT(dow FROM october_last_day)::integer
  END AS october_last_sunday
FROM target_months;

MySQL 实现 #

MySQL 中 LAST_DAY(date) 可直接获取月份最后一天，DAYOFWEEK(date) 返回值为 1（周日）到 7（周六），对应逻辑调整如下：

1. 单一年份指定月份查询

以2024年10月为例：

SELECT 
  CASE 
    WHEN DAYOFWEEK(last_day) = 1 THEN last_day
    ELSE DATE_SUB(last_day, INTERVAL (DAYOFWEEK(last_day) - 1) DAY)
  END AS october_last_sunday
FROM (
  SELECT LAST_DAY('2024-10-01') AS last_day
) AS target_month;

2. 连续年份批量查询

WITH years AS (
  -- 生成2020到2030的年份列表，可按需调整范围
  SELECT year FROM (
    SELECT 2020 + t1.i*10 + t2.i AS year 
    FROM (SELECT 0 AS i UNION SELECT 1) t1, 
         (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2
  ) t WHERE year BETWEEN 2020 AND 2030
),
target_months AS (
  SELECT 
    year,
    LAST_DAY(CONCAT(year, '-03-01')) AS march_last_day,
    LAST_DAY(CONCAT(year, '-10-01')) AS october_last_day
  FROM years
)
SELECT 
  year,
  CASE 
    WHEN DAYOFWEEK(march_last_day) = 1 THEN march_last_day
    ELSE DATE_SUB(march_last_day, INTERVAL (DAYOFWEEK(march_last_day) - 1) DAY)
  END AS march_last_sunday,
  CASE 
    WHEN DAYOFWEEK(october_last_day) = 1 THEN october_last_day
    ELSE DATE_SUB(october_last_day, INTERVAL (DAYOFWEEK(october_last_day) - 1) DAY)
  END AS october_last_sunday
FROM target_months;

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