PostgreSQL如何获取两个日期间完整周（周一至周日）的周数

要筛选出所有7天完全落在指定日期区间内的周一至周日完整周，核心是先修正生成序列的起止日期，确保从区间内第一个周一开始，到区间内最后一个周日结束，再生成周序列提取周数。

解决方案SQL #

WITH date_range AS (
    SELECT 
        '2022-12-01'::date AS original_start,
        '2022-12-31'::date AS original_end
),
adjusted_boundaries AS (
    SELECT
        -- 计算区间内第一个周一（原始起始日之后的首个周一）
        original_start + ((8 - EXTRACT(isodow FROM original_start)) % 7)::int * INTERVAL '1 day' AS start_monday,
        -- 计算区间内最后一个周日（原始结束日之前的最后一个周日）
        original_end - EXTRACT(isodow FROM original_end)::int * INTERVAL '1 day' AS end_sunday
    FROM date_range
)
SELECT EXTRACT('week' FROM dt::date) AS week_number
FROM adjusted_boundaries,
     generate_series(start_monday, end_sunday, '1 week'::interval) AS dt
WHERE start_monday <= end_sunday;

逻辑说明 #

1. date_range CTE：集中存储原始起止日期，方便后续修改维护。
2. adjusted_boundaries CTE：
   • start_monday：利用isodow（ISO标准，周一=1，周日=7）计算偏移量：
     • 若原始起始日为周一，(8 - 1) %7 =0，偏移0天直接取原日期；
     • 若为周四（isodow=4），(8-4)%7=4，偏移4天得到下周一（2022-12-01 +4天=2022-12-05）。
   • end_sunday：用isodow值作为天数减去：
     • 若原始结束日为周日（isodow=7），减7天仍为当天；
     • 若为周六（isodow=6），减6天得到上周日（2022-12-31 -6天=2022-12-25）。
3. 生成序列并筛选：用generate_series从修正后的周一到周日按周生成日期，提取周数；WHERE条件避免区间内无完整周时返回无效结果。

示例输出 #

对于输入的2022-12-01至2022-12-31，执行后将返回：

week_number
-------------
          49
          50
          51

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