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 1rh81U-009kWm-Tf for pgsql-general@arkaria.postgresql.org; Mon, 04 Mar 2024 13:06:29 +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 1rh81T-007qVK-E1 for pgsql-general@arkaria.postgresql.org; Mon, 04 Mar 2024 13:06:27 +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 1rh81T-007qVC-0N for pgsql-general@lists.postgresql.org; Mon, 04 Mar 2024 13:06:27 +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 1rh81R-002ivf-1f for pgsql-general@lists.postgresql.org; Mon, 04 Mar 2024 13:06:26 +0000 Received: from submission (posteo.de [185.67.36.169]) by mout02.posteo.de (Postfix) with ESMTPS id D6A7E240101 for ; Mon, 4 Mar 2024 14:06:23 +0100 (CET) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=posteo.de; s=2017; t=1709557583; bh=bvSovzPGR/UvY10rS1ebPqfQM8sLEL8UTse8pE2t0jE=; h=MIME-Version:Content-Type:Content-Transfer-Encoding:Date:From:To: Cc:Subject:Message-ID:From; b=mxyUZmg2DHpZokBiNcrUhLw0Kua/9XMDlTuPhNN4ytcS+LMjAHDTVLtA8vcW7HDuM LWT3MhQg32R+4ZZXUBDg0eQraA7jZM5YsG57C2Cx8p4egOX+t9QCsCMi4l47Suzscs 9SyqQRx+zcAPR4qvxuG4nyxW3qc0TeiGZjovg72h2xUAiPsxY4tqJgK38UY5cWkfa/ o2lD88r5dhEO2BxdHJDMpeO3V5Y8W38sVXedSl/rNTeqN0u1dxGmj2WyqAivlYXKBD u8k4yVJjYtNHqf9dFP061I4ch2diivRlHRTxoo0C1PiS8z1K9lGTajpcpJKWP7dchu xdsZvViyL2k2w== Received: from customer (localhost [127.0.0.1]) by submission (posteo.de) with ESMTPSA id 4TpJqL6Nygz9rxK; Mon, 4 Mar 2024 14:06:22 +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 13:06:22 +0000 From: albrecht.dress@posteo.de To: Francisco Olarte Cc: pgsql-general@lists.postgresql.org Subject: Re: Inconsistent results in timestamp/interval comparison In-Reply-To: References: <839f7314609c96c5123549a28d493534@posteo.de> Message-ID: <4faaea681e2e210bbfb7de92fc8cbb35@posteo.de> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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)=20 impression that Postgres _internally_ always uses numerical values (i.e.=20 the equivalent of EXTRACT(EPOCH =E2=80=A6)) for such calculations. My bad= =E2=80=A6 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) >=3D extract (epoch from= =20 '2 years'::interval), now() >=3D (t1 + '2 years'::interval) from testtab; produces consistent results. Thanks a lot for your help, Albrecht.