On Mon, 4 Mar 2024 at 13:46, Francisco Olarte <folarte@xxxxxxxxxxxxxx> wrote:
On Mon, 4 Mar 2024 at 13:10, <albrecht.dress@xxxxxxxxx> wrote:
> According to the documentation, Table 9.31, IMHO both comparisons should
> produce the same results, as
> timestamp - timestamp → interval
> timestamp + interval → timestamp
Your problem may be due to interval comparison.
Intervals are composed of months, days and seconds, as not every month
has 30 days and not every day has 86400 seconds, so to compare them
you have to normalize them somehow, which can lead to bizarre results.
=> select '2 years'::interval > '1 year 362 days'::interval;
?column?
----------
f
(1 row)
=> select '2 years'::interval > '1 year 359 days'::interval;
?column?
----------
t
(1 row)
=> select '2 years'::interval > '1 year 360 days'::interval;
?column?
----------
f
(1 row)
=> select '2 years'::interval = '1 year 360 days'::interval;
?column?
----------
t
(1 row)
If you want to do point in time arithmetic, you will be better of by
extracting epoch from your timestamps and substracting that. Intervals
are more for calendar arithmetic on the type "set me a date two
months, three days and four hours from the last".
Francisco Olarte.
To elaborate, justify_interval(t) shows how the length of the interval ends up when there is no timestamp to base the end of the interval on:
=> with testtab(t1) as (
select cast(v as timestamp with time zone)
from (values ('2022-02-27 11:46:33'), ('2022-03-11 23:39:17'), ('2022-03-21 17:49:02')) x(v)
)
select now(), t1, now() - t1 "now()-t1", justify_interval(now() -t1)
from testtab;
now | t1 | now()-t1 | justify_interval
------------------------------+------------------------+-------------------------+---------------------------------------
2024-03-04 13:00:31.00386+00 | 2022-02-27 11:46:33+00 | 736 days 01:13:58.00386 | 2 years 16 days 01:13:58.00386
2024-03-04 13:00:31.00386+00 | 2022-03-11 23:39:17+00 | 723 days 13:21:14.00386 | 2 years 3 days 13:21:14.00386
2024-03-04 13:00:31.00386+00 | 2022-03-21 17:49:02+00 | 713 days 19:11:29.00386 | 1 year 11 mons 23 days 19:11:29.00386
(3 rows)
select cast(v as timestamp with time zone)
from (values ('2022-02-27 11:46:33'), ('2022-03-11 23:39:17'), ('2022-03-21 17:49:02')) x(v)
)
select now(), t1, now() - t1 "now()-t1", justify_interval(now() -t1)
from testtab;
now | t1 | now()-t1 | justify_interval
------------------------------+------------------------+-------------------------+---------------------------------------
2024-03-04 13:00:31.00386+00 | 2022-02-27 11:46:33+00 | 736 days 01:13:58.00386 | 2 years 16 days 01:13:58.00386
2024-03-04 13:00:31.00386+00 | 2022-03-11 23:39:17+00 | 723 days 13:21:14.00386 | 2 years 3 days 13:21:14.00386
2024-03-04 13:00:31.00386+00 | 2022-03-21 17:49:02+00 | 713 days 19:11:29.00386 | 1 year 11 mons 23 days 19:11:29.00386
(3 rows)
--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.
Cut the trees and you'll see there is no forest.
