Select month between 2 date in postgresql-CodePudding

For example I have a report that requires February have column Created_date start at 2022-01-26 and end at 2022-02-25

How would I set up a query such that I retrieve the following table as a response:

            **Month of the year**

Feb (((Description: Start from 2021/01/26 to 2022/02/25)))

Mar (((Description: Start from 2021/02/26 to 2022/03/25)))

Apr (((Description: Start from 2021/03/26 to 2022/04/25)))

May (((Description: Start from 2021/04/26 to 2022/05/25)))

I put the description to easy understand, i dont need it in the table.

BTW I'm too fussed about the format, anything that works will do.

CodePudding user response：

This generates four rows with three columns:

select to_char(dt, 'Mon') as month,
       dt::date as start_day,
       (dt::date   interval '1 month')::date as end_day
from generate_series(date '2021-01-26', 
                     date '2021-04-26', interval '1 month') as g(dt)

If you want this as a single column, then just format and concatenate the values:

select to_char(dt, 'Mon')||' (((Description: Start from '||
       to_char(dt, 'yyyy/mm/dd')||' to '||
       to_char((dt::date   interval '1 month'), 'yyyy/mm/dd')||')))'
from generate_series(date '2021-01-26', 
                     date '2021-04-26', interval '1 month') as g(dt)