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.