Re: [BUGS] BUG #14632: Plus and minus operators inconsistency withleap years and year intervals. - Mailing list pgsql-bugs

From David G. Johnston
Subject Re: [BUGS] BUG #14632: Plus and minus operators inconsistency withleap years and year intervals.
Date
Msg-id CAKFQuwarKmdOQ09k5vM2imGHBVmeriPa8i4+Yqz7gw8u8PCOOQ@mail.gmail.com
Whole thread Raw
In response to Re: [BUGS] BUG #14632: Plus and minus operators inconsistency withleap years and year intervals.  ("David G. Johnston" <david.g.johnston@gmail.com>)
List pgsql-bugs
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.

pgsql-bugs by date:

Previous
From: "David G. Johnston"
Date:
Subject: Re: [BUGS] BUG #14632: Plus and minus operators inconsistency withleap years and year intervals.
Next
From: Pietro Pugni
Date:
Subject: Re: [BUGS] BUG #14632: Plus and minus operators inconsistency with leap years and year intervals.