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.
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 |