pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedEpoch from age is incorrect
5+ messages / 4 participants
[nested] [flat]
* Epoch from age is incorrect
@ 2021-10-11 20:39 Zhihong Zhang <zhihong@gmail.com>
2021-10-11 21:52 ` Re: Epoch from age is incorrect David G. Johnston <david.g.johnston@gmail.com>
2021-10-11 21:59 ` Re: Epoch from age is incorrect Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 5+ messages in thread
From: Zhihong Zhang @ 2021-10-11 20:39 UTC (permalink / raw)
To: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
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)
The correct value should be
test_db=# select (extract(epoch from now()) - extract(epoch from TIMESTAMP '2013-02-18 06:15:15'));
?column?
------------------
272816228.311208
(1 row)
They are several days apart. Maybe leap years are not counted properly?
Postgresql version:
PostgreSQL 11.12 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat
Let me know if you need more information. Thanks!
Zhihong
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Epoch from age is incorrect
2021-10-11 20:39 Epoch from age is incorrect Zhihong Zhang <zhihong@gmail.com>
@ 2021-10-11 21:52 ` David G. Johnston <david.g.johnston@gmail.com>
2021-10-11 21:56 ` Re: Epoch from age is incorrect Bruce Momjian <bruce@momjian.us>
1 sibling, 1 reply; 5+ messages in thread
From: David G. Johnston @ 2021-10-11 21:52 UTC (permalink / raw)
To: Zhihong Zhang <zhihong@gmail.com>; +Cc: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
On Monday, October 11, 2021, Zhihong Zhang <zhihong@gmail.com> wrote:
>
> They are several days apart. Maybe leap years are not counted properly?
>
That is related. Intervals supply integer years, months, and days (for
example). Given an integer month (or year) value there is no way to
accurately compute the number of days involved (or any subfield of day)
since you lack knowledge of which months (years) they are (generally). So
extracting an epoch from an interval is an inherently unwise thing to do.
Having done it, comparing its result to a timestamp epoch computation is
meaningless. If you can do the later then just do it as it will provide
the expected answer while the interval epoch is inherently problematic.
David J.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Epoch from age is incorrect
2021-10-11 20:39 Epoch from age is incorrect Zhihong Zhang <zhihong@gmail.com>
2021-10-11 21:52 ` Re: Epoch from age is incorrect David G. Johnston <david.g.johnston@gmail.com>
@ 2021-10-11 21:56 ` Bruce Momjian <bruce@momjian.us>
0 siblings, 0 replies; 5+ messages in thread
From: Bruce Momjian @ 2021-10-11 21:56 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Zhihong Zhang <zhihong@gmail.com>; pgsql-bugs <pgsql-bugs@lists.postgresql.org>
On Mon, Oct 11, 2021 at 02:52:59PM -0700, David G. Johnston wrote:
> On Monday, October 11, 2021, Zhihong Zhang <zhihong@gmail.com> wrote:
>
>
> They are several days apart. Maybe leap years are not counted properly?
>
>
> That is related. Intervals supply integer years, months, and days (for
> example). Given an integer month (or year) value there is no way to accurately
> compute the number of days involved (or any subfield of day) since you lack
> knowledge of which months (years) they are (generally). So extracting an epoch
> from an interval is an inherently unwise thing to do. Having done it,
> comparing its result to a timestamp epoch computation is meaningless. If you
> can do the later then just do it as it will provide the expected answer while
> the interval epoch is inherently problematic.
Yes. Also, this blog has more details:
https://momjian.us/main/blogs/pgblog/2020.html#August_3_2020
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
If only the physical world exists, free will is an illusion.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Epoch from age is incorrect
2021-10-11 20:39 Epoch from age is incorrect Zhihong Zhang <zhihong@gmail.com>
@ 2021-10-11 21:59 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-10-12 00:48 ` Re: Epoch from age is incorrect Zhihong Zhang <zhihong@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: Tom Lane @ 2021-10-11 21:59 UTC (permalink / raw)
To: Zhihong Zhang <zhihong@gmail.com>; +Cc: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Epoch from age is incorrect
2021-10-11 20:39 Epoch from age is incorrect Zhihong Zhang <zhihong@gmail.com>
2021-10-11 21:59 ` Re: Epoch from age is incorrect Tom Lane <tgl@sss.pgh.pa.us>
@ 2021-10-12 00:48 ` Zhihong Zhang <zhihong@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Zhihong Zhang @ 2021-10-12 00:48 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2021-10-12 00:48 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-10-11 20:39 Epoch from age is incorrect Zhihong Zhang <zhihong@gmail.com>
2021-10-11 21:52 ` David G. Johnston <david.g.johnston@gmail.com>
2021-10-11 21:56 ` Bruce Momjian <bruce@momjian.us>
2021-10-11 21:59 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-10-12 00:48 ` Zhihong Zhang <zhihong@gmail.com>
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