In PostgreSQL, the transaction_timestamp() function returns the current date and time (including the time zone offset), at the start of the current transaction.

It’s the equivalent of the traditional Postgres function now().

It’s also similar to the current_timestamp function (when called without an argument), except that it’s named to clearly reflect what it does.

The transaction_timestamp() function doesn’t accept any parameters, so you can’t specify its precision, whereas current_timestamp can be called with or without a precision parameter.

Also, transaction_timestamp() is a non-SQL-standard function.

Syntax

The syntax goes like this:

transaction_timestamp()

No arguments are required or accepted.

Basic Example

Here’s a basic example to demonstrate.

SELECT transaction_timestamp();

Result:

2020-07-02 08:23:08.810484+10

Within a Transaction

Here’s an example to demonstrate how it works within a transaction.

BEGIN;
SELECT transaction_timestamp();
SELECT pg_sleep(5);
SELECT transaction_timestamp();
SELECT pg_sleep(5);
SELECT transaction_timestamp();
COMMIT;

Here’s the full output within my terminal when using psql:

postgres=# BEGIN;
BEGIN
postgres=# SELECT transaction_timestamp();
     transaction_timestamp     
-------------------------------
 2020-07-02 08:27:04.229266+10
(1 row)


postgres=# SELECT pg_sleep(5);
 pg_sleep 
----------
 
(1 row)


postgres=# SELECT transaction_timestamp();
     transaction_timestamp     
-------------------------------
 2020-07-02 08:27:04.229266+10
(1 row)


postgres=# SELECT pg_sleep(5);
 pg_sleep 
----------
 
(1 row)


postgres=# SELECT transaction_timestamp();
     transaction_timestamp     
-------------------------------
 2020-07-02 08:27:04.229266+10
(1 row)


postgres=# COMMIT;
COMMIT

All three time values are identical, even though the pg_sleep() function was used to delay execution between each call to transaction_timestamp(), each of which happened to be in its own SQL statement.

So we can see that the time returned for each statement is based on the start time of the current transaction – not the statement. It doesn’t change as the transaction progresses.

This allows a single transaction to have a consistent notion of the “current” time, so that multiple modifications within the same transaction bear the same time stamp.

Multiple Calls within a Statement

It also doesn’t change as the statement progresses.

\x
SELECT 
  transaction_timestamp(),
  pg_sleep(5),
  transaction_timestamp(),
  pg_sleep(5),
  transaction_timestamp();

Result (using vertical output):

transaction_timestamp | 2020-07-02 09:15:56.154175+10
pg_sleep              | 
transaction_timestamp | 2020-07-02 09:15:56.154175+10
pg_sleep              | 
transaction_timestamp | 2020-07-02 09:15:56.154175+10

Again, all three time values are identical, even though the pg_sleep() function was used to delay execution between each call to transaction_timestamp().

This is in contrast to statement_timestamp(), which does change with each statement, and also the clock_timestamp() function, which changes even as it progresses through each statement (if called multiple times within the statement).