Re: [BUGS] BUG #14632: Plus and minus operators inconsistency withleap years and year intervals.

Поиск
Список
Период
Сортировка
От David G. Johnston
Тема Re: [BUGS] BUG #14632: Plus and minus operators inconsistency withleap years and year intervals.
Дата
Msg-id CAKFQuwYr=2eoP-CTLGrGzHzKgzotLG3zgEN4KDUE2Lo3Dyfe3A@mail.gmail.com
обсуждение исходный текст
Ответ на Re: [BUGS] BUG #14632: Plus and minus operators inconsistency with leap years and year intervals.  (Pietro Pugni <pietro.pugni@gmail.com>)
Ответы Re: [BUGS] BUG #14632: Plus and minus operators inconsistency withleap years and year intervals.  ("David G. Johnston" <david.g.johnston@gmail.com>)
Список pgsql-bugs
Дерево обсуждения
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.


В списке pgsql-bugs по дате отправления:

Предыдущее
От: Pietro Pugni
Дата:
Сообщение: Re: [BUGS] BUG #14632: Plus and minus operators inconsistency with leap years and year intervals.
Следующее
От: "David G. Johnston"
Дата:
Сообщение: Re: [BUGS] BUG #14632: Plus and minus operators inconsistency withleap years and year intervals.