Thread: generate_series with month intervals

Hi list,

I'd like to generate the latest year dynamically with generate_series.
This select works day wise:

select date_trunc ('month', now())::date + s.a
from generate_series(0, 11) as s(a)

I tried this but it didn't work:

select date_trunc ('month', now())::date + interval s.a || ' months'
from generate_series(0, 11) as s(a)

This works but looks grotesque:

select distinct date_trunc ('month', now()::date + s.a)::date
from generate_series(0, 365) as s(a)

Is there a way to do this more elegantly?

Best regards,
Marcus

Marcus Engene <mengpg@engene.se> writes:
> I tried this but it didn't work:

> select date_trunc ('month', now())::date + interval s.a || ' months'
> from generate_series(0, 11) as s(a)

People keep trying that :-(.  The "typename 'foo'" syntax is for a
*literal constant* only.  Instead use multiplication, something like

select (date_trunc('month', now())::date + s.a * '1 month'::interval)::date
from generate_series(0, 11) as s(a)

You'll want the cast back to date as the last step here because
date+interval will give timestamp.

            regards, tom lane

Tom Lane skrev:
> Marcus Engene <mengpg@engene.se> writes:
>> I tried this but it didn't work:
>
>> select date_trunc ('month', now())::date + interval s.a || ' months'
>> from generate_series(0, 11) as s(a)
>
> People keep trying that :-(.  The "typename 'foo'" syntax is for a
> *literal constant* only.  Instead use multiplication, something like
>
> select (date_trunc('month', now())::date + s.a * '1 month'::interval)::date
> from generate_series(0, 11) as s(a)
>
> You'll want the cast back to date as the last step here because
> date+interval will give timestamp.
>
>             regards, tom lane

Wonderful! Worked like a charm. Thanks!

Marcus

On Thu, Jan 11, 2007 at 20:07:29 +0100,
  Marcus Engene <mengpg@engene.se> wrote:
> Hi list,
>
> I'd like to generate the latest year dynamically with generate_series.
> This select works day wise:
>
> This works but looks grotesque:
>
> select distinct date_trunc ('month', now()::date + s.a)::date
> from generate_series(0, 365) as s(a)
>
> Is there a way to do this more elegantly?

Are you just trying to get a list off the first of the month for the current
month and the next 11 months after that? For that you want to get the first
of the current month and then add s.a * '1 month' to it for 0 to 11.