Re: BUG #14632: Plus and minus operators inconsistency with leap years and year intervals.

On Thu, Apr 27, 2017 at 12:48 AM, Pietro Pugni <pietro(dot)pugni(at)gmail(dot)com>
wrote:

> On Thu, Apr 27, 2017 at 1:01 AM, Pietro Pugni <pietro(dot)pugni(at)gmail(dot)com>
> wrote:
>
>> Expect 1912-02-28 is a correct response. The only reason you think the
>> 29th comes into play here is because you remember that the starting point
>> was the 29th. The system has no such memory.
>>
>> And this is logically wrong because it leads to wrong results. I’m aware
>> that time intervals are difficult to manage but more exactness is needed
>> here: '10 years' must have the same meaning when added to a date and
>> subtracted from it, otherwise it leads to wrong results.
>>
>
> Your suggestion just moves the wrong results to another use case; see my
> response upthread. There is no objectively correct answer here, like you
> seem to think.
>
>
>
> I just found this query clearly shows the underlying algorithm is somewhat
> wrong:
>
> *(I)*
> postgres=# select age('*2017-01-30*'::date, '1912-02-29'::date) +
> '1912-02-29'::date;
> ?column?
> ---------------------
> *2017-01-30* 00:00:00
>
> *(II)*
> postgres=# select age('*2017-02-01*'::date, '1912-02-29'::date) +
> '1912-02-29'::date;
> ?column?
> ---------------------
> *2017-01-30* 00:00:00
>
>
> Query *(II)* should return 2017-02-01 instead of 2017-01-30 and the two
> queries should return different results.
> More generally, ...
>
>
>
>
> I understand why this happens (this has been discussed previously) but
> clearly there’s something wrong..
>
>
> You seem to think that expressions similar to these should yield the same
results:

dateD + intervalA + intervalB

(dateD + intervalA) + intervalB

dateD + (intervalA + intervalB)

But they don't and they couldn't, as many others have mentioned already.
Expressions 1 and 2 are equivalent but the 3rd is not.

And we don't even need leap years to find such "wrong" results. We have
leap seconds (which are rare) and leap months (which are pretty common
although we don't call them leap months). Just try

SELECT
'2017-03-31'::date + '1 month'::interval + '1 month'::interval,
'2017-03-31'::date + ('1 month'::interval + '1 month'::interval) ;

Not all months have the same number of days and there is not a way to fix
that.

It's just not possible to squeeze 31 days in a 30-days month. What
should '2017-03-31'::date
+ '1 month'::interval result and what should '2017-03-30'::date + '1
month'::interval result?

Regards,
Pantelis