Re: How to reformat output of "age()" function - Mailing list pgsql-general

Adrian:

On Wed, Sep 11, 2019 at 11:19 PM Adrian Klaver
<adrian.klaver@aklaver.com> wrote:
> On 9/11/19 9:34 AM, Francisco Olarte wrote:
...
> > If you want to know the elapsed minutes between two timestamps, it
> > might be better to do it directly, extract the epoch from both (
> > seconds ), substract, divide by 60 truncating/rounding if you need to.
....
> > test=# select extract(epoch from age('2019.11.20
> > 20:00:00'::timestamptz, '2019.06.10 10:00:00'::timestamptz)) as
> > interval_seconds;
> >   interval_seconds
> > ------------------
> >           13860000
> > (1 row)
> >
> > test=# select extract(epoch from '2019.11.20 20:00:00'::timestamptz) -
> > extract(epoch from '2019.06.10 10:00:00'::timestamptz) as
> > elapsed_seconds;
> >   elapsed_seconds
> > -----------------
> >          14122800
> > (1 row)
>

> Epoch is not the issue, age() is. Leave age() out of it:

Well, I did not point to age as the problem assuming it was clear in
the examples I gave. Two usages of epoch, one with other without age.

> set timezone = 'Europe/Madrid';
>
> test=# select extract(epoch from '2019.11.20 20:00:00'::timestamptz) -
> test-# extract(epoch from '2019.06.10 10:00:00'::timestamptz) as
> test-# elapsed_seconds;
>   elapsed_seconds
> -----------------
>          14122800
> test=# select extract(epoch from '2019.11.20 20:00:00'::timestamptz
> -'2019.06.10 10:00:00'::timestamptz) as
> elapsed_seconds;
>   elapsed_seconds
> -----------------
>          14122800
> (1 row)

Ah, I see your point now. The problem is age and substraction gives
different invervals.

>cdrs=# select '2019.11.20 20:00:00'::timestamptz -'2019.06.10 10:00:00'::timestamptz as diff_interval;
   diff_interval
-------------------
 163 days 11:00:00
(1 row)

cdrs=# select age('2019.11.20 20:00:00'::timestamptz, '2019.06.10
10:00:00'::timestamptz) as age_interval;
      age_interval
-------------------------
 5 mons 10 days 10:00:00
(1 row)

And I see it still translate it to normalized days.

I still prefer substracting epoch values, as the 163d/11h seems really
tricky to work with, and it took me a time to parse & diff your
queries against mines, they all looked the same. It seems like PG does
date substraction by substracting epochs and converting to a
normalized interval, I prefer to go to number of seconds directly when
working with "elapsed time"as otherwise I get confused. I mean
something like this ( using it just to show the differences ):

cdrs=# select (extract(epoch from '2019.11.20 20:00:00'::timestamptz)
- extract(epoch from '2019.06.10 10:00:00'::timestamptz)) * '1
second'::interval as pure_seconds_interval;
pure_seconds_interval
-----------------------
 3923:00:00
(1 row)

A third representation! which gives the same result for epoch, but I'm
not sure it does for arithmetic....( tested it, it does not )

I thought substraction would give me that, clearly it does not ( both
give the same when using epoch, as lacking tz info it has to assume
something, and it seems to assume no dst changes ).

timestamp / interval arithmetic is really a hairy thing. ( As shown
below, start point carefully taken to avoid crossing dsts )


cdrs=# select x, '2019.11.20 20:00:00'::timestamptz + x::interval as
sum from ( values ('5 mons 10 days 10:00:00'),('163 days
11:00:00'),('3923:00:00')) as v(x);
            x            |          sum
-------------------------+------------------------
 5 mons 10 days 10:00:00 | 2020-05-01 06:00:00+02
 163 days 11:00:00       | 2020-05-02 07:00:00+02
 3923:00:00              | 2020-05-02 08:00:00+02
(3 rows)


Francisco Olarte.