PostgreSQL：如何在SQL查询中修改date_trunc的周起始日（如周六）

这确实是个常见的痛点——PostgreSQL默认的date_trunc('week')以周一作为周起始，没法直接适配像周六这样的自定义起始日，你原来的偏移写法虽然可行，但确实不够直观。我整理了几个更简洁、易维护的方案：

1. 简化偏移表达式（临时使用首选） #

你可以把偏移逻辑简化为更紧凑的写法，核心思路还是通过时间偏移让目标起始日“对齐”到PostgreSQL默认的周一，截断后再偏移回来。比如针对周六作为起始日：

SELECT date_trunc('week', time_id + interval '2 days') - interval '2 days' AS week_start
FROM your_sales_table;

如果需要改成其他起始日，只需要调整偏移天数：

• 周日起始：date_trunc('week', time_id + interval '1 day') - interval '1 day'
• 周五起始：date_trunc('week', time_id + interval '3 days') - interval '3 days'

或者用isodow函数计算动态偏移（语义更清晰）：

SELECT time_id - (extract(isodow from time_id) - 6)::int * interval '1 day' AS week_start
FROM your_sales_table;

这里isodow返回1（周一）到7（周日），减去6后，周六（isodow=6）偏移0天，周日（7）偏移-1天，周一（1）偏移-2天，刚好把所有日期对齐到最近的周六起始点。

2. 创建自定义函数（高频使用首选） #

如果这个需求会反复用到，不如封装成一个语义明确的自定义函数，调用起来更省心：

CREATE OR REPLACE FUNCTION date_trunc_week_sat_start(dt timestamp)
RETURNS timestamp AS $$
BEGIN
    -- 内部复用偏移逻辑，可根据需要修改偏移天数适配其他起始日
    RETURN date_trunc('week', dt + interval '2 days') - interval '2 days';
END;
$$ LANGUAGE plpgsql IMMUTABLE;

之后统计每周销量时直接调用：

SELECT date_trunc_week_sat_start(time_id) AS week_start, SUM(sales_amount) AS weekly_sales
FROM your_sales_table
GROUP BY week_start
ORDER BY week_start;

函数名可以根据起始日调整，比如date_trunc_week_sun_start，团队成员一看就懂。

3. 使用date_bin函数（PostgreSQL 12+ 最灵活方案） #

PostgreSQL 12及以上版本提供了date_bin函数，专门用于自定义周期的时间对齐，完美适配这类需求。你只需要指定周期长度、目标时间，以及一个锚点日期（必须是你想要的周起始日的某一天）：

SELECT date_bin('7 days', time_id, '2024-05-18'::timestamp) AS week_start
FROM your_sales_table;

这里'2024-05-18'是一个周六的日期，date_bin会把所有time_id对齐到最近的、以该锚点为起始的7天周期的开始时间。这个方法的优势是完全灵活——不管你需要周起始是周几，甚至是其他自定义周期（比如10天），都能轻松实现。

内容的提问来源于stack exchange，提问作者Syed Mohammad Hosseini