In a PostgreSQL transaction, why does now() return the same value on every call, and when do you need clock_timestamp() instead?
answer
- frozen for a reason
- transaction start, not call time
- statement start is another clock
- STABLE versus VOLATILE
- a quoted 'now' default
basics
~20 sIn 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 linesBEGIN;
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
Recall that now() and CURRENT_TIMESTAMP are the transaction start time and clock_timestamp() is the real clock.
Explain the four clocks, why freezing now() is deliberate, and how STABLE versus VOLATILE decides whether a time predicate can use an index.
Spot the production traps: stale timestamps in long transactions, timing code that measures nothing because it used now(), and quoted 'now' defaults.
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.