Recently a colleague asked me a simple question. How do I get the current time within a transaction so that I can track the different stages of SQL execution? This seemed to me as if it ought to be easy, but there are some subtleties to consider when telling time.
Most developers are familiar with current_timestamp, but PostgreSQL provides several built-in date functions that return something other than what is available using current_timestamp.
Did you know that in general, a timestamp value is the time at the beginning of the current transaction, not the time when it ends (see the PostgreSQL manual’s discussion)? Timestamp values don’t change as a transaction progresses, no matter how long it may run. Although this can be very useful for consistency, it’s not necessarily what a developer wants for every purpose. Fortunately, PostgreSQL provides a solution, should you want a different view of time while your transaction is running.
The table below summarizes the various timestamp functions available in PostgreSQL.
Data Type | Time returned |
---|---|
current_timestamp | current_timestamp, when the transaction begins |
localtimestamp | current_timestamp, when the transaction begins |
transaction_timestamp() | current_timestamp, when the transaction begins |
statement_timestamp() | start of current statement |
clock_timestamp() | actual current time, which changes even within a single SQL command |
The first two of these are SQL-standard functions. The last three are provided as extensions by PostgreSQL.
Here’s an example that shows how these timestamps differ. I’ve introduced some delays to make the time differences easier to observe. Note that I’m using extended display (\x) in psql to show the results in rows rather than columns.
Note that the current_timestamp (“now”), localtimestamp, and transaction_timestamp are unchanged for the duration of the transaction; they are always the time at the start of the transaction.
statement_timestamp() reflects the time when the current statement (“select …”) starts. But clock_timestamp is the actual current time, so will change within even a single SQL command.
There is a slight difference between the statement_timestamp and clock_timestamp values, which can be attributed to the time for calculating one value after the other. As described above, the clock_timestamp is not necessarily ever the same as the statement_timestamp.
Why is there both current_timestamp and transaction_timestamp() when they provide the same information? transaction_timestamp() is a PostgreSQL addition to provide some clarity about the data returned, the start of the transaction.
Why do current_timestamp and transaction_timestamp() use the start of a transaction for their time? The idea is to provide a consistent time for no matter how many individual statements may occur within a single transaction, an anchor-time, as it were. If you’re looking to identify the length of individual statements within a transaction, you’ll want to use the statement_timestamp().
Glenn Street
Data Architect