2026-10-16 · 8 min read
- postgresql
- sql
- time
PostgreSQL has more than one clock, and the short name now() is the stable one. now() and CURRENT_TIMESTAMP both return transaction_timestamp(), the time at the start of the current transaction. It stays put while you insert, update, and call functions, and it changes only when a new transaction starts.
statement_timestamp() returns the start of the current statement. A second statement in the same transaction sees a later value. Inside one statement it does not move. clock_timestamp() returns the wall clock at the moment the function runs, so two calls in one statement can differ. timeofday() returns that same clock as text.
Match the function to the column's meaning
created_at usually means when the transaction that inserted the row began. A column default of now() does that, including for a multi-row insert, which then shares one timestamp. That sharing is useful: one statement is one write.
updated_at usually means a later write. A trigger that sets it to now() fails that meaning when the update runs in the same transaction as the insert, because both calls return the transaction start. Use statement_timestamp() if the statement boundary is the meaning you want. Use clock_timestamp() if you need the clock to move inside a long statement. Say which one in the migration, not in a comment that says current time.
- now() and CURRENT_TIMESTAMP are the transaction start.
- statement_timestamp() moves once per statement.
- clock_timestamp() can move during a statement.
- timeofday() is text. Do not write it into a timestamptz column without a cast you have tested.
Stability changes the plan, not just the value
transaction_timestamp() is stable. The planner is allowed to treat it as a constant for the transaction. clock_timestamp() is volatile, so it is rechecked and it constrains optimizations. Replacing now() with clock_timestamp() to make a test flake is a behavior change and a plan change.
A predicate such as created_at >= now() - interval '1 day' uses one cutoff for the whole transaction. That is what you want for a report. It is not what you want if the transaction is held open for minutes and the cutoff is supposed to follow the wall clock. Close the transaction, or call the volatile function on purpose.
Compare epochs, then look at the session zone
timestamptz stores an instant. The session TimeZone only affects how that instant is printed and how a timestamp without time zone is interpreted on the way in. Two clients can show an hour of difference for one value. extract(epoch from column) removes the display.
Take the integer seconds. Fractional microseconds matter when you are ordering events inside one second, and they do not matter when you are checking that an update failed to move at all. A difference of zero seconds between insert and update, inside one transaction that called now() both times, is the function working as specified.
Show the call in SQL and the instant in UTC
Format the trigger or the default in Flashman's SQL formatter so the function name is visible in review. The formatter does not connect to a database and cannot tell you which clock ran. Paste the integer epoch seconds into the timestamp converter and read the UTC time. Do not paste extract(epoch) multiplied by 1000. The converter treats its input as seconds.
Diff the insert epoch and the update epoch from the incident. Use the JSON formatter when those values are buried in an API body. The note next to the column should name now(), statement_timestamp(), or clock_timestamp() in the same words PostgreSQL uses.