From: Zhihong Zhang <zhihong@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
Subject: Re: Epoch from age is incorrect
Date: Mon, 11 Oct 2021 20:48:54 -0400
Message-ID: <9CA7E8DC-0364-46FD-B6B1-45289B3A0448@gmail.com> (raw)
In-Reply-To: <523248.1633989571@sss.pgh.pa.us>
References: <E756CE54-1946-4DC8-9F63-9BB54A07EF63@gmail.com>
<523248.1633989571@sss.pgh.pa.us>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: zhihong@gmail.com, tgl@sss.pgh.pa.us, pgsql-bugs@lists.postgresql.org
Subject: Re: Epoch from age is incorrect
In-Reply-To: <9CA7E8DC-0364-46FD-B6B1-45289B3A0448@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox