Re: BUG #7797: datetime + '1 month'::interval is going outside of a month's bounds

On Mon, Jan 01/07/13, 2013 at 02:29:43PM +0000, afonit@gmail.com wrote:
> The following bug has been logged on the website:
>
> Bug reference:      7797
> Logged by:          Jared Thompson
> Email address:      afonit@gmail.com
> PostgreSQL version: 9.2.2
> Operating system:   Ubuntu 12.04
> Description:
>
> This query:
>
> select
> '2012-01-31'::date + '1 month'::interval
>
> Produces this result:
> "2012-02-29 00:00:00"
>
> 2012-02-29 is not a valid date.  February only goes to 28 days.
February has 29 days in 2012.

>
> I am not sure what the expected behavior should be, but what I was expecting
> is (in the above instance):
> Adding one month to 2012-01-31 would yield the last day of February
> 2012-01-28
>
> and if we added one month to 2012-01-25 we do get 2012-02-25.
>
> It appears that the +'1 month'::interval is making it the next month and
> same date - but if there aren't the same number of days in the months it
> seems to be breaking as noted in the first example.
>
> Again, not sure what the expected behavior is but I would think that 'last
> day of a month' + '1 month'::interval would give 'last day of next month'.
>
> I realize on January 29-30th I am not sure what the expected behavior should
> be.
>

'1 month'::interval is the same as '30 days'::interval.

-Ryan Kelly