Make date_trunc() start on Sunday instead of Monday

Question

Select date_trunc('week',dateTime) Date_week, Max(Ranking) Runing_Total_ID
    from (select datetime, id , dense_rank () over (order by datetime) as Ranking
          from Table1)
 group by 1

This query is working for me to give me the running total of total IDs by week. But the week starts on Monday in Postgres by default. Is there any way to change the week start to SUNDAY?

Answer 1

_{It's unclear how counting distinct dates translates to "the running total of total IDs by week". But let's focus on the core question in the title.}

Postgres 14 or newer

Use date_bin():

-- date_bin() in Postgres 14+
SELECT date_bin('7 days'              -- stride
              , datetime              -- source
              , timestamp '2023-1-1'  -- origin
               ) AS date_week
     , max(ranking) AS runing_total_id
FROM  (SELECT datetime, dense_rank() OVER (ORDER BY datetime) AS ranking FROM tbl) sub
GROUP  BY 1
ORDER  BY 1, 2;  -- optional

fiddle

The "origin" (timestamp '2023-1-1') must be a Sunday in this case! It's also a valid timestamp literal. Don't mix timestamp & timestamptz to avoid corner-case errors.
See:

• What is the fastest way to truncate timestamps to 5 minutes in Postgres?
• Generating time series between two dates in PostgreSQL

Postgres 13 or older

Shift the timestamp back and forth. Add a day before feeding the timestamp to date_trunc(), then subtract again:

SELECT date_trunc('week', datetime + interval '1 day') - interval '1 day' AS date_week
     , max(rnk) AS runing_total_id
FROM  (SELECT datetime, dense_rank() OVER (ORDER BY datetime) AS rnk FROM tbl) sub
GROUP  BY 1
ORDER  BY 1, 2;  -- optional

fiddle

See:

• PostgreSQL custom week number - first week containing Feb 1st