EXTRACT_EPOCH

Extract seconds since 1970-01-01 UTC from a timestamp, or total duration in seconds from an interval.

Category: datetimeReturns: DOUBLEDialect: PostgreSql

Syntax

EXTRACT(EPOCH FROM value)

Description

## Overview Convenience form of EXTRACT that returns the epoch value: for a TIMESTAMP it returns the number of seconds since the Unix epoch (1970-01-01 00:00:00 UTC) including fractional seconds; for an INTERVAL it returns the total duration in seconds. Use EXTRACT(EPOCH FROM ...) to convert timestamps into a numeric value suitable for arithmetic, bucketing, or storage as a BIGINT, and to convert intervals into a uniform seconds count regardless of how the interval is internally structured. ## Behavior - Returns a DOUBLE (includes fractional seconds with microsecond precision). - Returns NULL if the argument is NULL. - For TIMESTAMP WITHOUT TIME ZONE, the value is interpreted as UTC when computing the epoch. - For TIMESTAMP WITH TIME ZONE, the exact instant is used; the session zone does not affect the result. - For INTERVAL, the result is the total number of seconds represented by the interval, computed with 30-day months and 24-hour days. - The inverse operation for TIMESTAMP is TO_TIMESTAMP(epoch_seconds). ## Timezone handling - EXTRACT(EPOCH FROM tstz) is zone-stable: the same instant always returns the same epoch regardless of session zone. - EXTRACT(EPOCH FROM naive_ts) treats the naive timestamp as if it were UTC. If the naive value actually represents local time in a different zone, the result will be off by the zone offset. ## Compatibility - The SQL standard EXTRACT(EPOCH FROM ...) form is widely supported as an extension, though EPOCH is not listed among standard EXTRACT fields.

Parameters

Name	Type	Description
`value`		Specifies the timestamp or interval from which to extract the epoch value. For TIMESTAMP values, returns seconds since 1970-01-01 00:00:00 UTC. For INTERVAL values, returns the total duration in seconds.

Examples

-- Epoch seconds for Jan 1 2025 UTC
SELECT EXTRACT(EPOCH FROM TIMESTAMP '2025-01-01 00:00:00') AS epoch_s;

-- Total seconds in a 1-day-2-hour interval (93600)
SELECT EXTRACT(EPOCH FROM INTERVAL '1 day 2 hours') AS seconds;

-- Elapsed seconds between two timestamps
SELECT EXTRACT(EPOCH FROM TIMESTAMP '2025-03-15 12:00:00')
     - EXTRACT(EPOCH FROM TIMESTAMP '2025-03-15 08:00:00') AS elapsed_secs;

-- Round-trip: epoch back to a timestamp
SELECT TO_TIMESTAMP(EXTRACT(EPOCH FROM TIMESTAMP '2025-06-01 00:00:00')) AS restored;

-- Compute average request latency in seconds
SELECT AVG(EXTRACT(EPOCH FROM (finished_at - started_at))) AS avg_latency_secs
FROM telemetry.web.requests;

-- Bucket events into 10-minute windows by epoch arithmetic
SELECT FLOOR(EXTRACT(EPOCH FROM event_ts) / 600) * 600 AS bucket_start_epoch,
       COUNT(*) AS events
FROM telemetry.web.requests
GROUP BY bucket_start_epoch
ORDER BY bucket_start_epoch;

Pitfalls

For TIMESTAMP WITHOUT TIME ZONE, EPOCH treats the value as UTC. If your naive timestamps actually represent local time (a common anti-pattern), the epoch value will be shifted by the zone offset. Always store TIMESTAMP WITH TIME ZONE when a true instant is intended.
EPOCH for INTERVAL uses 30-day months and 24-hour days. An interval produced by AGE or by subtracting calendar-aware months will not round-trip exactly through epoch arithmetic.
The result is a DOUBLE and loses precision for very old dates or when combined with very large epochs. For sub-microsecond precision use EXTRACT(MICROSECOND FROM ...) arithmetic on the remainder.
Casting EXTRACT(EPOCH FROM ts) to BIGINT truncates rather than rounds. Use ROUND(..)::BIGINT or CAST(..AS BIGINT) explicitly if rounding is desired.