using generate_series to iterate through months

From: "Bill Reynolds" <Bill(dot)Reynolds(at)ateb(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: using generate_series to iterate through months
Date: 2009-08-03 15:15:25
Message-ID: 7C0800F63CCF4149AC0FC5EE2A04122606154F81@sr002-2k3exc.ateb.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

and in the where clause:
and DATE(sometimestamp) >= DATE('2008-05-01') || 's.a??? months' -
someOffsetVariable - 30
and DATE(sometimestamp) < DATE('2008-05-01') + s.a + 1 || 'months'

The point is I want to use interval (s.a 'months') to iterate through
them. I can obviously get DATE('2008-05-01') + interval '3 months' to
work, but I want the '3' part to be generated with a series. I have
tried various type casting to no avail. Any help with this syntax would
be appreciated.

Thanks in advance for insight on how to do this.

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Wojtek 2009-08-03 15:18:20 Re: Partition tables
Previous Message Michael Gould 2009-08-03 14:51:46 Partition tables