2
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?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Ethan Smih
  • 83
  • 1
  • 9

1 Answers1

8

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:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • worked after using '1' day instead of '1 day' – Ethan Smih Sep 30 '21 at 04:23
  • Works as is in Postgres, trust me. Or don't, and try it. – Erwin Brandstetter Sep 30 '21 at 04:25
  • Strange! I tried it as it is didn't work but then I changed it to '1' day it worked. I am working on Amazon Athena, Dbeaver. – Ethan Smih Sep 30 '21 at 04:53
  • @Ethan: Amazon Athena is not Postgres (at all!). Athena only accepts standard SQL (or so it claims). Postgres allows more reasonable [input syntax for intervals](https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-INTERVAL-INPUT) like the above, too. Hardcore SQL standard syntax would be `interval '1 0:00:00'` or `interval '1 0:0'` for "1 day". – Erwin Brandstetter Sep 30 '21 at 12:36
  • Oh! I think it is presto. – Ethan Smih Oct 01 '21 at 15:56