What's the Time?
The different ways of getting the current time in Postgres and how they differ. This episode is brought to you by Hashrocket, expert consultants in PostgreSQL - learn more at https://hashrocket.com and www.pgcasts.com
we have had some audio issues with this recording, so you may want to turn the volume up a bit.
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()`:
```sql
select now();
```
This gives us the current time.
The `now()` function works the same as the `transaction_timestamp()`
function:
```sql
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()`:
```sql
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:
```sql
begin;
select transaction_timestamp();
```
We can wait a few seconds and then try that statement again:
```sql
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:
```sql
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:
```sql
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:
```sql
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.
Watch video What's the Time? online without registration, duration hours minute second in high quality. This video was added by user PG Casts by Hashrocket 20 September 2016, don't forget to share it with your friends and acquaintances, it has been viewed on our site 519 once and liked it 12 people.