Make date_trunc() start on Sunday instead of Monday-CodePudding

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?

CodePudding user response：

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(ranking) AS runing_total_id
FROM (
   SELECT datetime, dense_rank() OVER (ORDER BY datetime) AS ranking
   FROM   table1
   ) sub
GROUP  BY 1;

See:

PostgreSQL custom week number - first week containing Feb 1st