PostgreSQL: now() vs. 'NOW'::timestamp vs. clock_timestamp()

05.2020 / Category: / Tags:

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.

time and date in postgresql - PostgreSQL: now() vs. 'NOW'::timestamp vs. clock_timestamp()

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:

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:

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?

In this case it makes all the difference in the world. The following example shows why:

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:

I have created a table containing 2 million entries as well as an index.

Let us check the difference between now() and clock_timestamp():

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.


0 0 votes
Article Rating
Notify of
Newest Most Voted
Inline Feedbacks
View all comments
Marc Rechté
Marc Rechté
4 years ago

You also mention pgwatch2. Do you plan to implement timescaledb extension for PostgreSQL metric storage ?

Marc Rechté
Marc Rechté
4 years ago

Thanks for this article, I am a fan of cybertec posts.

You could have mentioned the CURRENT_TIMESTAMP value.

Hans-Jürgen Schönig
Hans-Jürgen Schönig
4 years ago
Reply to  Marc Rechté

thank you for your positive feedback. basically CURRENT_TIMESTAMP is a synonym for now(). now() itself is set during transaction start.

CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf

+43 (0) 2622 93022-0

Get the newest PostgreSQL Info & Tools

    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    CYBERTEC PostgreSQL International GmbH
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram