extract('epoch' from age()) returning wrong number of seconds
The forth column in this query is returning the wrong value.
The last two columns in this query should return the same value, 5270400 (the number of seconds in 61 days). Using simple subtraction works fine, but using the age() function it returns 5184000 seconds (60 days). This same behavior can be found using other start/end dates and interval lengths, sometimes age() returns too many seconds, sometimes too few. I did not get any failures below 60 days, but I did not do an exhaustive test of all interval values and a range of start/end dates.
select version();
version
----------------------------------------------------------------------------------------------
PostgreSQL 9.2.6 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.7.2-5) 4.7.2, 64-bit
(1 row)
Time: 41.898 ms
select extract('epoch' from ('2013-12-31'::date) - ('2013-12-31'::date - '5184000 seconds'::interval)) as right_answer_subtraction_60_days
,extract('epoch' from age (('2013-12-31'::date) , ('2013-12-31'::date - '5184000 seconds'::interval))) as right_answer_age_60_days
,extract('epoch' from ('2013-12-31'::date) - ('2013-12-31'::date - '5270400 seconds'::interval)) as right_answer_subtraction_61_days
,extract('epoch' from age (('2013-12-31'::date) , ('2013-12-31'::date - '5270400 seconds'::interval))) as wrong_answer_age_61_days;
right_answer_subtraction_60_days | right_answer_age_60_days | right_answer_subtraction_61_days | wrong_answer_age_61_days
----------------------------------+--------------------------+----------------------------------+--------------------------
5184000 | 5184000 | 5270400 | 5184000
(1 row)
Peter Nelson <peter.nelson@code42.com> writes:
The forth column in this query is returning the wrong value.
No it isn't.
The underlying result of the age() function is
# select age (('2013-12-31'::date) , ('2013-12-31'::date - '5270400 seconds'::interval));
age
--------
2 mons
(1 row)
which is the desired result in this case: it's two months from 2013-10-31
to 2013-12-31. Then extract(epoch from interval) arbitrarily assumes that
a "month" equals 2592000 seconds (30 days), which isn't the greatest
thing, but without any context there's no way for it to do much better.
For the result that you're after, timestamp subtraction is the way
to get it:
select (('2013-12-31'::date) - ('2013-12-31'::date - '5270400 seconds'::interval));
?column?
----------
61 days
(1 row)
Bottom line: age() is not meant to exactly reproduce timestamp
subtraction, and it's not a bug that it doesn't. It's *supposed* to give
you symbolic results like "2 months" rather than "61 days", or "1 year"
rather than "365 days" (or sometimes "366 days"). In the same vein,
it tries to abstract away from daylight-savings-time changes ...
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs