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