From: | Zhihong Zhang <zhihong(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-bugs <pgsql-bugs(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Epoch from age is incorrect |
Date: | 2021-10-12 00:48:54 |
Message-ID: | 9CA7E8DC-0364-46FD-B6B1-45289B3A0448@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Mark Dilger | 2021-10-12 02:22:14 | Re: BUG #17212: pg_amcheck fails on checking temporary relations |
Previous Message | Peter Geoghegan | 2021-10-12 00:37:51 | Re: BUG #17212: pg_amcheck fails on checking temporary relations |