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 — jeremy-denis
– jeremy-denis, Commented Feb 25, 2022 at 7:30
No, because I need it automatic for everymonth to put it on my dashboard. — Mạnh Tô
– Mạnh Tô, Commented Feb 25, 2022 at 7:35
So you want 4 rows with three columns? (Name of the Month, start date, end date) — user330315
– user330315, Commented Feb 25, 2022 at 7: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ô
– Mạnh Tô, Commented Feb 25, 2022 at 7:44
BTW I'm too fussed about the format, anything that works will do — Mạnh Tô
– Mạnh Tô, Commented Feb 25, 2022 at 7:46

user330315 · Accepted Answer · 2022-02-25 08:00:27Z

0

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)

answered Feb 25, 2022 at 8:00

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

Mạnh Tô Over a year ago

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.

Collectives™ on Stack Overflow

Select month between 2 date in postgresql

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related