PostgreSQL: now() vs. ‘NOW’::timestamp vs. clock_timestamp()

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.

now() vs. ‘NOW’::timestamptz

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:

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:

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”.

Using ‘NOW’::timestamptz in table definitions

What if we want timestamps in our table definition?

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:

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.

now() vs. clock_timestamp()

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:

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

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.

Finally …

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.