Re: extract('epoch' from age()) returning wrong number of seconds - Mailing list pgsql-bugs

From Tom Lane
Subject Re: extract('epoch' from age()) returning wrong number of seconds
Date
Msg-id 2110.1406743223@sss.pgh.pa.us
Whole thread Raw
In response to extract('epoch' from age()) returning wrong number of seconds  (Peter Nelson <peter.nelson@code42.com>)
List pgsql-bugs
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

pgsql-bugs by date:

Previous
From: Bruce Momjian
Date:
Subject: Re: BUG #10823: Better REINDEX syntax.
Next
From: vipivoxa@gmail.com
Date:
Subject: BUG #11093: login with cyrilic input language make pgAdmin to blow out instead of the wrong password message.