pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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.






view thread (5+ messages)  latest in thread

Message-ID: <839f7314609c96c5123549a28d493534@posteo.de>
Permalink:  ../839f7314609c96c5123549a28d493534@posteo.de/
Also on:    postgresql.org/message-id/839f7314609c96c5123549a28d493534@posteo.de

 · 

reply

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