The following bug has been logged online:
Bug reference: 3431
Logged by: Pelle Johansson
Email address: pelle@morth.org
PostgreSQL version: 8.2.3
Operating system: Linux 2.6
Description: age() gets the days wrong
Details:
This might be a known issue but i couldn't find it reported before...
The age() function seem to work by first counting months until less than a
month remains to to the second argument, then counting days left. This
doesn't give the correct result, as shown by this example:
# select column1, age(column1, '2006-11-02'), date '2006-11-02' +
age(column1, '2006-11-02') from (values ('2007-01-31'::date),
('2007-02-01')) as alias;
column1 | age | ?column?
------------+----------------+---------------------
2007-01-31 | 2 mons 29 days | 2007-01-31 00:00:00
2007-02-01 | 2 mons 29 days | 2007-01-31 00:00:00
(2 rows)
For the second row, the age should be '2 mons 30 days', which is what you
need to add to '2006-11-02' to get back to '2007-02-01'.
The - operator does not have this problem, so it can be used as a
workaround, but not in all situations.