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.