Introduction to the PostgreSQL STATEMENT_TIMESTAMP() function

TheSTATEMENT_TIMESTAMP() function returns the start time of the current statement.

Here’s the syntax of theSTATEMENT_TIMESTAMP() function:

STATEMENT_TIMESTAMP()

TheSTATEMENT_TIMESTAMP() function doesn’t accept any argument. It returns a value of the typeTIMESTAMP WITH TIME ZONE, representing atimestamp at the start of the current statement.

PostgreSQL STATEMENT_TIMESTAMP() function examples

Let’s take some examples of using theSTATEMENT_TIMESTAMP() function.

1) Basic statement_timestamp() function example

The following statement uses theSTATEMENT_TIMESTAMP() function to retrieve the start time of the current statement:

SELECT STATEMENT_TIMESTAMP();

Output:

statement_timestamp------------------------------- 2024-03-20 11:30:47.001021-07(1 row)

The output indicates that theSTATEMENT_TIMESTAMP() function returns a timestamp with a time zone of the start time when the statement is executed.

2) Using the statement_timestamp() within a transaction

The following example calls theSTATEMENT_TIMESTAMP() function within a transaction multiple times and log the result into a table:

-- create a new table for loggingCREATE TABLE logs(   idSERIAL PRIMARY KEY,   started_atTIMESTAMP WITH TIME ZONE);-- start a transactionBEGIN;INSERT INTO logs(started_at)VALUES(statement_timestamp());SELECT pg_sleep(3);INSERT INTO logs(started_at)VALUES(statement_timestamp());SELECT pg_sleep(3);INSERT INTO logs(started_at)VALUES(statement_timestamp());END;-- retrieve data from the logs tableSELECT * FROM logs;

Output:

id |          started_at----+-------------------------------  1 | 2024-03-20 13:22:13.056783+07  2 | 2024-03-20 13:22:16.228492+07  3 | 2024-03-20 13:22:19.390211+07(3 rows)

In this example, we use thepg_sleep() function to delay the execution of eachINSERT statement.

Since we invoke theSTATEMENT_TIMESTAMP() function in its own SQL statement, it returns a timestamp differently with each call.

Notice that theSTATEMENT_TIMESTAMP() function is unlike theTRANSACTION_TIMESTAMP() function which does not change with each statement. TheTRANSACTION_TIMESTAMP() will return the same start time of the transaction.

3) Call the statement_timestamp() function multiple times within a statement

The following example calls theSTATEMENT_TIMESTAMP() function multiple times within a single statement:

SELECT  statement_timestamp(),  pg_sleep(3),  statement_timestamp(),  pg_sleep(3),  statement_timestamp();

Output:

-[ RECORD 1 ]-------+------------------------------statement_timestamp |2024-03-20 13:52:55.861004-07pg_sleep            |statement_timestamp |2024-03-20 13:52:55.861004-07pg_sleep            |statement_timestamp |2024-03-20 13:52:55.861004-07

Note that to display vertical results in psql, you execute the \x command first.

In this example, theSTATEMENT_TIMESTAMP() function returns the same values for all three calls, even though we call thepg_sleep() to delay execution between each call.

It is important to notice that this behavior contrasts with theCLOCK_TIMESTAMP() function, which continues to change as it progresses through the statement.

Summary

• Use theSTATEMENT_TIMESTAMP() function to retrieve the start time of the current statement.

Last updated onMarch 21, 2024

On this page

• Introduction to the PostgreSQL STATEMENT_TIMESTAMP() function
• PostgreSQL STATEMENT_TIMESTAMP() function examples
• Summary