Re: Epoch from age is incorrect

Zhihong Zhang <zhihong(at)gmail(dot)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