Inconsistent results in timestamp/interval comparison

[albrecht . dress]

Hi all,

I run the “official” deb package postgresql-16 v. 16.2-1.pgdg120+2 on a 
Debian Bookworm system, and observed a confusing behavior in a 
calculation with time stamps and intervals.


To reproduce, consider the following trivial example:


create table testtab (t1 timestamp without time zone);
insert into testtab values ('2022-02-27 11:46:33'), ('2022-03-11 
23:39:17'), ('2022-03-21 17:49:02');
test=# select now(), t1, (now() - t1) >= '2 years'::interval, now() >= 
(t1 + '2 years'::interval) from testtab;
  now  | t1  | ?column? | 
?column?

---+-+--+--
 2024-03-04 12:59:39.796969+01 | 2022-02-27 11:46:33 | t| t
 2024-03-04 12:59:39.796969+01 | 2022-03-11 23:39:17 | t| f
 2024-03-04 12:59:39.796969+01 | 2022-03-21 17:49:02 | f| f
(3 Zeilen)


According to the documentation, Table 9.31, IMHO both comparisons should 
produce the same results, as


timestamp - timestamp → interval
timestamp + interval → timestamp

i.e.

(now() - t1) >= '2 years'::interval# add t1 on both sides of the 
comparison

now() >= (t1 + '2 years'::interval)

As only the second example is wrong for the 1st comparison method, this 
might indicate some rounding and/or insufficient precision issue.


Or did I miss something here?

Thanks in advance,
Albrecht.

Re: Inconsistent results in timestamp/interval comparison

[albrecht . dress]

Am 04.03.2024 13:45 schrieb Francisco Olarte:

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.


Ah, I see, thanks for the explanation.  I had the (apparently wrong) 
impression that Postgres _internally_ always uses numerical values (i.e. 
the equivalent of EXTRACT(EPOCH …)) for such calculations.  My bad…


However, a clarification in the docs might be helpful!


If you want to do point in time arithmetic, you will be better of by
extracting epoch from your timestamps and substracting that.


I can confirm that using the query

select now(), t1, extract(epoch from now() - t1) >= extract (epoch from 
'2 years'::interval), now() >= (t1 + '2 years'::interval) from testtab;


produces consistent results.

Thanks a lot for your help,
Albrecht.