21

I'm trying to generate a series in PostgreSQL with the generate_series function. I need a series of months starting from Jan 2008 until current month + 12 (a year out). I'm using and restricted to PostgreSQL 8.3.14 (so I don't have the timestamp series options in 8.4).

I know how to get a series of days like:

select generate_series(0,365) + date '2008-01-01'

But I am not sure how to do months.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Pyrite
  • 277
  • 1
  • 2
  • 8

5 Answers5

28
select DATE '2008-01-01' + (interval '1' month * generate_series(0,11))

Edit

If you need to calculate the number dynamically, the following could help:

select DATE '2008-01-01' + (interval '1' month * generate_series(0,month_count::int))
from (
   select extract(year from diff) * 12 + extract(month from diff) + 12 as month_count
   from (
     select age(current_timestamp, TIMESTAMP '2008-01-01 00:00:00') as diff 
   ) td
) t

This calculates the number of months since 2008-01-01 and then adds 12 on top of it.

But I agree with Scott: you should put this into a set returning function, so that you can do something like select * from calc_months(DATE '2008-01-01')

  • I made the following out of what you suggested: `SELECT to_char(DATE '2008-01-01' + (interval '1 month' * generate_series(0,56)), 'Mon-YY') AS months` The question is now, how to calculate 56 instead of statically defining it in the query above. 56 is the number of months since Jan 2008 + 12. – Pyrite Sep 16 '11 at 22:32
  • `select ((extract (year from current_date + interval '12' month) - 2008) * 12) + (extract (month from current_date + interval '12' month) - 1)` will give you the right number of months. – Mike Sherrill 'Cat Recall' Sep 16 '11 at 23:19
  • 1
    I think it's time you wrote a function in plsql or plpgsql – Scott Marlowe Sep 18 '11 at 07:23
9

You can interval generate_series like this:

SELECT date '2014-02-01' + interval '1' month * s.a AS date
  FROM generate_series(0,3,1) AS s(a);

Which would result in:

        date         
---------------------
 2014-02-01 00:00:00
 2014-03-01 00:00:00
 2014-04-01 00:00:00
 2014-05-01 00:00:00
(4 rows)

You can also join in other tables this way:

SELECT date '2014-02-01' + interval '1' month * s.a AS date, t.date, t.id
  FROM generate_series(0,3,1) AS s(a)
LEFT JOIN <other table> t ON t.date=date '2014-02-01' + interval '1' month * s.a;
patrick
  • 838
  • 8
  • 11
4

You can interval generate_series like this:

SELECT TO_CHAR(months, 'YYYY-MM') AS "dateMonth"
FROM generate_series(
    '2008-01-01' :: DATE,
    '2008-06-01' :: DATE ,
    '1 month'
) AS months

Which would result in:

 dateMonth 
-----------
 2008-01
 2008-02
 2008-03
 2008-04
 2008-05
 2008-06
(6 rows)
alexandre-rousseau
  • 2,321
  • 26
  • 33
  • 2
    But BEWARE of ends of months! ```SELECT TO_CHAR(months, 'YYYY-MM-DD') AS "dateMonth" FROM generate_series( '2008-01-31' :: DATE, '2008-06-30' :: DATE , '1 month' ) AS months``` will generate series of `"2008-01-31" "2008-02-29" "2008-03-29" "2008-04-29" "2008-05-29" "2008-06-29"` – Foton Apr 08 '20 at 08:09
1

In the generated_series() you can define the step, which is one month in your case. So, dynamically you can define the starting date (i.e. 2008-01-01), the ending date (i.e. 2008-01-01 + 12 months) and the step (i.e. 1 month).

SELECT generate_series('2008-01-01', '2008-01-01'::date + interval '12 month', '1 month')::date AS generated_dates

and you get

1/1/2008
2/1/2008
3/1/2008
4/1/2008
5/1/2008
6/1/2008
7/1/2008
8/1/2008
9/1/2008
10/1/2008
11/1/2008
12/1/2008
1/1/2009
George Pipis
  • 1,452
  • 16
  • 12
1

Well, if you only need months, you could do:

select extract(month from days)
from(
  select generate_series(0,365) + date'2008-01-01' as days
)dates
group by 1
order by 1;

and just parse that into a date string...

But since you know you'll end up with months 1,2,..,12, why not just go with select generate_series(1,12);?