Thread: [BUGS] BUG #14632: Plus and minus operators inconsistency with leap yearsand year intervals.

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




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


Il giorno 26 apr 2017, alle ore 20:29, David G. Johnston <david.g.johnston@gmail.com> ha scritto:

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

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> 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> 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 aPietro>  date should provide the date itself,
butit 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



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


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
 

Il giorno 27 apr 2017, alle ore 21:55, Pedro Gimeno <pgsql-004@personal.formauri.es> ha scritto:

I would like to better understand your expectations regarding your proposed fix.

1. What do you propose that this specific query should return?

SELECT '1912-02-28'::date + interval '10 years’;

1922-02-28 
(same result returned by Postgres)


2. What do you propose that this specific query should return?

SELECT (whatever your answer to question 1 is)::date - interval '10 years’;

1912-02-28
(same result returned by Postgres but collides with result returned at point 4.)

3. What do you propose that this specific query should return?

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

1922-02-28
(same result returned by Postgres)


4. What do you propose that this specific query should return?

SELECT (whatever your answer to question 3 is)::date - interval '10 years’;

1912-02-29
(different from result returned at point 2.)


5. What do you propose that this specific query should return?

SELECT '1912-03-01'::date + interval '10 years’;

1922-03-01
(same result returned by Postgres)


6. What do you propose that this specific query should return?

SELECT (whatever your answer to question 5 is)::date - interval '10 years’;

1912-03-01
(same result returned by Postgres)



I’m aware that 2. and 4. can’t return different result because they are the same queries, but in that cases the result correctness depends on the context.

Probably it should be worth it to implement a sort of age2(timestamp, timestamp) function that returns ages accounting for leap years, without changing the actual implementation of age(timestamp, timestamp).


Kind regards,
 Pietro Pugni


Il giorno 27 apr 2017, alle ore 14:31, Pantelis Theodosiou <ypercube@gmail.com> ha scritto:

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

Probably it should be worth it to implement a sort of age2(timestamp, timestamp) function that returns ages accounting for leap years, months, days and seconds, without changing the actual implementation of age(timestamp, timestamp).

Kind regards,
 Pietro Pugni
Probably it should be worth it to implement a sort of age2(timestamp, timestamp) function that returns ages accounting for leap years, months, days and seconds, without changing the actual implementation of age(timestamp, timestamp).

This results also may be of interest:

1)
select age('2007-04-01'::date, '1917-04-01'::date);
                age                
-----------------------------------
 89 years 11 mons 29 days 23:00:00

2)
select age('2007-04-01'::timestamp without time zone, '1917-04-01'::timestamp without time zone);
   age    
----------
 90 years


3)
select age('2007-04-01'::timestamp without time zone, '1917-04-01'::date);
   age    
----------
 90 years

4)
select age('2007-04-01'::date, '1917-04-01'::timestamp without time zone);
   age    
----------
 90 years


I don’t know if the result of query 1) is the desired output of the age(timestamp, timestamp) function.
Casting to timestamp and all combinations of date provides the same result as queries 2), 3) and 4):

5)
select age('2007-04-01'::timestamp, '1917-04-01'::timestamp);
   age    
----------
 90 years

6)
select age('2007-04-01'::date, '1917-04-01'::timestamp);
   age    
----------
 90 years

7)
select age('2007-04-01'::timestamp, '1917-04-01'::date);
   age    
----------
 90 years


Is there a reason for 1) being different in respect of the other queries?


Kind regards,
 Pietro Pugni

Pietro Pugni wrote, On 2017-04-28 10:28:

> I’m aware that 2. and 4. can’t return different result because they are the same queries, but in that cases the
resultcorrectness depends on the context.
 

You can use 10*interval '31556952 seconds' to add or subtract 10 years with consistent results. 31556952 seconds is the
averagelength of the year in the Gregorian calendar. To use this approach, you need to start using timestamp instead of
date,and avoid using timestamp with time zone on a time zone that uses daylight savings.
 

I don't think PostgreSQL needs to do anything about this issue.


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