Creating a table of last 12 month in Postgresql

Question

I'm trying to create a table that looks like this (a table of the last 12 months)

month, year
10, 2016
9, 2016
8, 2016
7, 2016
6, 2016
5, 2016
4, 2016
3, 2016
2, 2016
1, 2016
12, 2015
11, 2015

The code I have looks something like this:

select date_part('month', current_date) as order_month,
select date_part('year', current_date) as order_year
union all
select date_part('month', current_date - interval '1 month') as order_month,
select date_part('year', current_date - interval '1 month') as order_year
union all
...

Is there a more concise way of writing this, rather than using 11 unions?

Maybe http://stackoverflow.com/q/7450515/1741542 or http://stackoverflow.com/q/14113469/1741542 helps. — Olaf Dietsche, Oct 31 '16 at 16:00

score 4 · Accepted Answer · answered Oct 31 '16 at 16:04

generate_series(start, stop, step) will be useful such that

SELECT
    EXTRACT('month' FROM d) AS month,
    EXTRACT('year' FROM d) AS year
FROM
    GENERATE_SERIES(
        now(),
        now() - interval '12 months',
        interval '-1 month'
    ) AS d
;

Creating a table of last 12 month in Postgresql

1 Answers1