Re: Epoch from age is incorrect

I thought age(time) was just a shorthand for (now() - time). Apparently they yield very different results.

I will stay away from age() for our use case. Thanks for the explanation.

Zhihong


> On Oct 11, 2021, at 5:59 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> 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