From: Tom Lane <tgl@sss.pgh.pa.us>
To: Zhihong Zhang <zhihong@gmail.com>
Cc: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
Subject: Re: Epoch from age is incorrect
Date: Mon, 11 Oct 2021 17:59:31 -0400
Message-ID: <523248.1633989571@sss.pgh.pa.us> (raw)
In-Reply-To: <E756CE54-1946-4DC8-9F63-9BB54A07EF63@gmail.com>
References: <E756CE54-1946-4DC8-9F63-9BB54A07EF63@gmail.com>
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: tgl@sss.pgh.pa.us, zhihong@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: Epoch from age is incorrect
In-Reply-To: <523248.1633989571@sss.pgh.pa.us>
* 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