Below is the query I have tried:

with weeks as (
select d::date start_date, case when to_char(d::date 6,'Month') = 
to_char(d::date,'Month') then d::date  6 else (date_trunc('month', 
to_timestamp(to_char(d::date,'YYYY-MM-DD') ,'YYYY-MM-DD HH:MI:SS'))   interval '1 
month - 1 day')::date end as end_date
from generate_series('2022-01-01', '2022-07-30', '7d'::interval) d
)

I want results as:

And it should go from 2022-01-29 to 2022-01-31 then for next month it should start from '2022-02-01' to '2022-02-07' and should continue.

CodePudding user response：

I would combine to generate_series() here. One for the start of the months, and one for the week starts.

Evaluating the week end can be achieved using the least() function by calculating the last day of the month:

select gw.week_start::date start_date, 
       least(gw.week_start::date   6, date_trunc('month', gw.week_start)   interval '1 month - 1 day')::date as end_date
from generate_series('2022-01-01', '2022-07-30', interval '1 month') as gm(month_start)
  cross join generate_series(gm.month_start, gm.month_start   interval '1 month - 1 day', interval '1 week') as gw(week_start);

This returns:

start_date | end_date  
----------- -----------
2022-01-01 | 2022-01-07
2022-01-08 | 2022-01-14
2022-01-15 | 2022-01-21
2022-01-22 | 2022-01-28
2022-01-29 | 2022-01-31
2022-02-01 | 2022-02-07
2022-02-08 | 2022-02-14
2022-02-15 | 2022-02-21
2022-02-22 | 2022-02-28
2022-03-01 | 2022-03-07
2022-03-08 | 2022-03-14
2022-03-15 | 2022-03-21
2022-03-22 | 2022-03-28
2022-03-29 | 2022-03-31
2022-04-01 | 2022-04-07
2022-04-08 | 2022-04-14
2022-04-15 | 2022-04-21
2022-04-22 | 2022-04-28
2022-04-29 | 2022-04-30
2022-05-01 | 2022-05-07
2022-05-08 | 2022-05-14
2022-05-15 | 2022-05-21
2022-05-22 | 2022-05-28
2022-05-29 | 2022-05-31
2022-06-01 | 2022-06-07
2022-06-08 | 2022-06-14
2022-06-15 | 2022-06-21
2022-06-22 | 2022-06-28
2022-06-29 | 2022-06-30
2022-07-01 | 2022-07-07
2022-07-08 | 2022-07-14
2022-07-15 | 2022-07-21
2022-07-22 | 2022-07-28
2022-07-29 | 2022-07-31