CODEWITHSUNDEEP
postgresql
Adam12344
Adam12344

Reputation: 1053

Creating a table of last 12 month in Postgresql

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?

Upvotes: 3

Views: 1811

Answers (1)

ferhatelmas
ferhatelmas

Reputation: 3978

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
;

Upvotes: 4

Related Questions