March 03, 2017

Tips & Tricks: What Time is It, PostgreSQL?

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.

postgres=# begin transaction;
BEGIN

postgres=# select current_timestamp, localtimestamp, transaction_timestamp(), statement_timestamp(), clock_timestamp();

-[ RECORD 1 ]---------------------------------------
now                    2017-03-02 16:37:05.126939-05
timestamp              2017-03-02 16:37:05.126939   
transaction_timestamp  2017-03-02 16:37:05.126939-05
statement_timestamp    2017-03-02 16:37:08.790847-05
clock_timestamp        2017-03-02 16:37:08.790961-05

postgres=# select pg_sleep for ('180 seconds');
 pg_sleep for

(1 row)

postgres=# select current_timestamp, localtimestamp, transaction_timestamp(), statement_timestamp(), clock_timestamp();

-[ RECORD 1 ]---------------------------------------
now                    2017-03-02 16:37:05.126939-05
timestamp              2017-03-02 16:37:05.126939
transaction_timestamp  2017-03-02 16:37:05.126939-05
statement_timestamp    2017-03-02 16:40:19.042447-05
clock_timestamp        2017-03-02 16:40:19.042556-05

postgres=# end transaction;
COMMIT

Observations

  • 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