Does anyone really know what time it is?
7 August 2018
PostgreSQL has a variety of ways of telling time:
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
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
- Generally, you only use
clock_timestamp()inside of a programming language procedure so you can get the current timestamp.
There are 2 comments.
Bruce Momjian at 19:08, 7 August 2018:
FYI, for symmetry, ‘now’ is the same as transaction_timestamp.
Xof at 19:11, 7 August 2018:
Indeed so, but
now()is so firmly imperative. :)