How to group by week use sunday based week-CodePudding

is it possible to group by week use sunday start based day in posgresql? if so, what would be the logic?

CodePudding user response：

Alternatively, you can use date_trunc on any version. When you truncate by week it will use Monday, but that can be resolved by adding a day so that Sunday dates roll to the next week:

select
  date_trunc ('week', my_date   1)
from
  my_table
group by
  date_trunc ('week', my_date   1)

When you render the final output, just be sure to subtract the day from the field (no need to do that in the group-by):

select
  date_trunc ('week', my_date   1) - interval '1 day' as sunday,
  count (*)
from
  my_table
group by
  date_trunc ('week', my_date   1)

If my_date is a timestamp (below would also work for a date), then:

date_trunc ('week', my_date   interval '1 day')

instead of:

date_trunc ('week', my_date   1)

You can also encapsulate this into a function, but it seems overkill unless you are using this all over the place.

create or replace function sunday(input_date date)
returns date
language sql
as
$BODY$
  select (date_trunc ('week', input_date   1) - interval '1 day')::date
$BODY$

select
  sunday(my_date), count (*)
from
  my_table
group by
  sunday(my_date)

CodePudding user response：

Yes it is possible. Use date_bin function. It is available in PG version 14 and onwards.
Here is an implementation for versions prior to PG14.
An illustration:

select date_bin('1 week', d, '2022-01-02')::date as db, count(1)
from generate_series('2022-07-01'::timestamp, '2022-10-01', '1 day') as d
group by db
order by db;

/*
2022-01-02 is a Sunday

db        |count|
---------- ----- 
2022-06-26|    3|
2022-07-03|    7|
2022-07-10|    7|
2022-07-17|    7|
2022-07-24|    7|
2022-07-31|    7|
2022-08-07|    7|
2022-08-14|    7|
2022-08-21|    7|
2022-08-28|    7|
2022-09-04|    7|
2022-09-11|    7|
2022-09-18|    7|
2022-09-25|    6|
*/