{"id":2515,"date":"2025-03-20T13:51:42","date_gmt":"2025-03-20T06:51:42","guid":{"rendered":"https:\/\/www.pgtutorial.com\/?page_id=2515"},"modified":"2025-03-21T20:55:07","modified_gmt":"2025-03-21T13:55:07","slug":"postgresql-transaction_timestamp","status":"publish","type":"page","link":"https:\/\/www.pgtutorial.com\/postgresql-date-functions\/postgresql-transaction_timestamp\/","title":{"rendered":"PostgreSQL TRANSACTION_TIMESTAMP Function"},"content":{"rendered":"\n

Summary<\/strong>: In this tutorial, you’ll learn the PostgreSQL TRANSACTION_TIMESTAMP<\/code> function and how to use it to get the current transaction’s start time.<\/p>\n\n\n\n

Introduction to PostgreSQL TRANSACTION_TIMESTAMP Function #<\/a><\/h2>\n\n\n\n

The TRANSACTION_TIMESTAMP<\/code> function lets you obtain the current transaction’s start time.<\/p>\n\n\n\n

The TRANSACTION_TIMESTAMP<\/code> has the following straightforward syntax:<\/p>\n\n\n

TRANSACTION_TIMESTAMP()<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Basic PostgreSQL TRANSACTION_TIMESTAMP function example #<\/a><\/h2>\n\n\n\n
The following example uses the TRANSACTION_TIMESTAMP<\/code> function to obtain the start time of the current transaction:<\/p>\n\n\n
SELECT<\/span> TRANSACTION_TIMESTAMP();<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 transaction_timestamp\n------------------------<\/span>\n 2025<\/span>-03<\/span>-19<\/span> 21<\/span>:23<\/span>:37.665989<\/span>-07<\/span><\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Calling the TRANSACTION_TIMESTAMP function within a statement multiple times #<\/a><\/h2>\n\n\n\n
The following example illustrates how to call the TRANSACTION_TIMESTAMP()<\/code> function multiple times within a single statement:<\/p>\n\n\n
SELECT<\/span>\n    TRANSACTION_TIMESTAMP() AS<\/span> first_call,\n    pg_sleep(2<\/span>),\n    TRANSACTION_TIMESTAMP() AS<\/span> second_call;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
          first_call           | pg_sleep |          second_call\n-------------------------------+----------+-------------------------------<\/span>\n 2025<\/span>-03<\/span>-19<\/span> 23<\/span>:50<\/span>:20.314038<\/span>-07<\/span> |          | 2025<\/span>-03<\/span>-19<\/span> 23<\/span>:50<\/span>:20.314038<\/span>-07<\/span><\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Even though we have a delay (2s) between the first and second calls of the TRANSACTION_TIMESTAMP()<\/code> function, both return the same timestamp.<\/p>\n\n\n\n
It means that the TRANSACTION_TIMESTAMP<\/code> returns the time when the statement executes.<\/p>\n\n\n\n
Finding transactions started 5 minutes ago #<\/a><\/h2>\n\n\n\n
The following query uses the TRANSACTION_TIMESTAMP()<\/code> function in the WHERE<\/code> clause to find the transaction started 5 minutes ago:<\/p>\n\n\n
SELECT<\/span>\n    *\nFROM<\/span>\n    pg_stat_activity\nWHERE<\/span>\n    state = 'active'<\/span>\n    AND<\/span> TRANSACTION_TIMESTAMP() < CURRENT_TIMESTAMP<\/span> - INTERVAL<\/span> '5 minutes'<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
This query allows you to identify the old transactions that may cause locks.<\/p>\n\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
The TRANSACTION_TIMESTAMP<\/code> function accepts no argument. It returns a value of the TIMESTAMP WITH TIME ZONE<\/a><\/code> type.<\/p>\n\n\n\n