There are three different timestamp functions provided by PostgreSQL.
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
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.