Timestamp Functions in PostgreSQL

There are three different timestamp functions provided by PostgreSQL.

  • transaction_timestamp()
  • statement_timestamp()
  • clock_timestamp()

I discuss the differences between each of these timestamp functions in a recent PG Cast. To demonstrate the clock_timestamp() function, I had to figure out a way to show that two invocations of that function should produce different timestamps. Even if they appear in the same SQL statement.

I came up with a somewhat convoluted approach:

select max(times.time), min(times.time)  
from (  
  select clock_timestamp()
  from generate_series(1,5000000)
) as times(time);

This is certainly a fun use of generate_series() and the min() and max() aggregate functions, but it also overcomplicates what I'm attempting to demonstrate.

In discussing with a coworker (Jack Christensen), I was able to come up with a couple simpler approaches:

select clock_timestamp() != clock_timestamp();  
 ?column?
----------
 t

This approach doesn't show us the timestamps, but it does show that two invocations of the clock_timestamp() at close to the same exact time produce unequal timestamps.

select clock_timestamp(), pg_sleep(2)  
union all  
select clock_timestamp(), '';  
        clock_timestamp        | pg_sleep
-------------------------------+----------
 2016-09-22 23:55:05.356836-05 |
 2016-09-22 23:55:07.357836-05 |

This approach uses the pg_sleep() function to simulate an expensive query. A query that takes 2 seconds to execute with invocations of clock_timestamp() on either end will produce timestamps that are 2 seconds apart.