PostgreSQL Current Timestamp
PostgreSQL provides several functions for getting the current timestamp, each with important behavioral differences. Understanding when to use each one is essential for writing correct database code, especially in transactions where timing consistency matters.
The most important distinction is between NOW() (or equivalently CURRENT_TIMESTAMP) and CLOCK_TIMESTAMP(). NOW() returns the timestamp at the start of the current transaction — it doesn't change within a transaction, no matter how long the transaction runs. CLOCK_TIMESTAMP() returns the actual current wall-clock time and changes with each call, even within a transaction. This difference is critical for transaction logging and performance measurement.
PostgreSQL current timestamp functions
NOW() / CURRENT_TIMESTAMP:
SELECT NOW();
Returns the transaction start time as timestamptz. Stable within a transaction.
CLOCK_TIMESTAMP():
SELECT CLOCK_TIMESTAMP();
Returns the actual current time. Changes within a transaction. Use for performance measurement.
TRANSACTION_TIMESTAMP():
SELECT TRANSACTION_TIMESTAMP();
Identical to NOW() — returns transaction start time.
Statement_TIMESTAMP():
SELECT STATEMENT_TIMESTAMP();
Returns the start time of the current SQL statement within a transaction.
As Unix timestamp (seconds)
SELECT EXTRACT(EPOCH FROM NOW())::INT;
As Unix timestamp (milliseconds):
SELECT (EXTRACT(EPOCH FROM NOW()) * 1000)::BIGINT;