PostgreSQL offers many time and date functions. Among these functions, transaction_timestamp() is one of the most commonly used functions. The transaction_timestamp() method retrieves the timestamp at the start of the recent transaction. The transaction_timestamp() function works similarly to the now() and the current_timestamp() functions.

In this post, we will see how the transaction_timestamp() works.

What Does PostgreSQL transaction_timestamp() Function Do?

The transaction_timestamp() function retrieves the timestamp with the time zone offset for the recent/current transaction. The syntax for this function looks like:

transaction_timestamp();

The function requires no argument/parameter. It returns the timestamp when the recent transaction is initiated and its return type is TIMESTAMPTZ.

Let’s see how the transaction_timestamp() function works with the help of examples.

Example 1: Working of transaction_timestamp() Function

To understand how the transaction_timestamp() function works consider the following query:

SELECT transaction_timestamp();

The function returns the timestamp for the current transaction. The output is:

We can see that the function has returned the date and time at the start of this transaction along with the timezone information.

Note: One thing that needs to be taken notice of is, that the function returns the timestamp when the statement that contains the function is executed. Not the time when the stated function initiates execution. The concept will become more clear using an example.

Example 2: Using the Function with pg_sleep Function

We will use the pg_sleep() function with the transaction_timestamp(). The pg_time() function creates a pause for the specified seconds between the two transaction_timestamp() functions. the syntax can be written as:

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

In this specific case, we have placed a pause for 5 seconds between both functions. Now we will execute the query to see the result of the query. The output will look like this:

We can see that despite the pause we have specified between both functions, both functions returned the same timestamp. This thing advocates the fact that the function returns the timestamp when the statement that contains the function is executed not the time when the function is executed.

So that's all about the transaction_timestamp() function.

Conclusion

The transaction_timestamp() function returns the timestamp for the current transaction. This function does not need any argument. The return data type of this function is TIMESTAMP with a time zone. The function returns the timestamp when the statement that contains the function is executed. Not the time when the stated function initiates execution.