Re: Inconsistent results in timestamp/interval comparison

On Mon, 4 Mar 2024 at 13:46, Francisco Olarte <folarte(at)peoplecall(dot)com>
wrote:

> On Mon, 4 Mar 2024 at 13:10, <albrecht(dot)dress(at)posteo(dot)de> 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)

--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.