Postgresql generate_series of months

sql postgresql time-series generate-series set-returning-functions
Pyrite picture Pyrite · Sep 16, 2011 · Viewed 21.7k times · Source

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.

Answer

a_horse_with_no_name picture a_horse_with_no_name · Sep 16, 2011 
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')

Related questions

Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More
How do I (or can I) SELECT DISTINCT on multiple columns?

I need to retrieve all rows from a table where 2 columns combined are all different. So I want all the sales that do not have any other sales that happened on the same day for the same price. The sales …

Read More
How to select the nth row in a SQL database table?

I'm interested in learning some (ideally) database agnostic ways of selecting the nth row from a database table. It would also be interesting to see how this can be achieved using the native functionality of the following databases: SQL Server …

Read More