如何修改PostgreSQL中date_trunc函数周截断的起始日为周日而非周一

方案1：日期偏移法（改动最小，适配原有逻辑） #

PostgreSQL默认date_trunc('week', xxx)返回的是当周周一的日期，只要给原日期加1天再取周，再减回1天，就能得到以周日为起始的周起始日期，逻辑验证：

• 周日日期加1天变为周一，date_trunc('week')取到当周周一，减1天回到原周日，即为周起始
• 周一到周六的日期加1天后，date_trunc('week')仍取到本周周一，减1天回到上周周日，刚好是本周的周起始

修改后的完整SQL如下：

SELECT 
  (date_trunc('week', dateTime + INTERVAL '1 day') - INTERVAL '1 day')::date AS Date_week,
  MAX(Ranking) AS Runing_Total_ID
FROM (
  SELECT 
    datetime, 
    id,
    dense_rank() OVER (ORDER BY datetime) AS Ranking
  FROM Table1
) t
GROUP BY 1
ORDER BY 1;

末尾加::date可以直接返回日期格式，去掉默认带的时分秒。

方案2：dow计算法（灵活适配任意周起始） #

如果后续需要调整为其他周起始日，可以用extract(dow from datetime)计算偏移量，dow的取值为0（周日）到6（周六），减去对应天数即可直接得到当周周日：

SELECT 
  (datetime - (extract(DOW FROM datetime) || ' days')::interval)::date AS Date_week,
  MAX(Ranking) AS Runing_Total_ID
FROM (
  SELECT 
    datetime, 
    id,
    dense_rank() OVER (ORDER BY datetime) AS Ranking
  FROM Table1
) t
GROUP BY 1
ORDER BY 1;

两种方案返回的周统计结果完全一致，可根据使用习惯选择。

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