On 9/11/19 9:34 AM, Francisco Olarte wrote:
David:
On Wed, Sep 11, 2019 at 5:39 PM David Gauthier <davegauthierpg@xxxxxxxxx> 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@xxxxxxxxxxx
