PostgreSQL STATEMENT_TIMESTAMP Function

Summary: In this tutorial, you’ll learn how to retrieve the current time with the time zone offset at the start of an SQL statement using the PostgreSQL STATEMENT_TIMESTAMP function.

PostgreSQL STATEMENT_TIMESTAMP Function Overview #

The STATEMENT_TIMESTAMP function returns the start time of the current statement as a timestamp with a time zone.

The following shows the syntax of the STATEMENT_TIMESTAMP function:

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

The STATEMENT_TIMESTAMP function does not accept any argument. But you still need to include the parentheses. Otherwise, you’ll encounter the following error:

ERROR: column "statement_timestamp" does not existCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

PostgreSQL STATEMENT_TIMESTAMP Function Example #

First, show the session’s time zone:

SHOW TIMEZONE;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

  TimeZone
------------
 US/PacificCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Second, get the start time of the current statement:

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

Output:

    statement_timestamp
-------------------------------
 2025-03-19 18:34:13.701446-07Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Calling the STATEMENT_TIMESTAMP Function Multiple Times Within a Statement #

The following statement calls the STATEMENT_TIMESTAMP function twice within the same SQL statement:

SELECT
  STATEMENT_TIMESTAMP(),
  pg_sleep(2),
  STATEMENT_TIMESTAMP();Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this example, the pg_sleep delays between the calls of the STATEMENT_TIMESTAMP function by 2 seconds. However, both calls to the STATEMENT_TIMESTAMP function return the same value.

This is because the STATEMENT_TIMESTAMP returns the time when the current statement starts.

To retrieve the exact time that changes during the execution of a statement, you can use the CLOCK_TIMESTAMP() function.

Calling PostgreSQL STATEMENT_TIMESTAMP Function Within a Transaction #

First, create a table called logs for storing log messages:

CREATE TEMPORARY TABLE logs (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  message TEXT NOT NULL,
  started_at TIMESTAMPTZ NOT NULL
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Second, call the STATEMENT_TIMESTAMP() function within a transaction multiple times and log the time when the statement starts:

BEGIN;

INSERT INTO
  logs (message, started_at)
VALUES
  ('Transaction started', statement_timestamp());

SELECT
  pg_sleep(2);

INSERT INTO
  logs (message, started_at)
VALUES
  ('Transaction ended', statement_timestamp());

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

Third, retrieve data from the logs table:

SELECT * FROM logs;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

 id |       message        |        started_at
----+----------------------+-------------------------------
  1 | Transaction started  | 2025-03-19 18:53:49.46155-07
  2 | Transaction ended    | 2025-03-19 18:53:51.673564-07Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Since the function STATEMENT_TIMESTAMP recorded the timestamp when each statement started, the output indicates different timestamps within the transaction.

Summary #

• Use the STATEMENT_TIMESTAMP function to retrieve the current time with the time zone offset at the start of an SQL statement.

Quiz #