Thread: How to reformat output of "age()" function

How can I change the default output of the "age" function to be, for example, in minutes?

E.g.

dvdb=> select age('2019-09-11 09:00:00','2019-09-09 11:00:00');
      age      
----------------
 1 day 22:00:00
(1 row)

I want the equivalent of that time delta in minutes.

David:

On Wed, Sep 11, 2019 at 5:39 PM David Gauthier <davegauthierpg@gmail.com> wrote:
> How can I change the default output of the "age" function to be, for example, in minutes?
> E.g.
> dvdb=> select age('2019-09-11 09:00:00','2019-09-09 11:00:00');
>       age
> ----------------
>  1 day 22:00:00
> (1 row)
> I want the equivalent of that time delta in minutes.

Some answers have already been posted, but also consider what you are
doing. Intervals have three components, months, days, seconds for a
reason, ( ignoring leap seconds for now ) not all days have 24h (
daylight saving time changes ) and not all months have 28/29/30/31
days. IIRC interval normalization for epoch assumes all months have 30
days, all days have 24 hours.

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.

This is what happens in one case on my timezone ( Europe/Madrid ):


test=# select '2019.11.20 20:00:00'::timestamptz as end, '2019.06.10
10:00:00'::timestamptz as start;
          end           |         start
------------------------+------------------------
 2019-11-20 20:00:00+01 | 2019-06-10 10:00:00+02
(1 row)

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

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)

Francisco Olarte.

On 9/11/19 9:34 AM, Francisco Olarte wrote:
> David:
> 
> On Wed, Sep 11, 2019 at 5:39 PM David Gauthier <davegauthierpg@gmail.com> wrote:
>> How can I change the default output of the "age" function to be, for example, in minutes?
>> E.g.
>> dvdb=> select age('2019-09-11 09:00:00','2019-09-09 11:00:00');
>>        age
>> ----------------
>>   1 day 22:00:00
>> (1 row)
>> I want the equivalent of that time delta in minutes.
> 
> Some answers have already been posted, but also consider what you are
> doing. Intervals have three components, months, days, seconds for a
> reason, ( ignoring leap seconds for now ) not all days have 24h (
> daylight saving time changes ) and not all months have 28/29/30/31
> days. IIRC interval normalization for epoch assumes all months have 30
> days, all days have 24 hours.
> 
> 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.
> 
> This is what happens in one case on my timezone ( Europe/Madrid ):
> 
> 
> test=# select '2019.11.20 20:00:00'::timestamptz as end, '2019.06.10
> 10:00:00'::timestamptz as start;
>            end           |         start
> ------------------------+------------------------
>   2019-11-20 20:00:00+01 | 2019-06-10 10:00:00+02
> (1 row)
> 
> test=# select age('2019.11.20 20:00:00'::timestamptz, '2019.06.10
> 10:00:00'::timestamptz) as interval;
>          interval
> -------------------------
>   5 mons 10 days 10:00:00
> (1 row)
> 
> 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:

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)


> 
> Francisco Olarte.
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

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.

OOps, I got it bad:

On Thu, Sep 12, 2019 at 1:50 PM Francisco Olarte <folarte@peoplecall.com> wrote:

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

It was chosen to FORCE, not AVOID, crossing dst.

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

cdrs=# select x, '2019.11.20 20:00:00'::timestamptz + x::interval as
sum from ( values ('0'),('5 mons 10 days 10:00:00'),('163 days
11:00:00'),('3923:00:00')) as v(x);
            x            |          sum
-------------------------+------------------------
 0                       | 2019-11-20 20:00:00+01
 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
(4 rows)

Subject is just complex enough I should avoid making this typos!

Apologies, my fault, bad proof reading, hungry....

Francisco Olarte.

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

Adrian:

On Thu, Sep 12, 2019 at 4:23 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:

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

I know how to set the timezone and operate with it, and Iknow for the
purpose of calculating elapsed seconds ( minutes / hours ) both
approaches are the same. What I'm saying is you must take care on how
to define and calculate your intervals if you are going to add them to
TS directly, as an 86400 seconds one has the same epoch than a 1day
one, but they represent different things when being added to a tstz,
and I know that the difference depends on the current timezone.

I do a lot of these, working with phone calls to extract ring / talk
time from setup/connect/disconnect time, and I do not hit problems
because I never do interval arithmetic on elapsed times , its not a
problem if you are aware that there are "infinite" ways to represent
an epoch in an interval .

Francisco Olarte.