On 06/27/2011 10:49 AM, David E. Wheeler wrote:
> Hackers,
>
> I'm curious about behavior such as this:
>
> bric=# select generate_series('2011-05-31'::timestamp , '2012-04-01'::timestamp, '1 month');
> generate_series
> ---------------------
> 2011-05-31 00:00:00
> 2011-06-30 00:00:00
> 2011-07-30 00:00:00
> 2011-08-30 00:00:00
> 2011-09-30 00:00:00
> 2011-10-30 00:00:00
> 2011-11-30 00:00:00
> 2011-12-30 00:00:00
> 2012-01-30 00:00:00
> 2012-02-29 00:00:00
> 2012-03-29 00:00:00
>
> It seems to me that this is subject to interpretation. If I was building a calendaring app, for example, I might
ratherthat the results were:
>
> generate_series
> ---------------------
> 2011-05-31 00:00:00
> 2011-06-30 00:00:00
> 2011-07-31 00:00:00
> 2011-08-31 00:00:00
> 2011-09-30 00:00:00
> 2011-10-31 00:00:00
> 2011-11-30 00:00:00
> 2011-12-31 00:00:00
> 2012-01-31 00:00:00
> 2012-02-29 00:00:00
> 2012-03-31 00:00:00
>
> Is there some way to change the interpretation of interval calculation like this? Or would I just have to write my
ownfunction to do it the way I want?
>
> Thanks,
>
> David
>
>
That's just how intervals that represent varying periods of time work.
You would need to write your own. But a series of end-of-month dates is
pretty easy:
select generate_series('2011-06-01'::timestamp ,
'2012-04-01'::timestamp, '1 month') - '1 day'::interval; ?column?
--------------------- 2011-05-31 00:00:00 2011-06-30 00:00:00 2011-07-31 00:00:00 2011-08-31 00:00:00 2011-09-30
00:00:002011-10-31 00:00:00 2011-11-30 00:00:00 2011-12-31 00:00:00 2012-01-31 00:00:00 2012-02-29 00:00:00 2012-03-31
00:00:00
Cheers,
Steve
