Re: using generate_series to iterate through months

Bill Reynolds <Bill(dot)Reynolds(at)ateb(dot)com> wrote:

> Ok, I'm a bit stumped on getting my group by query to work which
> iterates through a number of months that basically a generate_series
> provides for me.
>
> Here is what I am using in the from clause (along with other tables) to
> generate the series of numbers for the number of months. This seems to
> work:
> generate_series( 0, ((extract(years from age(current_date,
> DATE('2008-05-01')))*12) + extract(month from age(current_date,
> DATE('2008-05-01'))))::INTEGER) as s(a)
>
> Next, I want to group by and compare against dates in my table. These
> are the two parts I can't get to work:
>
> In the select part:
> select DATE('2008-05-01') + interval (s.a??? ' months') as Month_of

The trick is easy:

test=*# select current_date + s.a * '1 month'::interval from (select
generate_series(0,10) as a) as s;
?column?
---------------------
2009-08-03 00:00:00
2009-09-03 00:00:00
2009-10-03 00:00:00
2009-11-03 00:00:00
2009-12-03 00:00:00
2010-01-03 00:00:00
2010-02-03 00:00:00
2010-03-03 00:00:00
2010-04-03 00:00:00
2010-05-03 00:00:00
2010-06-03 00:00:00
(11 Zeilen)

I think, you can solve your problem now.

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°