Select month between 2 date in postgresql

Question

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.

Does this answer your question? [Postgresql query between date ranges](https://stackoverflow.com/questions/23335970/postgresql-query-between-date-ranges) — jeremy-denis, Feb 25 '22 at 07:30
No, because I need it automatic for everymonth to put it on my dashboard. — Mạnh Tô, Feb 25 '22 at 07:35
So you want 4 rows with three columns? (Name of the Month, start date, end date) — , Feb 25 '22 at 07:40
No, I need only 1 column for the month. Is there any query that can put 4 rows with one column? — Mạnh Tô, Feb 25 '22 at 07:44
BTW I'm too fussed about the format, anything that works will do — Mạnh Tô, Feb 25 '22 at 07:46

score 0 · Accepted Answer · answered Feb 25 '22 at 08:00

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)

Ok, it's working. But for example: If i want to count `user_id` in tbl kyc. How can i do it with that query. — Mạnh Tô, Feb 25 '22 at 08:57

Select month between 2 date in postgresql

1 Answers1