Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rh78j-009fyH-BN for pgsql-general@arkaria.postgresql.org; Mon, 04 Mar 2024 12:09:54 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rh78h-007Hdk-VV for pgsql-general@arkaria.postgresql.org; Mon, 04 Mar 2024 12:09:52 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rh78h-007Hdc-HN for pgsql-general@lists.postgresql.org; Mon, 04 Mar 2024 12:09:52 +0000 Received: from mout02.posteo.de ([185.67.36.66]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rh78f-002iNR-EX for pgsql-general@lists.postgresql.org; Mon, 04 Mar 2024 12:09:51 +0000 Received: from submission (posteo.de [185.67.36.169]) by mout02.posteo.de (Postfix) with ESMTPS id D6E98240103 for ; Mon, 4 Mar 2024 13:09:47 +0100 (CET) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=posteo.de; s=2017; t=1709554187; bh=5n6xLTSOHBPqAZ3zQvLqVlpsD5CbsJEiQP3YgQCDcoE=; h=MIME-Version:Content-Type:Content-Transfer-Encoding:Date:From:To: Subject:Message-ID:From; b=RaEPjW34AwZtNkUmF0h9IdoZ8ru8jWNE7z4Rl5wGyser+vtydt31McY/Iqj16AcE/ UstNu6X3FLz6rcVs7oHV/RhaX4PL+AeYQXBN1H4+bXHhKaXvl2jdOriyZ2tQFV7Jwh Pq0jKLyMsBbbl2c3lNgNNdgMH4plF3ahTdw8s4LLltL5jHtSwIfZH0EIS2r8U87A7r Hs+AymmzUz/61LuoJsw2caZ0DX8Ok45jFDzXEneh4M68hPQRDFzKIc4Az3rxALXajg WuSq+DlfwJSeOO50Hwk6w07mlLg0LUZ7WcA853hEhw6+3p9W/fw+T0Bs5eKwhIRIkQ JprzC25n4EHiQ== Received: from customer (localhost [127.0.0.1]) by submission (posteo.de) with ESMTPSA id 4TpHZ3154Jz6tyX for ; Mon, 4 Mar 2024 13:09:47 +0100 (CET) MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: quoted-printable Date: Mon, 04 Mar 2024 12:09:47 +0000 From: albrecht.dress@posteo.de To: pgsql-general@lists.postgresql.org Subject: Inconsistent results in timestamp/interval comparison Message-ID: <839f7314609c96c5123549a28d493534@posteo.de> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Hi all, I run the =E2=80=9Cofficial=E2=80=9D deb package postgresql-16 v. 16.2-1.pg= dg120+2 on a=20 Debian Bookworm system, and observed a confusing behavior in a=20 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=20 23:39:17'), ('2022-03-21 17:49:02'); test=3D# select now(), t1, (now() - t1) >=3D '2 years'::interval, now() >= =3D=20 (t1 + '2 years'::interval) from testtab; now | t1 | ?column? |=20 ?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=20 produce the same results, as timestamp - timestamp =E2=86=92 interval timestamp + interval =E2=86=92 timestamp i.e. (now() - t1) >=3D '2 years'::interval # add t1 on both sides of the=20 comparison now() >=3D (t1 + '2 years'::interval) As only the second example is wrong for the 1st comparison method, this=20 might indicate some rounding and/or insufficient precision issue. Or did I miss something here? Thanks in advance, Albrecht.