PostgreSQL中如何实现按2周粒度的DATE_TRUNC日期截断功能

核心逻辑 #

PostgreSQL 原生没有直接支持双周截断的date_trunc参数，你可以通过14天周期的时间差计算实现对齐，匹配你需要的规则：2021年4月5日_{4月18日统一对齐到4月5日，4月19日}5月2日统一对齐到4月19日，以此类推。

实现代码 #

通用版本（无需硬编码基准日期） #

如果Date字段已经是date类型，直接使用：

SELECT
  Date,
  CustID,
  '1970-01-01'::date + ((DATE_PART('day', Date - '1970-01-04'::date))::INTEGER / 14) * 14 + 3 AS biweekly_start
FROM 你的表名;

如果Date字段是DD-MM-YYYY格式的字符串，先做类型转换：

SELECT
  Date,
  CustID,
  '1970-01-01'::date + ((DATE_PART('day', TO_DATE(Date, 'DD-MM-YYYY') - '1970-01-04'::date))::INTEGER / 14) * 14 + 3 AS biweekly_start
FROM 你的表名;

自定义基准版本 #

如果你的业务双周有固定的起始基准（比如你示例中的2021-04-05），可以用更简洁的写法：

SELECT
  Date,
  CustID,
  '2021-04-05'::date + (EXTRACT(WEEK FROM Date) - 14)::INTEGER / 2 * INTERVAL '2 weeks' AS biweekly_start
FROM 你的表名;

注：14是2021年4月5日对应的年度周数，你可以替换为自己基准日期对应的周数。

结果验证 #

用你提供的示例数据测试，返回结果完全符合预期：

调整说明 #

如果需要修改双周的起始周规则，比如从周一开始改成从周日开始，只需要调整计算逻辑里的时间偏移量即可：

• 周日为双周起始：把减法参考日期改为'1970-01-03'::date，最后加的数字改为2

内容的提问来源于stack exchange，提问作者Muhammad Ariq Naufal