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

On 9/12/19 4:50 AM, Francisco Olarte wrote:
> 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

Because they are the same:

https://www.postgresql.org/docs/11/functions-datetime.html

"Subtraction of dates and timestamps can also be complex. One 
conceptually simple way to perform subtraction is to convert each value 
to a number of seconds using EXTRACT(EPOCH FROM ...), then subtract the 
results; this produces the number of seconds between the two values. 
This will adjust for the number of days in each month, timezone changes, 
and daylight saving time adjustments. Subtraction of date or timestamp 
values with the “-” operator returns the number of days (24-hours) and 
hours/minutes/seconds between the values, making the same adjustments. 
The age function returns years, months, days, and hours/minutes/seconds, 
performing field-by-field subtraction and then adjusting for negative 
field values. The following queries illustrate the differences in these 
approaches. The sample results were produced with timezone = 
'US/Eastern'; there is a daylight saving time change between the two 
dates used:

SELECT EXTRACT(EPOCH FROM timestamptz '2013-07-01 12:00:00') -
        EXTRACT(EPOCH FROM timestamptz '2013-03-01 12:00:00');
Result: 10537200
SELECT (EXTRACT(EPOCH FROM timestamptz '2013-07-01 12:00:00') -
         EXTRACT(EPOCH FROM timestamptz '2013-03-01 12:00:00'))
         / 60 / 60 / 24;
Result: 121.958333333333
SELECT timestamptz '2013-07-01 12:00:00' - timestamptz '2013-03-01 
12:00:00';
Result: 121 days 23:00:00
SELECT age(timestamptz '2013-07-01 12:00:00', timestamptz '2013-03-01 
12:00:00');
Result: 4 mons
"

> 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 ).

See doc information above. It uses the SET timezone. See below for more 
information:

https://www.postgresql.org/docs/11/datatype-datetime.html#DATATYPE-TIMEZONES

It does deal with DST changes. See the example above.

> 
> 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.
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com