On Mon, Jun 27, 2011 at 1:49 PM, David E. Wheeler <da...@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 rather that 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 own function 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: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 (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?" -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers