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

On Wed, Apr 26, 2017 at 11:23 AM, <pietro.pugni@gmail.com> wrote:

The following bug has been logged on the website:

Bug reference: 14632
Logged by: Pietro Pugni
Email address: pietro.pugni@gmail.com
PostgreSQL version: 9.6.2
Operating system: Ubuntu 16.04.2 LTS
Description:

The following query:
SELECT '1912-02-29'::date + '10 years'::interval

returns:
'1922-02-28 00:00:00'

while the reverse operation:
SELECT '1922-02-28'::date - '10 years'::interval

returns:
'1912-02-28 00:00:00'

instead of '1912-02-29 00:00:00'.

​What part of this do you consider to be a bug - and what should it do
instead?

David J.​

I’ll try to reformulate better.

Adding 10 years to 1912-02-29 returns 1922-02-29, as expected.
I would like to apply the reverse operation. To do so, I subtract 10 years from 1922-02-29 but I obtain 1912-02-28, so the math is actually wrong.

The “logical” bug can be seen also by adding and subtracting the same quantity:

SELECT '1912-02-29'::date - '10 years'::interval + '10 years'::interval

It returns '1912-02-28 00:00:00' instead of '1912-02-29 00:00:00'.

It’s an issue because there’s no other way to obtain the original date (reversing the add operation). The function age(timestamp, timestamp), for example, behave exactly as adding and subtracting the interval:
SELECT age('1922-02-28'::date, '1912-02-29'::date)
returns:
9 years 11 mons 28 days
which is inconsistent against the result returned by adding 10 years to 1912-02-29:
SELECT '1912-02-29'::date + '10 years'::interval
returns:
(a) '1922-02-28 00:00:00'
while
SELECT '1922-02-28'::date - '9 years 11 mons 28 days'::interval
returns:
(b) '1912-02-29 00:00:00'
Please, note the difference of 1 day between (a) and (b).

Also:
SELECT age('1922-02-28'::date, '1912-02-29'::date) = '10 years'::interval
returns false, while:
SELECT ('1922-02-29'::date + '10 years'::interval) = '1922-02-28'::date
returns true.

The inconsistency (or bug) resides in the non unique meaning of the interval handling with respect of summing and subtracting the same quantity.

Again, the issue can be seen this way: adding the interval returned by SELECT age('1922-02-28'::date, '1912-02-29'::date) to the original date. So:
SELECT '1912-02-29'::date + '9 years 11 mons 28 days'::interval
returns:
(c) 1922-02-26 00:00:00
Here we loose 2 days even if we add the same interval used in (b)!

The bug basically consists of the vague meaning of “years” applied to leap years. It should be revised in order to be consistent and correct.

Kind regards,
Pietro Pugni

On 4/26/2017 1:30 PM, Pietro Pugni wrote:

Adding 10 years to 1912-02-29 returns 1922-02-29, as expected.
I would like to apply the *reverse* operation. To do so, I subtract 10
years from 1922-02-29 but I obtain 1912-02-28, so *the math is
actually wrong*.

assuming 1922 was a leap year, 1912 is NOT a leap year, so therefore
there is no 1912-02-29, that is an invalid date.

--
john r pierce, recycling bits in santa cruz

On Wed, Apr 26, 2017 at 1:53 PM, John R Pierce <pierce@hogranch.com> wrote:

On 4/26/2017 1:30 PM, Pietro Pugni wrote:

Adding 10 years to 1912-02-29 returns 1922-02-29, as expected.
I would like to apply the *reverse* operation. To do so, I subtract 10
years from 1922-02-29 but I obtain 1912-02-28, so *the math is actually
wrong*.

assuming 1922 was a leap year, 1912 is NOT a leap year, so therefore there
is no 1912-02-29, that is an invalid date.

​PostgreSQL think 1912 is the leap year, 1922 is not...

Dave
​

On Wed, Apr 26, 2017 at 8:23 PM, <pietro.pugni@gmail.com> wrote:

The following bug has been logged on the website:

Bug reference: 14632
Logged by: Pietro Pugni
Email address: pietro.pugni@gmail.com
PostgreSQL version: 9.6.2
Operating system: Ubuntu 16.04.2 LTS
Description:

The following query:
SELECT '1912-02-29'::date + '10 years'::interval

returns:
'1922-02-28 00:00:00'

while the reverse operation:
SELECT '1922-02-28'::date - '10 years'::interval

returns:
'1912-02-28 00:00:00'

instead of '1912-02-29 00:00:00'.

And if you change that, then this happens:

SELECT date '1912-02-28' + interval '10 years' - interval '10 years';
?column?
---------------------
1912-02-29 00:00:00
(1 row)

which is obviously not correct either.

The problem is that after the subtraction the date '1912-02-28' doesn't
know it's supposed to be the last date of the month instead of the 28th,
specifically. And you can't really fix that without breaking pg_upgrade
and probably hundreds of applications using the binary format for dates.

You'll just have to take this into account when working on your application.

.m

On 4/26/2017 1:53 PM, John R Pierce wrote:

On 4/26/2017 1:30 PM, Pietro Pugni wrote:

Adding 10 years to 1912-02-29 returns 1922-02-29, as expected.
I would like to apply the *reverse* operation. To do so, I subtract
10 years from 1922-02-29 but I obtain 1912-02-28, so *the math is
actually wrong*.

assuming 1922 was a leap year, 1912 is NOT a leap year, so therefore
there is no 1912-02-29, that is an invalid date.

ok, I got that backwards. 1912 is the leap year.

date arithmetic is not guaranteed to be associative or commutative due
to the irregular units involved.

pierce=# SELECT date '1912-02-29';
date
------------
1912-02-29
(1 row)

pierce=# SELECT date '1922-02-29';
ERROR: date/time field value out of range: "1922-02-29"
LINE 1: SELECT date '1922-02-29';
^
pierce=# SELECT date '1912-02-29' - interval '10 years';
?column?
---------------------
1902-02-28 00:00:00
(1 row)

pierce=# SELECT date '1912-02-29' + interval '10 years';
?column?
---------------------
1922-02-28 00:00:00
(1 row)

as an even more extreme case...

pierce=# SELECT date '2017-04-30' - interval '2 months';
?column?
---------------------
2017-02-28 00:00:00
(1 row)

--
john r pierce, recycling bits in santa cruz

On Wed, Apr 26, 2017 at 11:06 PM, I wrote:

On Wed, Apr 26, 2017 at 8:23 PM, <pietro.pugni@gmail.com> wrote:
The problem is that after the subtraction the date '1912-02-28' doesn't
know it's supposed to be the last date of the month instead of the 28th,
specifically.

Sorry, I meant "addition" and the date '1922-02-28' here.

.m

On Wed, Apr 26, 2017 at 1:30 PM, Pietro Pugni <pietro.pugni@gmail.com>
wrote:

I’ll try to reformulate better.

Adding 10 years to 1912-02-29 returns 1922-02-29, as expected.
I would like to apply the *reverse* operation. To do so, I subtract 10
years from 1922-02-29 but I obtain 1912-02-28, so *the math is actually
wrong*.

​In theory we could simply die trying but given few complaints this
behavior doesn't seem to bother many people if not actually please them
because at least they can get a correct result even if it is munged a bit.
Date interval math is fraught with problems.; you can bypass most of them
by manipulating days instead.

The “logical” bug can be seen also by adding and subtracting the same

quantity:

SELECT '1912-02-29'::date - '10 years'::interval + '10 years'::interval

It returns '1912-02-28 00:00:00' instead of '1912-02-29 00:00:00'.

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.​

It’s an issue because there’s no other way to obtain the original date

(reversing the add operation).

You are correct. Given the presence of leap years what you describe is a
mathematical impossibility - not a bug.

Also:

SELECT age('1922-02-28'::date, '1912-02-29'::date) = '10 years'::interval
returns false, while:
SELECT ('1922-02-29'::date + '10 years'::interval) = '1922-02-28'::date
returns true.

The inconsistency (or bug) resides in the non unique meaning of the
interval handling with respect of summing and subtracting the same
quantity.

​It resides in the fact we apply date shifting after applying the interval
in order to come up with a valid date. That such date shifting negates the
commutative property is an unfortunate byproduct. To my knowledge there is
no promise nor requirement for date arithmetic to be commutative. Or, more
precisely, if you wish to use the commutative property here you must
operate using days.

Again, the issue can be seen this way: adding the interval returned by
​​
SELECT age('1922-02-28'::date, '1912-02-29'::date) to the original date.
So:
​​
SELECT '1912-02-29'::date + '9 years 11 mons 28 days'::interval
returns:
(c) 1922-02-26 00:00:00
Here we loose *2 days *even if we add the same interval used in (b)!

​Operate in days and you can do this just fine - but note you cannot just
convert the interval to a number of days.

​SELECT '1922-02-28'::date - '1912-02-29'::date ; 3652
SELECT '1922-02-28'::date - 3652 ; 1912-02-29

This particular example leads me to suspect that some improvement in this
area might be possible...

The bug basically consists of the vague meaning of “years” applied to leap

years. It should be revised in order to be consistent and correct.

How?

Pietro Pugni <pietro.pugni@gmail.com> writes:

Adding 10 years to 1912-02-29 returns 1922-02-29, as expected.

No, it doesn't, and it would be wrong to do so because there is no
such date; 1922 wasn't a leap year.

There are basically three things the addition operator could do here:
throw an error, return 1922-02-28, or return 1922-03-01. The first
choice seems rather unhelpful. The second choice is more in keeping
with what we do for some other similar cases, such as

regression=# select '2017-01-31'::date + '1 month'::interval;
?column?
---------------------
2017-02-28 00:00:00
(1 row)

regression=# select '2017-01-31'::date + '3 months'::interval;
?column?
---------------------
2017-04-30 00:00:00
(1 row)

Basically the thought is that the last day of the month is more likely
to be the answer the user wants for such cases than the first day of the
next month. If you want the less-fuzzy semantics of, say, "plus 30 days",
you can have that too but you need to say it that way.

The really short answer is that civil calendars were not invented by
mathematicians and expecting them to obey mathematical laws is doomed
to be an exercise in frustration.

The bug basically consists of the vague meaning of “years” applied to
leap years. It should be revised in order to be consistent and correct.

As remarked somewhere in our documentation, you'd need to take that up
with the Pope, not with us database hackers. We didn't invent the
calendar rules.

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

On Wed, Apr 26, 2017 at 2:06 PM, Marko Tiikkaja <marko@joh.to> wrote:

The problem is that after the subtraction the date '1912-02-28' doesn't
know it's supposed to be the last date of the month instead of the 28th,
specifically.

Quite true. It would be nice to have better support for "Month End" (and
month start too but that's easier since its always "1") specification but
that isn't what we've got.

David J.​

Pietro Pugni <pietro.pugni@gmail.com> writes:

Adding 10 years to 1912-02-29 returns 1922-02-29, as expected.

No, it doesn't, and it would be wrong to do so because there is no
such date; 1922 wasn't a leap year.

I messed up 28 with 29 in that statement, sorry. Look at the queries instead, they are correct.
I’ll try to shed some light on the inconsistency of age() versus intervals used in conjunction with leap years.

The whole point (for leap years) is:
- inconsistency (adding and subtracting the same quantity to a date should provide the date itself, but it doesn't);
- wrong results (we loose days)
- ambiguity (we know that the meaning of an interval is relative to the referred date, but we have two different meanings if we add an interval to a date and obtain x and then subtract the same interval and obtain y=x-1)

The day loss can be easily seen by recursing the addition of the result returned by age:
SELECT age(age('1922-02-28'::date, '1912-02-29'::date) + '1912-02-29'::date, '1912-02-29'::date) + '1912-02-29'::date
?column?
---------------------
1922-02-24 00:00:00

This is totally wrong from a semantic point of view and it should return '1922-02-28 00:00:00'. Instead we get a total loss of 4 days.

As reported in my previous mail, adding 10 years to a leap date returns the expected value:
SELECT '1912-02-29'::date + '10 years'::interval
?column?
---------------------
1922-02-28 00:00:00

while subtracting 10 years from the returned date has a totally different meaning for Postgres and misses 1 day:
SELECT '1922-02-28'::date - '10 years'::interval
?column?
---------------------
1902-02-28 00:00:00

This is inconsistent (and wrong) against the result provided by this query:
select age('1922-02-28'::date, '1912-02-29'::date)
age
-------------------------
9 years 11 mons 28 days

I expect it to return ’10 years’ instead. It’s inconsistent also with this query:
SELECT '1912-02-29'::date + age('1922-02-28'::date, '1912-02-29'::date)
?column?
---------------------
1922-02-26 00:00:00

because it returns 1922-02-26 instead of 1922-02-28! age() returns the correct value if we look at the docs but it’s wrong from a logical point of view if we consider it for what it should be: a function returning age between dates and not the number of days.

The only workaround I see is subtracting 1 day from the “born” date:
SELECT age('1922-02-28'::date, '1912-02-29'::date - '1 day'::interval)
age
----------
10 years

but I’ll apply this caveat only to leap years, so I’ll need a function or a case statement.

The bug basically consists of the vague meaning of “years” applied to
leap years. It should be revised in order to be consistent and correct.

As remarked somewhere in our documentation, you'd need to take that up
with the Pope, not with us database hackers. We didn't invent the
calendar rules.

Postgres does a great job with dates and this is the first bug I find, but it’s very frustrating because it leads to errors, especially when dealing with a lot of records. The docs aren’t exhaustive on this topic IMHO.

Kind regards
Pietro Pugni

​In theory we could simply die trying but given few complaints this behavior doesn't seem to bother many people if not actually please them because at least they can get a correct result even if it is munged a bit. Date interval math is fraught with problems.; you can bypass most of them by manipulating days instead.

Many people doesn’t observe data and thus don’t see errors. I was simply calculating birthdays from birth dates and found some weird results that turned out to be associated to leap years.

The “logical” bug can be seen also by adding and subtracting the same quantity:

SELECT '1912-02-29'::date - '10 years'::interval + '10 years'::interval

It returns '1912-02-28 00:00:00' instead of '1912-02-29 00:00:00'.

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.

It’s an issue because there’s no other way to obtain the original date (reversing the add operation).

You are correct. Given the presence of leap years what you describe is a mathematical impossibility - not a bug.

It should be advised in the official docs (I can’t find any reference for this topic).

Also:
SELECT age('1922-02-28'::date, '1912-02-29'::date) = '10 years'::interval
returns false, while:
SELECT ('1922-02-29'::date + '10 years'::interval) = '1922-02-28'::date
returns true.

The inconsistency (or bug) resides in the non unique meaning of the interval handling with respect of summing and subtracting the same quantity.

​It resides in the fact we apply date shifting after applying the interval in order to come up with a valid date. That such date shifting negates the commutative property is an unfortunate byproduct. To my knowledge there is no promise nor requirement for date arithmetic to be commutative. Or, more precisely, if you wish to use the commutative property here you must operate using days.

Again, it should be written in some part of the docs that intervals lead to different results if summed/subtracted to/from leap dates.

Again, the issue can be seen this way: adding the interval returned by ​​SELECT age('1922-02-28'::date, '1912-02-29'::date) to the original date. So:
​​SELECT '1912-02-29'::date + '9 years 11 mons 28 days'::interval
returns:
(c) 1922-02-26 00:00:00
Here we loose 2 days even if we add the same interval used in (b)!

​Operate in days and you can do this just fine - but note you cannot just convert the interval to a number of days.

​SELECT '1922-02-28'::date - '1912-02-29'::date ; 3652
SELECT '1922-02-28'::date - 3652 ; 1912-02-29

This particular example leads me to suspect that some improvement in this area might be possible...

The bug basically consists of the vague meaning of “years” applied to leap years. It should be revised in order to be consistent and correct.

How?

Probably considering leap dates as singularities and threat them by subtracting 1 day.
For example, a possible workaround to properly calculate the interval between 1922-02-28 and 1922-02-29 is the following:

SELECT age('1922-02-28'::date, '1912-02-29'::date - '1 day'::interval)
age
----------
10 years

King regards,
Pietro Pugni

On Wed, Apr 26, 2017 at 3:44 PM, Pietro Pugni <pietro.pugni@gmail.com>
wrote:

This is inconsistent (and wrong) against the result provided by this query:
select age('1922-02-28'::date, '1912-02-29'::date)
age
-------------------------
9 years 11 mons 28 days

I expect it to return ’10 years’ instead.

I don't know if you intended to supersize your response but it ended up
that way in my mail client.

I'll agree that the age function has enough data available to it to return
10 years in this case.

28 days, target month is February in a non-leap-year (1922), convert to 1
month. 11 months + 1 month = 12 months = 1 year. 9 years + 1 year = 10
years.

But the provided answer is correct as well...

David J.

On Thu, Apr 27, 2017 at 1:01 AM, Pietro Pugni <pietro.pugni@gmail.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.

.m

On Wed, Apr 26, 2017 at 3:44 PM, Pietro Pugni <pietro.pugni@gmail.com <mailto:pietro.pugni@gmail.com>> wrote:
I don't know if you intended to supersize your response but it ended up that way in my mail client.

It was my client (Mail on OS X) that messed up font size and just saw it from the web browser..

I'll agree that the age function has enough data available to it to return 10 years in this case.

28 days, target month is February in a non-leap-year (1922), convert to 1 month. 11 months + 1 month = 12 months = 1 year. 9 years + 1 year = 10 years.

But the provided answer is correct as well...

This is the sort of ambiguity/inconsistency I was referring to. Intervals need a context in order to have a meaning but they never gave me issues except from leap years. So, I think it should be solved by “simply” threating leap years in a different way. Intervals are used to approach a human meaning of time measure, what we call “age”. Leap years should represent a specific exception IMHO.

Kind regards,
Pietro Pugni

On Wed, Apr 26, 2017 at 4:01 PM, Pietro Pugni <pietro.pugni@gmail.com>
wrote:

​​

It’s an issue because there’s no other way to obtain the original date

(reversing the add operation).

You are correct. Given the presence of leap years what you describe is a
mathematical impossibility - not a bug.

It should be advised in the official docs (I can’t find any reference for
this topic).

There are 365 days in a normal year and 366 days in a leap year. There
does not exist a function that can completely map from the normal domain to
the leap year domain (I'm stretching my math limits here, forgive or
correct minor inaccuracies). The 28th day of a non-leap year must either
be the 28th day of a leap year or the 29th day of a leap year, it cannot be
both (by definition of a function). Since the definition of "+/- 1 year"
is to simply increment/decrement the year by 1 we have chosen the first
outcome. There is a map the other direction though, because the 29th can
and is mapped to the 28th.

I haven't and am not presently slogging through docs to further support or
refute how well this is documented. If you have suggestions, or better, a
patch, they are welcome.

How?

Probably considering leap dates as singularities and threat them by
subtracting 1 day.
For example, a possible workaround to properly calculate the interval
between 1922-02-28 and 1922-02-29 is the following:

SELECT age('1922-02-28'::date, '1912-02-29'::date - '1 day'::interval)
age
----------
10 years

At first glance I would consider this a worthwhile path to explore; and it
meshes well with the comment I made above (before reading this) about the
functions are either ignoring or mapping the 29th to the 28th. Subtracting
1 day to leap-year dates effective does both. I'm sure there are other
interactions involved here but if for someone willing to do the work this
approach seems to have some merit.

David J.

On Wed, Apr 26, 2017 at 4:18 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Wed, Apr 26, 2017 at 4:01 PM, Pietro Pugni <pietro.pugni@gmail.com>
wrote:

How?

Probably considering leap dates as singularities and threat them by
subtracting 1 day.
For example, a possible workaround to properly calculate the interval
between 1922-02-28 and 1922-02-29 is the following:

SELECT age('1922-02-28'::date, '1912-02-29'::date - '1 day'::interval)
age
----------
10 years

At first glance I would consider this a worthwhile path to explore; and it
meshes well with the comment I made above (before reading this) about the
functions are either ignoring or mapping the 29th to the 28th. Subtracting
1 day to leap-year dates effective does both. I'm sure there are other
interactions involved here but if for someone willing to do the work this
approach seems to have some merit.

​And given that simple of a definition writing your own age function that
checks for the 29th and makes the adjustment would be trivial...so if
that's the extent I'm not sure PostgreSQL would want to change the
definition of the included age function and risk backward compatibility
issues.

David J.
​

On Thu, Apr 27, 2017 at 1:01 AM, Pietro Pugni <pietro.pugni@gmail.com <mailto:pietro.pugni@gmail.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, if we calculate the previous query on all the dates of February:

select age('2017-01-31'::date, '1912-02-29'::date) + '1912-02-29'::date;
select age('2017-02-01'::date, '1912-02-29'::date) + '1912-02-29'::date;
select age('2017-02-02'::date, '1912-02-29'::date) + '1912-02-29'::date;
select age('2017-02-03'::date, '1912-02-29'::date) + '1912-02-29'::date;
...
select age('2017-02-28'::date, '1912-02-29'::date) + '1912-02-29'::date;
select age('2017-03-01'::date, '1912-02-29'::date) + '1912-02-29'::date;

they return, respectively:
2017-01-31 00:00:00
2017-01-30 00:00:00
2017-01-31 00:00:00
2017-02-01 00:00:00
...
2017-02-26 00:00:00
2017-03-01 00:00:00

while the expected results are:
2017-01-31 00:00:00
2017-02-01 00:00:00
2017-02-02 00:00:00
2017-02-03 00:00:00
...
2017-02-28 00:00:00
2017-03-01 00:00:00

I understand why this happens (this has been discussed previously) but clearly there’s something wrong..

Kind regards
Pietro Pugni

"Pietro" == Pietro Pugni <pietro.pugni@gmail.com> writes:

Pietro> The whole point (for leap years) is:

Pietro> - inconsistency (adding and subtracting the same quantity to a
Pietro> date should provide the date itself, but it doesn't);

It should be obvious that this is mathematically impossible; a function
from a larger set (the 366 days of a leap year) to a smaller one (the
365 days of a common year) cannot have an inverse.

--
Andrew (irc:RhodiumToad)

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs