skip to content

In a PostgreSQL transaction, why does now() return the same value on every call, and when do you need clock_timestamp() instead?

level: middleimportance: should knowfreq 52%

answer

  1. frozen for a reason
  2. transaction start, not call time
  3. statement start is another clock
  4. STABLE versus VOLATILE
  5. a quoted 'now' default

basics

~20 s

In PostgreSQL, now() returns the current transaction's start time, so every call in one transaction agrees. clock_timestamp() reads the real clock at each call, which is what you need to time work inside a transaction or statement.

solid answer

~40 s

`now()`, `CURRENT_TIMESTAMP` and `transaction_timestamp()` all return the start time of the current transaction, deliberately frozen so that every row a transaction writes carries one consistent timestamp. `statement_timestamp()` is when the server received the current statement, and `clock_timestamp()` reads the actual clock on every call, so it changes even between rows of one statement. The volatility follows: `now()` is `STABLE`, so `departs_at > now()` can drive an index scan, while `clock_timestamp()` is `VOLATILE` and cannot be an index scan condition. Use `now()` for `created_at` defaults and consistent batch stamps; use `clock_timestamp()` to measure elapsed time inside a long transaction or loop. And never write a quoted `'now'` as a column default: the literal is converted when the table is created.

code

sql · 15 lines
sql
BEGIN;
SELECT now(), statement_timestamp(), clock_timestamp();
SELECT pg_sleep(2);
SELECT now() = transaction_timestamp() AS frozen,   -- true
       clock_timestamp() - now()       AS elapsed;  -- a little over 2 seconds
COMMIT;

SELECT g, now() AS tx_time, clock_timestamp() AS wall   -- wall differs per row
FROM generate_series(1, 3) AS g;

CREATE TABLE fare_quote (
  quote_id  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  amount    numeric(12,2) NOT NULL,
  quoted_at timestamptz   NOT NULL DEFAULT now()   -- not DEFAULT 'now'
);

go deeper

for a junior

Recall that now() and CURRENT_TIMESTAMP are the transaction start time and clock_timestamp() is the real clock.

for a middle

Explain the four clocks, why freezing now() is deliberate, and how STABLE versus VOLATILE decides whether a time predicate can use an index.

for a senior

Spot the production traps: stale timestamps in long transactions, timing code that measures nothing because it used now(), and quoted 'now' defaults.

for a principal

Set conventions for which clock defines event time across services, and keep transactions short enough that a transaction-start timestamp stays meaningful.

## Four clocks PostgreSQL exposes several notions of *now*, and they differ in **when** they are sampled: | Function | Returns | Volatility | |---|---|---| | `now()`, `CURRENT_TIMESTAMP`, `transaction_timestamp()` | start of the current transaction | `STABLE` | | `statement_timestamp()` | when the current statement's message arrived from the client | `STABLE` | | `clock_timestamp()` | the real time at this call | `VOLATILE` | | `timeofday()` | the real time, as formatted `text` (historical) | `VOLATILE` | `now()` is PostgreSQL's traditional name; `transaction_timestamp()` is the same function with a clearer name, and `CURRENT_TIMESTAMP` is the SQL-standard spelling. All return `timestamptz`. ## Why now() is frozen The manual calls the frozen value a **feature**: one transaction should have a single, consistent notion of the current time, so that every row it inserts or updates bears the same timestamp. A booking that writes a reservation, three fare lines and an audit row in one transaction stamps all five identically, which makes them easy to correlate. Consequences worth stating in an interview: - in **autocommit** mode each statement is its own transaction, so `now()` looks like it advances — it is the transaction start each time; - inside an explicit `BEGIN ... COMMIT` lasting ten minutes, `now()` is ten minutes stale by the end; - a procedure that issues `COMMIT` starts a new transaction, and `now()` moves with it. ## statement_timestamp() and clock_timestamp() - `statement_timestamp()` equals `transaction_timestamp()` during the first statement of a transaction and may differ in later ones; it is constant within a statement. - `clock_timestamp()` changes **even within a single statement**, so it gives every row of a `generate_series` a different value. - Use it for elapsed-time measurement: `clock_timestamp() - start` inside a PL/pgSQL loop, or logging how long each step of a batch took. ## Volatility and indexes A function's volatility tells the planner how often it may evaluate it. - `STABLE` functions return the same result for all rows within one statement, so the planner may evaluate `now()` once and use it as an **index scan condition**: `WHERE departs_at > now()` can use an index on `departs_at`. - `VOLATILE` functions must be evaluated per row; because an index scan evaluates its comparison value only once, a volatile function **cannot** be used in an index scan condition, and `WHERE departs_at > clock_timestamp()` becomes a filter. - Neither function is `IMMUTABLE`, so neither can appear in an index expression. ## The 'now' literal trap The special input string `'now'` is converted to a concrete time **as soon as it is read**. Used as a column default — `DEFAULT 'now'` or `DEFAULT TIMESTAMP 'now'` — it is read when the `CREATE TABLE` runs, so every future row would receive the table's creation time. `DEFAULT now()` or `DEFAULT CURRENT_TIMESTAMP` are function calls, evaluated at each insert. ## Transaction start is not commit time Because `now()` is sampled when a transaction **starts**, a column stamped with it records start order, not the order in which rows became visible: - transaction A starts at 10:00:00 and commits at 10:00:05; transaction B starts at 10:00:02 and commits at 10:00:03; - B's rows are visible first, yet A's rows carry the *earlier* timestamp; - a consumer that polls `WHERE quoted_at > last_seen` after reading B's rows sets `last_seen` to 10:00:02 and then never sees A's rows stamped 10:00:00. Using `clock_timestamp()` does not fix this, because any timestamp taken before commit can still be overtaken. Incremental readers need a margin that covers the longest transaction, or a mechanism that follows commit order rather than a clock; that design belongs to change-data-capture topics. The point here is to know that `now()` describes when the work began. ## Choosing in the fare service 1. `quoted_at timestamptz DEFAULT now()` — every fare line in one quote transaction shares one stamp. 2. Queries for upcoming departures use `departs_at > now()` so the index on `departs_at` stays usable. 3. A long repricing procedure logs `clock_timestamp()` at each step to measure where the time goes. 4. Long-running transactions are avoided anyway, since a stale `now()` is the least of their costs.

  • Why does WHERE departs_at > clock_timestamp() stop using an index on departs_at?
    `clock_timestamp()` is `VOLATILE`, so PostgreSQL must assume it changes per row. An index scan evaluates its comparison value once, so a volatile expression cannot be used as an index scan condition and the predicate is applied as a filter. `now()` is `STABLE`, so `departs_at > now()` can be an index condition.
  • In PostgreSQL, when does now() advance inside a stored procedure?
    Only when a new transaction starts. A procedure invoked with `CALL` may issue `COMMIT` or `ROLLBACK`; the next statement runs in a fresh transaction, so `now()` returns that new start time. A function cannot commit, so inside a function `now()` stays fixed for the whole call.

now() is the time printed on a boarding pass at check-in: every scanner along the way reads the same printed time, however long the passenger takes. clock_timestamp() is the clock on the gate wall, which shows something new every time you look.

  • now() returns the current wall-clock time at each call.
  • CURRENT_TIMESTAMP and now() return different values in one transaction.
  • clock_timestamp() is just a slower alias of now().
  • DEFAULT 'now' stamps each row with its insert time.
  • statement_timestamp() changes from row to row within one statement.