On Mon, 4 Mar 2024 at 13:46, Francisco Olarte <fola...@peoplecall.com> wrote:
> On Mon, 4 Mar 2024 at 13:10, <albrecht.dr...@posteo.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.