Josh Branchaud walk through the 3 different kinds of timestamp functions Postgres provides.
Postgres Version 9.5
When it comes to time, Postgres can do quite a bit. Even if you just want to know what time it is right now, there are a few different functions to consider.
The one we are probably the most familiar with is
This gives us the current time.
now() function works the same as the
We will continue to use
transaction_timestamp() throughout this episode,
but remember that it is interchangeable with
The other two timestamp functions we are going to be looking at are
select statement_timestamp(); select clock_timestamp();
They all seem to be doing the same thing, so, what's the difference?
Their names might give us an idea of what each really does. Let's start a transaction and take a close look:
begin; select transaction_timestamp();
We can wait a few seconds and then try that statement again:
Interesting, they both produce the exact same timestamp. That is because this function returns the timestamp of when the current transaction began.
Now, let's try out the
Some time has passed since we started the transaction and that is reflected
statement_timestamp() function provides the timestamp that
Postgres began executing the statement.
So, even if we have an expensive query, that references
statement_timestamp() multiple times, we will get the same timestamp each
select statement_timestamp(), pg_sleep(2)::text union all select statement_timestamp(), '';
We use the
pg_sleep function to simulate an expensive query. Though there
was a couple of seconds between the two different calls to
statement_timestamp(), they both produce the exact same timestamp.
And this is where
clock_timestamp() comes in to the picture. Let's try the
same query as above, but with
select clock_timestamp(), pg_sleep(2)::text union all select clock_timestamp(), '';
We can see the difference in the timestamps for this example because the
clock_timestamp() function gets the actual current time.
You now know the differences between the different timestamp functions.
If we are updating or creating a number of different records within a
transaction and we want all of their timestamps to be coordinated, we should
transaction_timestamp() or we can use
now() as a shorthand.
When more specific timestamps are called for, you may want to reach for
That's it for this episode. Until next time, may your data be consistent and your queries performant.
Thanks to Jack Christensen for recommending I use
pg_sleep(2)::text as a
way of simplifying the simulated expensive query.