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.
Смотрите видео What's the Time? онлайн без регистрации, длительностью часов минут секунд в хорошем качестве. Это видео добавил пользователь PG Casts by Hashrocket 20 Сентябрь 2016, не забудьте поделиться им ссылкой с друзьями и знакомыми, на нашем сайте его посмотрели 519 раз и оно понравилось 12 людям.