On 9/12/19 4:50 AM, Francisco Olarte wrote:
Adrian:
On Wed, Sep 11, 2019 at 11:19 PM Adrian Klaver
<adrian.klaver@xxxxxxxxxxx> 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@xxxxxxxxxxx
