How to generate series of dates in postgresql that are repeated every end of the month of another date

Question

I want to generate date data using postgresql function "generate_series" , however I have only advanced as far as the following:

SELECT
    ( DATE_TRUNC( 'month', ld ) + '1 month'::INTERVAL - '1day'::INTERVAL )::DATE AS pe_produc
FROM
    GENERATE_SERIES( TIMESTAMP'2022-01-31', TIMESTAMP'2022-3-31', INTERVAL'1 month' ) AS ld

The result of the previous query:

While the date structure I want to generate is as follows:

How should I modify my query to get the desired result?

Thanks in advance.

The same date mes_prod should be repeated for each different date in pe_prod through the generate_series function. — Jean
– Jean, Commented Oct 17, 2022 at 0:41

Jean · Accepted Answer · 2022-10-17 04:01:56Z

-1

With cte command I was able to fix it!

SELECT 
         a.pe_produc::date AS pe_produc
        ,b.mes_produc::date AS mes_produc
FROM 
    (SELECT
     generate_series('2022-01-31'::date
                     , '2022-03-31'::date
                     , interval  '1 month') AS pe_produc
     ,generate_series(1, (DATE_PART('month', '2022-03-31'::date) - DATE_PART('month', '2022-01-31'::date)+1)::int) AS b
     ) A 
    ,(SELECT generate_series('2022-01-31'::date
                       ,'2022-03-31'::date
                       , interval  '1 month') AS mes_produc 
     , generate_series(1, (DATE_PART('month', '2022-03-31'::date) - DATE_PART('month', '2022-01-31'::date)+1)::int) AS b
      ) B

answered Oct 17, 2022 at 4:01

Jean

338 bronze badges

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

1 Comment

Adrian Klaver Over a year ago

That is not a CTE statement. That is joined sub queries.

Collectives™ on Stack Overflow

How to generate series of dates in postgresql that are repeated every end of the month of another date

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related