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.