Re: Epoch from age is incorrect

Zhihong Zhang <zhihong@gmail.com> writes:
> The epoch from following statement is incorrect,
> test_db=# select extract(epoch from age(TIMESTAMP '2013-02-18 06:15:15'));
>  date_part
> -----------
>  272396685
> (1 row)

AFAICS it's following the documented definition of epoch for intervals.

regression=# begin;
BEGIN
regression=*# select extract(epoch from age(TIMESTAMP '2013-02-18 06:15:15'));
     extract
------------------
 272223885.000000
(1 row)

regression=*# select age(TIMESTAMP '2013-02-18 06:15:15');
               age
---------------------------------
 8 years 7 mons 20 days 17:44:45
(1 row)

regression=*# select ((((8 * 365 + 7 * 30 + 20) * 24 + 17) * 60) + 44) * 60 + 45;
 ?column?
-----------
 272223885
(1 row)


age() is useful for some purposes, but this isn't one of them.
I'd recommend considering it as a human-readable approximation
rather than something to do further arithmetic with.  You'd get
more nearly the answer you probably want with

regression=*# select extract(epoch from localtimestamp - TIMESTAMP '2013-02-18 06:15:15');
     extract
------------------
 272806988.613568
(1 row)

or even more to the point,

regression=*# select extract(epoch from now() - TIMESTAMPTZ '2013-02-18 06:15:15');
     extract
------------------
 272803388.613568
(1 row)

            regards, tom lane