Postgresql Create Timeframe Using One Column

Question

I have a yearmonth column which outputs:

SELECT yearmonth FROM table_1

yearmonth
202201
202202
202203
202204
202205

I would need to create some sort of output that selects the last 6 months per yearmonth. So the desired output would look like:

period	yearmonth
202201	202201
202201	202112
202201	202111
202201	202110
202201	202109
202201	202108
202202	202202
202202	202201
202202	202112
202202	202111
202202	202110
202202	202109

Answer 1

You can do it using a CTE subquery to get your table_1 data and then generate_series() to create a series of dates.

With test data as:

create table table_1 as
select unnest(array[202201, 202202, 202203, 202204, 202205 ]) as yearmonth;

Query:

with months as 
(select yearmonth::text, yearmonth::text || '01' as yearmonthday from table_1)
select 
    months.yearmonth, 
    generate_series(months.yearmonthday::date - '6 months'::interval , months.yearmonthday::date, '1 month'::interval)
from months;

Note that your date format YYYYMM does not parse correctly, so I have appended '01' to make the date format YYYYMMDD as "yearmonthday".

Produces the following:

yearmonth	generate_series
202201	2021-07-01 00:00:00
202201	2021-08-01 00:00:00
202201	2021-09-01 00:00:00
202201	2021-10-01 00:00:00
202201	2021-11-01 00:00:00
202201	2021-12-01 00:00:00
202201	2022-01-01 00:00:00
202202	2021-08-01 00:00:00
202202	2021-09-01 00:00:00
202202	2021-10-01 00:00:00
…