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
