Re: using generate_series to iterate through months
От | Andreas Kretschmer |
---|---|
Тема | Re: using generate_series to iterate through months |
Дата | |
Msg-id | 20090803154722.GA17251@tux обсуждение исходный текст |
Ответ на | using generate_series to iterate through months ("Bill Reynolds" <Bill.Reynolds@ateb.com>) |
Список | pgsql-general |
Bill Reynolds <Bill.Reynolds@ateb.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°
В списке pgsql-general по дате отправления: