17:58

Does anyone really know what time it is?

7 August 2018

PostgreSQL has a variety of ways of telling time: now(), statement_timestamp(), and clock_timestamp(). Each has a different sense of when “now” is:

• now() is the time at the start of the transaction; it never changes while the current transaction is open.
• statement_timestamp() is the time that the current statement started running. It changes from statement to statement, but is constant within a statement (which means it is constant within a PL/pgSQL function).
• clock_timestamp() changes each time it is called, regardless of context.

Each has its uses:

• For a predicate in a WHERE clause, you want either now() or statement_timestamp(). These work properly with indexes, because they are constant within the execution of a statement.
• If you need the time to update within a single transaction, use statement_timestamp(); otherwise, use now().
• Generally, you only use clock_timestamp() inside of a programming language procedure so you can get the current timestamp.

There are 2 comments.