Re: How to reformat output of "age()" function
От | Adrian Klaver |
---|---|
Тема | Re: How to reformat output of "age()" function |
Дата | |
Msg-id | abfebdbe-da87-7b21-1d56-32340c5c934a@aklaver.com обсуждение исходный текст |
Ответ на | Re: How to reformat output of "age()" function (Francisco Olarte <folarte@peoplecall.com>) |
Ответы |
Re: How to reformat output of "age()" function
(Francisco Olarte <folarte@peoplecall.com>)
|
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: