Inconsistent results in timestamp/interval comparison

Albrecht Dreß <albrecht.dress@posteo.de>

From: albrecht.dress@posteo.de
To: pgsql-general@lists.postgresql.org
Date: 2024-03-04T12:09:47Z
Lists: pgsql-general
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:

<snip>
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)
</snip>

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.