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

Thanks a lot!

On Wed, Sep 11, 2019 at 12:34 PM Francisco Olarte <folarte(at)peoplecall(dot)com>
wrote:

> David:
>
> On Wed, Sep 11, 2019 at 5:39 PM David Gauthier <davegauthierpg(at)gmail(dot)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.
>