Transcript

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 now(): 

select now();

This gives us the current time.

The now() function works the same as the transaction_timestamp() function: 

select transaction_timestamp();

We will continue to use transaction_timestamp() throughout this episode, but remember that it is interchangeable with now().

The other two timestamp functions we are going to be looking at are statement_timestamp() and clock_timestamp(): 

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: 

select transaction_timestamp();

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 statement_timestamp() function: 

select statement_timestamp();

Some time has passed since we started the transaction and that is reflected here. The 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 time: 

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 clock_timestamp() instead: 

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 reach for transaction_timestamp() or we can use now() as a shorthand.

When more specific timestamps are called for, you may want to reach for statement_timestamp() or clock_timestamp().

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.