Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1maBbS-0008MU-2f for pgsql-bugs@arkaria.postgresql.org; Tue, 12 Oct 2021 06:49:34 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1maBbR-0006Nu-1T for pgsql-bugs@arkaria.postgresql.org; Tue, 12 Oct 2021 06:49:33 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ma5yV-0001BQ-Mw for pgsql-bugs@lists.postgresql.org; Tue, 12 Oct 2021 00:48:59 +0000 Received: from mail-qk1-x72c.google.com ([2607:f8b0:4864:20::72c]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1ma5yS-0007rJ-P5 for pgsql-bugs@lists.postgresql.org; Tue, 12 Oct 2021 00:48:58 +0000 Received: by mail-qk1-x72c.google.com with SMTP id y10so1377377qkp.9 for ; Mon, 11 Oct 2021 17:48:56 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=mime-version:subject:from:in-reply-to:date:cc :content-transfer-encoding:message-id:references:to; bh=/qveA5v8x13uw0Y84ZFZUuVzmJpq3HLjgDEF0olb3L0=; b=OtPEaYzi7gjZE33IZVIIXUB6D7VAn3D92Cu0dLn/ZYf360ex1VGO3HiWjfLZrQnUy0 U/ZZgYbGRPTFzPNDP/sItV6L7JcwA+NwmC7XgBzBPl3LtUtD4dvU2tvluVrIdr6aS+sr jO+9ZR7ZgLNQ9VKQq++c35h8ISlIyV2nk9E9xRqFXV12dtTRrzKqpI3Ob7LT6q+m6R8o BNeyqQfMnTEqPGNmLgu5SS+Gh/XW9uUE4dY9IpwXzPTZS99coEnhxrM5S3Tdv1DbV59y r3c+vnXjTyKjG1W2EqO+ktgylFj4WGlBLM3W59GVNL7KAuLx5QCoH8pVKWqsxJR6nDUw jgbg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:mime-version:subject:from:in-reply-to:date:cc :content-transfer-encoding:message-id:references:to; bh=/qveA5v8x13uw0Y84ZFZUuVzmJpq3HLjgDEF0olb3L0=; b=aqyeczPRoNvHzQkPT7Mc3B/qNyeZpeiyM8UueKm/G5I6mqVpOk3RiJAYjLR8rgqp/3 H/8vdVV1o+95u4oZo4XEDu5E/4WUCyTKCTEUN9TjZcioDVs4S+RD426xmDRi08mw8JWh hJ/dN9kB6JwnJMMYyAyim8VXOUIgHyHRBoDJNB6Ti41LDOtue30zv0/1FhfRMUc+sXrT Rm3hXJ5j4M2VsPcpAYQKCo7E8AjfSdGewiBv3Mcb7gSd0g0YeM6QYT5nMfWN2TN2SMFb aicPCCWKj9ZodrHPDpIDp7nQUkXXb7IpAT3AG7z7xGs1mo4EzYt8mIdbf3xqB/0TQPZY t+Qw== X-Gm-Message-State: AOAM530Z7P1QGQkXifYtfTnVQBn8rgGMAFD0a0yaoEl56cNe8xqkBSKC /wwDIgdluBABVkSHC7lR/MDF4pfzZX8= X-Google-Smtp-Source: ABdhPJwDPM/UwWdHxdoInDmikTcAwD55l5zsdb58V7z/NgIrIZjGiju5fThoupAayL4e3Wigw9CLtA== X-Received: by 2002:a37:6396:: with SMTP id x144mr17178193qkb.397.1633999735679; Mon, 11 Oct 2021 17:48:55 -0700 (PDT) Received: from smtpclient.apple (pool-173-79-138-153.washdc.fios.verizon.net. [173.79.138.153]) by smtp.gmail.com with ESMTPSA id o130sm2270487qke.123.2021.10.11.17.48.54 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Mon, 11 Oct 2021 17:48:54 -0700 (PDT) Content-Type: text/plain; charset=us-ascii Mime-Version: 1.0 (Mac OS X Mail 14.0 \(3654.120.0.1.13\)) Subject: Re: Epoch from age is incorrect From: Zhihong Zhang In-Reply-To: <523248.1633989571@sss.pgh.pa.us> Date: Mon, 11 Oct 2021 20:48:54 -0400 Cc: pgsql-bugs Content-Transfer-Encoding: quoted-printable Message-Id: <9CA7E8DC-0364-46FD-B6B1-45289B3A0448@gmail.com> References: <523248.1633989571@sss.pgh.pa.us> To: Tom Lane X-Mailer: Apple Mail (2.3654.120.0.1.13) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk I thought age(time) was just a shorthand for (now() - time). Apparently = they yield very different results.=20 I will stay away from age() for our use case. Thanks for the = explanation. Zhihong > On Oct 11, 2021, at 5:59 PM, Tom Lane wrote: >=20 > Zhihong Zhang writes: >> The epoch from following statement is incorrect, >> test_db=3D# select extract(epoch from age(TIMESTAMP '2013-02-18 = 06:15:15')); >> date_part=20 >> ----------- >> 272396685 >> (1 row) >=20 > AFAICS it's following the documented definition of epoch for = intervals. >=20 > regression=3D# begin; > BEGIN > regression=3D*# select extract(epoch from age(TIMESTAMP '2013-02-18 = 06:15:15')); > extract =20 > ------------------ > 272223885.000000 > (1 row) >=20 > regression=3D*# select age(TIMESTAMP '2013-02-18 06:15:15');=20 > age =20 > --------------------------------- > 8 years 7 mons 20 days 17:44:45 > (1 row) >=20 > regression=3D*# select ((((8 * 365 + 7 * 30 + 20) * 24 + 17) * 60) + = 44) * 60 + 45; > ?column? =20 > ----------- > 272223885 > (1 row) >=20 >=20 > age() is useful for some purposes, but this isn't one of them. > I'd recommend considering it as a human-readable approximation > rather than something to do further arithmetic with. You'd get > more nearly the answer you probably want with >=20 > regression=3D*# select extract(epoch from localtimestamp - TIMESTAMP = '2013-02-18 06:15:15'); > extract =20 > ------------------ > 272806988.613568 > (1 row) >=20 > or even more to the point, >=20 > regression=3D*# select extract(epoch from now() - TIMESTAMPTZ = '2013-02-18 06:15:15'); > extract =20 > ------------------ > 272803388.613568 > (1 row) >=20 > regards, tom lane