Everybody who has ever written any kind of database application had to use time and date. However, in PostgreSQL there are some subtle issues most people might not be aware of. To make it easier for beginners, as well as advanced people, to understand this vital topic I have decided to compile some examples which are important for your everyday work.
Table of Contents
Most people are not aware of the fact that there is actually a difference between now() as a function and 'NOW'::timestamptz as a constant. My description already contains the magic words “function” and “constant”. Why is that relevant? At first glance it seems to make no difference:
|
1 2 3 4 5 |
test=# SELECT now(), 'NOW'::timestamptz; now | timestamptz -------------------------------+------------------------------- 2020-04-01 10:56:21.310924+02 | 2020-04-01 10:56:21.310924+02 (1 row) |
As expected both flavors of “now” will return transaction time which means that the time within the very same transaction will stay the same. Here is an example:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
test=# BEGIN; BEGIN test=# SELECT now(), 'NOW'::timestamptz; now | timestamptz -------------------------------+------------------------------- 2020-04-01 10:57:08.555708+02 | 2020-04-01 10:57:08.555708+02 (1 row) test=# SELECT pg_sleep(10); pg_sleep ---------- (1 row) test=# SELECT now(), 'NOW'::timestamptz; now | timestamptz -------------------------------+------------------------------- 2020-04-01 10:57:08.555708+02 | 2020-04-01 10:57:08.555708+02 (1 row) test=# COMMIT; COMMIT |
Even if we sleep the time inside the transaction will be “frozen”.
What if we want timestamps in our table definition?
|
1 2 3 4 5 |
test=# CREATE TABLE a (field timestamptz DEFAULT 'NOW'::timestamptz); CREATE TABLE test=# CREATE TABLE b (field timestamptz DEFAULT now()); CREATE TABLE |
In this case it makes all the difference in the world. The following example shows why:
|
1 2 3 4 5 6 7 8 9 10 11 |
test=# d a Table 'public.a' Column | Type | Collation | Nullable | Default --------+--------------------------+-----------+----------+----------------------------------------------------------- field | timestamp with time zone | | | '2020-04-01 10:49:06.741606+02'::timestamp with time zone test=# d b Table 'public.b' Column | Type | Collation | Nullable | Default --------+--------------------------+-----------+----------+--------- field | timestamp with time zone | | | now() |
As I said before: now() is a function and therefore PostgreSQL will use the function call as the default value for the column. This means that the default value inserted will change over time as transactions are started and committed. However, 'NOW'::timestamptz is a constant. It is not a function call. Therefore the constant will be resolved, and the current timestamp will be added to the table definition. This is a small but important difference.
There is more: In PostgreSQL there is also a distinction between now() and clock_timestamp(). now() returns the same timestamp within the same transaction. Inside a transaction time does not appear to move forward. If you are using clock_timestamp() you will get the real timestamp. Why is that important? Let us take a look:
|
1 2 3 4 5 6 7 8 9 10 |
test=# CREATE TABLE t_time AS SELECT now() + (x || ' seconds')::interval AS x FROM generate_series(-1000000, 1000000) AS x; SELECT 2000001 test=# CREATE INDEX idx_time ON t_time (x); CREATE INDEX test=# ANALYZE; ANALYZE |
I have created a table containing 2 million entries as well as an index.
Let us check the difference between now() and clock_timestamp():
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
test=# explain SELECT * FROM t_time WHERE x = now(); QUERY PLAN ---------------------------------------------------------------------------- Index Only Scan using idx_time on t_time (cost=0.43..8.45 rows=1 width=8) Index Cond: (x = now()) (2 rows) test=# explain SELECT * FROM t_time WHERE x = clock_timestamp(); QUERY PLAN ------------------------------------------------------------------------- Gather (cost=1000.00..22350.11 rows=1 width=8) Workers Planned: 2 -> Parallel Seq Scan on t_time (cost=0.00..21350.01 rows=1 width=8) Filter: (x = clock_timestamp()) (4 rows) |
Keep in mind: now() stays the same … it does not change during the transaction. Thus PostgreSQL can evaluate the function once and look up the constant in the index. clock_timestamp() changes all the time. Therefore PostgreSQL cannot simply up the value in the index and return the result because clock_timestamp() changes from line to line. Note that this is not only a performance thing - it is mainly about returning correct results. You want your results to be consistent.
If you want to find out more about PostgreSQL and performance we recommend taking a look at pgwatch2 which is a comprehensive monitoring solution for PostgreSQL. pgwatch 1.7 has finally been released and we recommend checking it out.
You need to load content from reCAPTCHA to submit the form. Please note that doing so will share data with third-party providers.
More InformationYou are currently viewing a placeholder content from Facebook. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More InformationYou are currently viewing a placeholder content from X. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More Information
Thanks for this article, I am a fan of cybertec posts.
You could have mentioned the CURRENT_TIMESTAMP value.
thank you for your positive feedback. basically CURRENT_TIMESTAMP is a synonym for now(). now() itself is set during transaction start.
You also mention pgwatch2. Do you plan to implement timescaledb extension for PostgreSQL metric storage ?