PostgreSQL按周过滤查询：当前周及指定年周的SELECT实现

嗨，这两个需求在PostgreSQL里都有很直接的实现方式，我帮你拆解一下：

PostgreSQL默认的date_trunc('week', ...)是从周一开始计算的，所以要适配周日到周六的范围，我们需要对起始日期做一点偏移：

SELECT * 
FROM your_table
WHERE datecolumn >= date_trunc('week', CURRENT_DATE) - INTERVAL '1 day'
  AND datecolumn < date_trunc('week', CURRENT_DATE) + INTERVAL '7 days';

代码解释： #

• date_trunc('week', CURRENT_DATE)会返回当前周周一的零点时间，减1天就得到本周日的零点，作为筛选的起始时间。
• 加7天则得到下周日的零点，用<而不是<=，可以完整包含周六当天所有非零点的时间数据（避免BETWEEN导致的闭区间遗漏问题）。

如果你想基于当前日期动态计算周范围，也可以用extract(dow ...)函数（dow返回0=周日，6=周六）：

SELECT *
FROM your_table
WHERE datecolumn >= CURRENT_DATE - (extract(dow FROM CURRENT_DATE) || ' days')::INTERVAL
  AND datecolumn < CURRENT_DATE + (6 - extract(dow FROM CURRENT_DATE) || ' days')::INTERVAL + INTERVAL '1 day';

这个写法会自动计算当前日期到本周日的天数，往前推到周起始，往后推到周结束。

这里我们可以利用PostgreSQL的日期格式化函数，先定位到目标周的起始日期，再做范围筛选：

SELECT *
FROM your_table
WHERE datecolumn >= to_date('2020-15', 'IYYY-IW') - INTERVAL '1 day'
  AND datecolumn < to_date('2020-15', 'IYYY-IW') + INTERVAL '7 days';

代码解释： #

• to_date('YYYY-WW', 'IYYY-IW')是ISO周标准的写法：IYYY代表ISO年份，IW代表ISO周数，这个函数会返回目标周周一的零点，减1天就得到我们需要的周日起始点。
• 同样用< 下周日零点来包含整周的所有数据。

注意：ISO周的规则是每周从周一开始，一年的第一周必须包含当年的1月4日（至少4天在当年）。如果你的业务周定义不是ISO标准，可能需要调整起始日期的计算逻辑，但ISO周是最通用的标准周定义。

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