To: pgsql-admin <pgsql-admin@postgresql.org>
From: Holger Jakobs <holger@jakobs.com>
Subject: now() and statement_timestamp()
Message-ID: <536812b3-8040-371d-506d-266bcf10ec2f@jakobs.com>
Date: Thu, 27 May 2021 18:26:42 +0200
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101
 Thunderbird/78.10.2
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha256;
 protocol="application/pgp-signature";
 boundary="eM8CSOLjSgtTxQ5bxcrwiaz5ghBQrlhxF"
Archived-At: 
 <https://www.postgresql.org/message-id/536812b3-8040-371d-506d-266bcf10ec2f%40jakobs.com>
Precedence: bulk

This is an OpenPGP/MIME signed message (RFC 4880 and 3156)
--eM8CSOLjSgtTxQ5bxcrwiaz5ghBQrlhxF
Content-Type: multipart/mixed; boundary="2gaJaBYHb0nrZVs5Ro4LJXTb7oSb7mWWN";
 protected-headers="v1"
From: Holger Jakobs <holger@jakobs.com>
To: pgsql-admin <pgsql-admin@postgresql.org>
Message-ID: <536812b3-8040-371d-506d-266bcf10ec2f@jakobs.com>
Subject: now() and statement_timestamp()

--2gaJaBYHb0nrZVs5Ro4LJXTb7oSb7mWWN
Content-Type: multipart/alternative;
 boundary="------------2C21A821B998F19D7254EB5C"
Content-Language: de-DE

This is a multi-part message in MIME format.
--------------2C21A821B998F19D7254EB5C
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: quoted-printable

According to the documentation. now() should yield the timestamp of the=20
current transaction, while statement_timestamp() should yield the=20
timestamp of the current statement.

In case of Autocommit, both should be identical, in case of a running=20
transaction, they should be different.

Funny enough, psql and executing the same statement from within an=20
application yield different results. I tried a Tcl script and DBeaver.

Statement: |SELECT now(), statement_timestamp();|

psql: |2021-05-07 13:31:10.135851+02 | 2021-05-07 13:31:10.135851+02|

DBeaver: |2021-05-07 13:31:00.633784+02|2021-05-07 13:31:00.633913+02|

Tcl script: |{now {2021-05-07 13:24:54.659837+02} statement_timestamp=20
{2021-05-07 13:24:54.65985+02}}|

So in psql now() and statement_timestamp() are identical when not in a=20
transaction, while in the others the two values vary a little bit,=20
roughly 100 microseconds.

How come the same statements aren't behaving identical. I'm using a=20
current PostgreSQL server on a Linux machine.

--=20
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012


--------------2C21A821B998F19D7254EB5C
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: quoted-printable

<html>
  <head>

    <meta http-equiv=3D"content-type" content=3D"text/html; charset=3DUTF=
-8">
  </head>
  <body>
    <p>According to the documentation. now() should yield the timestamp
      of the current transaction, while statement_timestamp() should
      yield the timestamp of the current statement.</p>
    <p>In case of Autocommit, both should be identical, in case of a
      running transaction, they should be different.</p>
    <p>Funny enough, psql and executing the same statement from within
      an application yield different results. I tried a Tcl script and
      DBeaver.
    </p>
    <p>Statement: <code>SELECT now(), statement_timestamp();</code></p>
    <p>psql: <code>2021-05-07 13:31:10.135851+02 | 2021-05-07
        13:31:10.135851+02</code></p>
    <p>DBeaver: <code>2021-05-07 13:31:00.633784+02|2021-05-07
        13:31:00.633913+02</code></p>
    <p>Tcl script: <code>{now {2021-05-07 13:24:54.659837+02}
        statement_timestamp {2021-05-07 13:24:54.65985+02}}</code></p>
    <p>So in psql now() and statement_timestamp() are identical when not
      in a transaction, while in the others the two values vary a little
      bit, roughly 100 microseconds.</p>
    <p>How come the same statements aren't behaving identical. I'm using
      a current PostgreSQL server on a Linux machine.<br>
    </p>
    <pre class=3D"moz-signature" cols=3D"72">--=20
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012</pre>
  </body>
</html>

--------------2C21A821B998F19D7254EB5C--

--2gaJaBYHb0nrZVs5Ro4LJXTb7oSb7mWWN--

--eM8CSOLjSgtTxQ5bxcrwiaz5ghBQrlhxF
Content-Type: application/pgp-signature; name="OpenPGP_signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="OpenPGP_signature"

-----BEGIN PGP SIGNATURE-----

wmMEABEIACMWIQScuJCxWJK1p5BOebGKV5yTsxlhugUCYK/IQgUDAAAAAAAKCRCKV5yTsxlhuhhP
AJ92aZAio3j3+HyviS5lePxRJAmh5wCgx3U6oCf40iGCQDp2ZP7YTNxsXzA=
=Gar8
-----END PGP SIGNATURE-----

--eM8CSOLjSgtTxQ5bxcrwiaz5ghBQrlhxF--