Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version


PostgreSQL 12.16 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-18), 64-bit



SELECT 1


create or replace 
function week_dates( do_date_in date) 
 returns table (week_num integer, first_date date, last_date date)
 language sql 
 immutable strict
as $$
    with recursive date_list(week_num,first_date,terminate_date) as 
         ( select 1
                , date_trunc('month', do_date_in)::timestamp
                , (date_trunc('month', do_date_in) + interval '1 month' - interval '1 day')::timestamp
           union all 
           select week_num+1, (first_date+interval '7 day'), terminate_date
             from date_list
            where first_date+interval '6 day'  < terminate_date::timestamp    
         ) 
    select week_num
         , first_date::date
         , case when (first_date+interval '6 day')::date > terminate_date 
                then terminate_date::date 
                else (first_date+interval '6 day')::date 
           end last_date
    from date_list;
$$; 

CREATE FUNCTION


create or replace 
function week_dates_pivotd(do_date_in date) 
 returns table( "Start of month" date 
              , "End of Month"   date
              , "Week 1"         text 
              , "Week 2"         text
              , "Week 3"         text
              , "Week 4"         text 
              , "Week 5"         text 
              ) 
 language sql
 immutable strict
as $$
    select week1s  
         , case when week5e is null 
                then week4e
                else week5e
           end "end of month"
         , week1s  || ' -  ' || week1e  
         , week2s  || ' -  ' || week2e  
         , week3s  || ' -  ' || week3e  
         , week4s  || ' -  ' || week4e  
         , week5s  || ' -  ' || week5e  
      from ( select  max(case when (week_num=1) then first_date else NULL end) as week1s 
                  ,  max(case when (week_num=1) then last_date  else NULL end) as week1e         
                  ,  max(case when (week_num=2) then first_date else NULL end) as week2s
                  ,  max(case when (week_num=2) then last_date  else NULL end) as week2e      
                  ,  max(case when (week_num=3) then first_date else NULL end) as week3s
                  ,  max(case when (week_num=3) then last_date  else NULL end) as week3e      
                  ,  max(case when (week_num=4) then first_date else NULL end) as week4s
                  ,  max(case when (week_num=4) then last_date  else NULL end) as week4e      
                  ,  max(case when (week_num=5) then first_date else NULL end) as week5s
                  ,  max(case when (week_num=5) then last_date  else NULL end) as week5e      
               from week_dates(do_date_in)
            ) w ; 
$$;

CREATE FUNCTION


select * from week_dates_pivotd(current_date); 


Start of month
End of Month
Week 1
Week 2
Week 3
Week 4
Week 5


2023-10-01
2023-10-31
2023-10-01 -  2023-10-07
2023-10-08 -  2023-10-14
2023-10-15 -  2023-10-21
2023-10-22 -  2023-10-28
2023-10-29 -  2023-10-31



SELECT 1