PostgreSQL TRANSACTION_TIMESTAMP Function

Summary: In this tutorial, you’ll learn the PostgreSQL TRANSACTION_TIMESTAMP function and how to use it to get the current transaction’s start time.

Introduction to PostgreSQL TRANSACTION_TIMESTAMP Function #

The TRANSACTION_TIMESTAMP function lets you obtain the current transaction’s start time.

The TRANSACTION_TIMESTAMP has the following straightforward syntax:

TRANSACTION_TIMESTAMP()Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The TRANSACTION_TIMESTAMP function accepts no argument. It returns a value of the TIMESTAMP WITH TIME ZONE type.

Basic PostgreSQL TRANSACTION_TIMESTAMP function example #

The following example uses the TRANSACTION_TIMESTAMP function to obtain the start time of the current transaction:

SELECT TRANSACTION_TIMESTAMP();Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

 transaction_timestamp
------------------------
 2025-03-19 21:23:37.665989-07Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Calling the TRANSACTION_TIMESTAMP function within a statement multiple times #

The following example illustrates how to call the TRANSACTION_TIMESTAMP() function multiple times within a single statement:

SELECT
    TRANSACTION_TIMESTAMP() AS first_call,
    pg_sleep(2),
    TRANSACTION_TIMESTAMP() AS second_call;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

          first_call           | pg_sleep |          second_call
-------------------------------+----------+-------------------------------
 2025-03-19 23:50:20.314038-07 |          | 2025-03-19 23:50:20.314038-07Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Even though we have a delay (2s) between the first and second calls of the TRANSACTION_TIMESTAMP() function, both return the same timestamp.

It means that the TRANSACTION_TIMESTAMP returns the time when the statement executes.

Finding transactions started 5 minutes ago #

The following query uses the TRANSACTION_TIMESTAMP() function in the WHERE clause to find the transaction started 5 minutes ago:

SELECT
    *
FROM
    pg_stat_activity
WHERE
    state = 'active'
    AND TRANSACTION_TIMESTAMP() < CURRENT_TIMESTAMP - INTERVAL '5 minutes';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

This query allows you to identify the old transactions that may cause locks.

Summary #

• The TRANSACTION_TIMESTAMP function allows you to get the current transaction’s start time.

Quiz #