CLOCK_TIMESTAMP

Return the actual wall-clock timestamp, re-read on every call.

Syntax

CLOCK_TIMESTAMP()

Description

## Overview Returns the actual wall-clock date and time at the moment of invocation. Unlike NOW(), CURRENT_TIMESTAMP, and STATEMENT_TIMESTAMP (which are cached at transaction or statement start), CLOCK_TIMESTAMP is re-read from the system clock every time it is evaluated. Use CLOCK_TIMESTAMP for benchmarking, per-row timing, heartbeat tracking inside long-running statements, or anywhere you want the freshest possible instant regardless of transaction boundaries. ## Behavior - Returns a TIMESTAMP WITH TIME ZONE value. - Never returns NULL. - Not stable inside a statement: two successive calls can return different values. - Not stable inside a transaction: the value advances continuously. - Differs from NOW(), CURRENT_TIMESTAMP, TRANSACTION_TIMESTAMP (transaction-scope) and STATEMENT_TIMESTAMP (statement-scope). - Requires the function-call parentheses. ## Timezone handling - Carries the session time zone offset. - Use AT TIME ZONE for explicit conversion to a specific zone. ## Compatibility - Extension beyond the SQL standard. The standard does not expose a wall-clock, per-call timestamp primitive.

Examples

-- Current wall-clock timestamp
SELECT CLOCK_TIMESTAMP();

-- Two reads in the same statement return different values
SELECT CLOCK_TIMESTAMP() AS t1,
       pg_sleep(0.1),
       CLOCK_TIMESTAMP() AS t2;

-- Compare wall clock with transaction-scope reading
SELECT NOW() AS transaction_ts, CLOCK_TIMESTAMP() AS wall_clock_ts;

-- Measure per-row elapsed time inside a single statement
SELECT order_id,
       CLOCK_TIMESTAMP() AS scanned_at
FROM commerce.sales.orders
ORDER BY order_id
LIMIT 1000;

-- Benchmark an expensive function in a CTE
WITH timing AS (
  SELECT CLOCK_TIMESTAMP() AS before_ts, my_udf(payload) AS result, CLOCK_TIMESTAMP() AS after_ts
  FROM analytics.events.user_activity
  LIMIT 100
)
SELECT AVG(EXTRACT(EPOCH FROM (after_ts - before_ts))) AS avg_seconds FROM timing;

Pitfalls

• CLOCK_TIMESTAMP is non-deterministic inside a statement, which can prevent query optimizers from caching expressions and can disable certain transformations (for example, predicate pushdown across views that embed it).
• Inserting CLOCK_TIMESTAMP into multiple rows of a single INSERT...SELECT produces a different instant per row. Use NOW or CURRENT_TIMESTAMP if you want all rows from one statement to share the same timestamp.
• CLOCK_TIMESTAMP is subject to system clock changes. A clock skew correction, NTP step, or DST adjustment in the host OS can cause successive calls to appear to move backwards; use a monotonic source if you need strictly increasing readings.

See Also

• now
• current_timestamp
• statement_timestamp
• transaction_timestamp
• timeofday