postgresql show month start date and end date based on given dates

Question

I need to pull out first date and last date of the month from the given from_date and to_date as input, For Example:-

I have my psql output table as the following:

Year	Term	Start Date	End Date
2022	Odd	01-02-2022	30-04-2022
2022	Even	01-07-2022	30-09-2022

I need the output as the following:-

Year	Term	Start Date	End Date
2022	Odd	01-02-2022	28-02-2022
2022	Odd	01-03-2022	31-03-2022
2022	Odd	01-04-2022	30-04-2022
2022	Even	01-07-2022	30-07-2022
2022	Even	01-08-2022	31-08-2022
2022	Even	01-09-2022	30-09-2022

I need the ouput in Postgresql, Pls help Thanks

Answer 1

Your issue boils down to given a period with start and end dates, determine the first and last dates for each month in that period. In Postgres given a date you can determine the first (with date_trunc function) and last of the a month with the expressions:

-- for a given date
date_trunc('month', given_date)   -- returns first day of month  
date_trunc('month', given_date + interval '1 month' - interval '1 day') -- returns last day of month   

Use the first expression above, with generate_series with dates, to create the first of each month in the period. The use the second expression to generate the end of each month. (see demo)

with range_dates (year, term, gsom) as 
     ( select year 
           , term 
           , generate_series(  date_trunc('month', od.start_date)::date
                            ,  date_trunc('month', od.end_date )::date
                            ,  interval '1 month'
                            )::date      
      from output_data od                   
     ) 
select year 
     , term 
     , gsom   start_date
     , (gsom + interval '1 month' - interval '1 day')::date end_date
  from range_dates
 order by term desc, start_date;