Re: generate_series with month intervals - Mailing list pgsql-general

From Marcus Engene
Subject Re: generate_series with month intervals
Date
Msg-id 45A69B97.4060305@engene.se
Whole thread Raw
In response to Re: generate_series with month intervals  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-general
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

pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: ORDER BY col is NULL in UNION causes error?
Next
From: Magnus Hagander
Date:
Subject: Re: [HACKERS] Checkpoint request failed on version 8.2.1.