Convert date into Weeks in PostgreSQL

Question

I want to add week against dates, start day of the date should be Saturday. can anyone tell me how to do it. enter image description here

I tried below code but it didn't solve my purpose

select date_part('week',dates ::date ) as weekly,
       date_part('year',dates ::date ) as year,
        
count(dates)
from finaldata
group by weekly,year
order by weekly,year; 

Answer 1

You cannot get what you want form the the standard Postgres date functions date_part('week', ...) nor extract(week from ...). This is because those routines are define with a week in terms of the ISP-8601 Standard (see Documentation ).

week

The number of the ISO 8601 week-numbering week of the year. By definition, ISO weeks start on Mondays and the first week of a year contains January 4 of that year. In other words, the first Thursday of a year is in week 1 of that year.

Your best option is creating a calendar table which defines your particular requirement. Further it is not sufficient just saying the week starts on Saturday. As the ISO definition you must define how the first day of the year is defined. The table and procedure following use define a week as:

Non standard week definition
1. First week of year begins on Jan 1.
2. Subject to #1 above, each week begins on Saturday.
3. Subject to #1 and #2 above, each week contains 7 days.

create table non_standard_calendar( 
             nsw_id              integer  generated always as identity
                                          primary key
           , gegorian_date       date     not null 
                                          unique
           , year                smallint
           , week_of_year        smallint
           , day_of_year         smallint
           , doy_of_week         smallint
           );

Then the procedure can be used to generate the calendar for the specified year.

create or replace procedure generate_non_standard_calendar(yr_in integer)
   language sql
 as $$
    with recursive ns_cal (gdate     -- gegorian date
                          ,nsc_yr    -- yr_in
                          ,nsc_woy   -- non_standard week of year
                          ,nsc_doy   -- non_standard day of year
                          ,nsc_dow   -- non-standard day of week
                          ) as
                   ( select make_date(yr_in,01,01)
                          , yr_in 
                          , 1
                          , 1
                          , 1
                     union all 
                     select gdate + 1                         -- gdate
                          , nsc_yr                            -- nsc_yr 
                          , case when to_char(gdate, 'dy') = 'fri'
                                 then nsc_woy + 1
                                 else nsc_woy 
                            end 
                          , (nsc_doy + 1)                     -- nsc_doy
                          , case when to_char(gdate, 'dy') = 'fri'
                                 then 1
                                 else nsc_dow+1
                            end                  -- nsc_dow
                       from ns_cal 
                      where extract(year from gdate + 1) = yr_in
                  )
     insert into  non_standard_calendar( gegorian_date 
                                       , year                 
                                       , week_of_year         
                                       , day_of_year       
                                       , doy_of_week 
                                       ) 
            select gdate     -- gegorian date
                 , nsc_yr    -- yr_in
                 , nsc_woy   -- non_standard week of year
                 , nsc_doy   -- non_standard day of year
                 , nsc_dow   -- non-standard day of week
              from ns_cal;
$$;
 
-- generate calendar for 2023 
do $$
begin
   call generate_non_standard_calendar(2023);
end;
$$; 

You can then get what you had asked for with:

with base(gdate, woy) as 
     (select gegorian_date, week_of_year
        from non_standard_calendar
       where gegorian_date  in ('2023-08-05'::date, '2023-08-12'::date)
     ) 
select to_char(gegorian_date, 'dd-mm-yyyy') "Dates"
     , 'Week_' || to_char(gdate, 'dd-Mon')  "Week"
  from non_standard_calendar
  join base on woy = week_of_year;

See demo here.

NOTES:

Since the procedure consists of a only an SQL statement it can be pulled out and run as straight SQL.
The procedure has not been exhaustively tested for various days for first_of_year.