From: albrecht.dress@posteo.de
To: pgsql-general@lists.postgresql.org
Subject: Inconsistent results in timestamp/interval comparison
Date: Mon, 04 Mar 2024 12:09:47 +0000
Message-ID: <839f7314609c96c5123549a28d493534@posteo.de> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: albrecht.dress@posteo.de
Subject: Re: Inconsistent results in timestamp/interval comparison
In-Reply-To: <839f7314609c96c5123549a28d493534@posteo.de>
References: <839f7314609c96c5123549a28d493534@posteo.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox