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

The following bug has been logged on the website:

Bug reference: 7797
Logged by: Jared Thompson
Email address: afonit(at)gmail(dot)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.

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.

On Mon, Jan 01/07/13, 2013 at 02:29:43PM +0000, afonit(at)gmail(dot)com wrote:
> The following bug has been logged on the website:
>
> Bug reference: 7797
> Logged by: Jared Thompson
> Email address: afonit(at)gmail(dot)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

On Mon, Jan 7, 2013 at 12:19 PM, Ryan Kelly <rpkelly22(at)gmail(dot)com> wrote:

>
> February has 29 days in 2012.
>
> Thanks I realize now I was looking at 2013's calendar while typing in 2012

> '1 month'::interval is the same as '30 days'::interval.
>
> -Ryan Kelly
>

Thank you.

On Mon, Jan 7, 2013 at 5:19 PM, Ryan Kelly <rpkelly22(at)gmail(dot)com> wrote:

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

No, it's not.

# select '2012-07-31'::date + '1 month'::interval, '2012-07-31'::date
+ '30 days'::interval;
?column? | ?column?
---------------------+---------------------
2012-08-31 00:00:00 | 2012-08-30 00:00:00

-- Daniele

On Mon, Jan 01/07/13, 2013 at 05:42:40PM +0000, Daniele Varrazzo wrote:
> On Mon, Jan 7, 2013 at 5:19 PM, Ryan Kelly <rpkelly22(at)gmail(dot)com> wrote:
>
> > '1 month'::interval is the same as '30 days'::interval.
>
> No, it's not.
>
> # select '2012-07-31'::date + '1 month'::interval, '2012-07-31'::date
> + '30 days'::interval;
> ?column? | ?column?
> ---------------------+---------------------
> 2012-08-31 00:00:00 | 2012-08-30 00:00:00
>
> -- Daniele

Alright, now I'm thoroughly confused. What magic makes this true:

# select '30 days'::interval = '1 month'::interval;
?column?
----------
t

But not this:

# select '2012-07-31'::timestamp + '1 month'::interval = '2012-07-31'::timestamp + '30 days'::interval;
?column?
----------
f

?

-Ryan Kelly

On Mon, Jan 7, 2013 at 5:47 PM, Ryan Kelly <rpkelly22(at)gmail(dot)com> wrote:

> Alright, now I'm thoroughly confused. What magic makes this true:

"""
Internally interval values are stored as months, days, and seconds.
This is done because the number of days in a month varies, and a day
can have 23 or 25 hours if a daylight savings time adjustment is
involved.
"""

http://www.postgresql.org/docs/9.1/static/datatype-datetime.html

-- Daniele

On 1/7/2013 9:47 AM, Ryan Kelly wrote:
> On Mon, Jan 01/07/13, 2013 at 05:42:40PM +0000, Daniele Varrazzo wrote:
>> On Mon, Jan 7, 2013 at 5:19 PM, Ryan Kelly <rpkelly22(at)gmail(dot)com> wrote:
>>
>>> '1 month'::interval is the same as '30 days'::interval.
>> No, it's not.
>>
>> # select '2012-07-31'::date + '1 month'::interval, '2012-07-31'::date
>> + '30 days'::interval;
>> ?column? | ?column?
>> ---------------------+---------------------
>> 2012-08-31 00:00:00 | 2012-08-30 00:00:00
>>
>> -- Daniele
> Alright, now I'm thoroughly confused. What magic makes this true:
>
> # select '30 days'::interval = '1 month'::interval;
> ?column?
> ----------
> t
>
> But not this:
>
> # select '2012-07-31'::timestamp + '1 month'::interval = '2012-07-31'::timestamp + '30 days'::interval;
> ?column?
> ----------
> f
>
> ?

interval type comparison and timestamp type comparison. It's not the
same comparison.

> -Ryan Kelly
>
>

Tam

afonit(at)gmail(dot)com writes:
> 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.

Uh, 2012 was a leap year, and so 2012-02-29 is a perfectly valid date.

> 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

It did.

regards, tom lane

Ryan Kelly <rpkelly22(at)gmail(dot)com> writes:
> On Mon, Jan 01/07/13, 2013 at 05:42:40PM +0000, Daniele Varrazzo wrote:
>> On Mon, Jan 7, 2013 at 5:19 PM, Ryan Kelly <rpkelly22(at)gmail(dot)com> wrote:
>>> '1 month'::interval is the same as '30 days'::interval.

>> No, it's not.

> Alright, now I'm thoroughly confused. What magic makes this true:
> # select '30 days'::interval = '1 month'::interval;
> ?column?
> ----------
> t

'1 month' and '30 days' are distinct concepts. Some operations will
treat them as equal, if there is no additional info that will nail
down the actual length of the particular month in question. Or if
you like, 30 days is the "default" length of a month.

A related point is that the interval equality operator will say that
'24 hours' and '1 day' are equal, even though they aren't the same
thing --- there are cases where they behave differently, when working
near a DST transition time.

There might be some use for an interval "identity" operator, which would
only say true for completely-indistinguishable interval values; but we
don't have one.

regards, tom lane