Adrian:
On Wed, Sep 11, 2019 at 11:19 PM Adrian Klaver
<adrian.klaver@xxxxxxxxxxx> 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.
