2026-10-16 · 6 min read · Rahul Chitturi
- postgresql
- sql
- time
now() in PostgreSQL is transaction_timestamp(). It is the start of the current transaction, and it does not move until that transaction ends. CURRENT_TIMESTAMP is the same value. A function that inserts a row and then updates it, both with now(), can store one instant for created_at and updated_at even though the statements were seconds apart.
statement_timestamp() is the start of the current statement. It changes between statements in the same transaction and stays fixed while one statement runs. clock_timestamp() reads the wall clock and can change during a statement. timeofday() is that clock as text, not as timestamptz.
Decide which clock the column means
A default of now() means the transaction start, which is what most created_at columns want. An updated_at trigger that also calls now() will not move during a later statement of the same transaction. If the product means the statement that wrote the row, use statement_timestamp(). If it means the clock as the row is written, use clock_timestamp().
now() is stable inside the transaction, so the planner may treat it as a constant. clock_timestamp() is volatile. Swapping them changes results and can change plans. Do not make that swap only to force a timestamp to look different in a test.
- Name the function in the ticket, not the word timestamp.
- Compare two writes that share a transaction before blaming the clock.
- Check a second statement before blaming a trigger.
- Remember that display TimeZone does not change the stored instant.
Compare instants as integer Unix seconds
extract(epoch from a timestamptz) is seconds since 1970-01-01T00:00:00Z, with a fractional part when the clock has microseconds. Two rows that look an hour apart on screen can be the same instant printed in two session zones. Compare the epoch.
Flashman's SQL formatter shows the function call and does not execute it. The timestamp converter takes Unix seconds: paste the integer part only. A millisecond count is interpreted as seconds and lands centuries away. Diff the two epoch values from the insert and the update, and use the JSON formatter when the API emits those timestamps as strings.