Re: generate_series() Interpretation

On Mon, Jun 27, 2011 at 1:49 PM, David E. Wheeler <david@kineticode.com> 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? 

It's not hugely difficult to get something pretty appropriate:

emp@localhost->  select generate_series('2011-06-01'::timestamp ,
'2012-04-01'::timestamp, '1 month')- '1 day' ::interval;     ?column?
---------------------2011-05-31 00:00:002011-06-30 00:00:002011-07-31 00:00:002011-08-31 00:00:002011-09-30
00:00:002011-10-3100:00:002011-11-30 00:00:002011-12-31 00:00:002012-01-31 00:00:002012-02-29 00:00:002012-03-31
00:00:00
(11 rows)

That's more or less a bit of "cleverness."  But it's not so grossly
clever as to seem too terribly frightful.
--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"