Question 114450 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to group by week use sunday based week

How to extract grouped data for the week using postgresql. Using sunday as the starting day of the week.

The main problem is how to make the week count from Sunday instead of Monday

Tags:

2

Answers


  1. 0

    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|
*/
    Login or Signup to reply.
  2. 0

    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)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search