using generate_series to iterate through months
| От | Bill Reynolds |
|---|---|
| Тема | using generate_series to iterate through months |
| Дата | |
| Msg-id | 7C0800F63CCF4149AC0FC5EE2A04122606154F81@sr002-2k3exc.ateb.com обсуждение исходный текст |
| Ответ на | Partition tables (Michael Gould <mgould@intermodalsoftwaresolutions.net>) |
| Ответы |
Re: using generate_series to iterate through months
Re: using generate_series to iterate through months Re: using generate_series to iterate through months |
| Список | 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.
В списке pgsql-general по дате отправления: