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