TO_UNIX_TIMESTAMP

Convert a TIMESTAMP value into Unix epoch seconds.

Category: datetimeReturns: BIGINTDialect: Standard

Syntax

TO_UNIX_TIMESTAMP(timestamp)

Description

## Overview Converts a TIMESTAMP value to a BIGINT representing whole seconds since the Unix epoch (1970-01-01 00:00:00 UTC). Equivalent to CAST(EXTRACT(EPOCH FROM timestamp) AS BIGINT) with truncation toward zero on the fractional part. Use TO_UNIX_TIMESTAMP as the strictly typed counterpart to UNIX_TIMESTAMP: the input is always a TIMESTAMP (not a string) and the result is always a BIGINT (no implicit formatting). This is the right function for numeric export to downstream systems. ## Behavior - Returns a BIGINT. - Returns NULL if the argument is NULL. - Truncates fractional seconds toward zero; use EXTRACT(EPOCH FROM ...) if you need the DOUBLE with fractional precision. - For TIMESTAMP WITH TIME ZONE the instant is zone-stable and the result is independent of session zone. - For TIMESTAMP WITHOUT TIME ZONE the value is interpreted as UTC when computing the epoch. ## Timezone handling - Zone-stable for TIMESTAMP WITH TIME ZONE: the same instant always produces the same BIGINT. - Naive timestamps are treated as UTC. If a naive column actually represents local wall-clock time, cast it to TIMESTAMP WITH TIME ZONE at the session's source zone before calling. ## Compatibility - Timestamp-to-epoch conversion matching the convention in analytical SQL engines. Prefer this over EXTRACT(EPOCH FROM ...) when you want a typed BIGINT without fractional precision.

Parameters

Name	Type	Description
`timestamp`		Specifies the timestamp to convert. Both TIMESTAMP WITHOUT TIME ZONE and TIMESTAMP WITH TIME ZONE are accepted.

Examples

-- Jan 1 2025 UTC in epoch seconds
SELECT TO_UNIX_TIMESTAMP(TIMESTAMP '2025-01-01 00:00:00') AS epoch;

-- Current timestamp as epoch
SELECT TO_UNIX_TIMESTAMP(CURRENT_TIMESTAMP()) AS now_epoch;

-- Round-trip: back and forth between epoch and timestamp
SELECT FROM_UNIXTIME(TO_UNIX_TIMESTAMP(TIMESTAMP '2025-06-15 12:00:00')) AS restored_string;

-- Elapsed seconds between two events
SELECT TO_UNIX_TIMESTAMP(finished_at) - TO_UNIX_TIMESTAMP(started_at) AS elapsed_secs
FROM telemetry.jobs.completed_runs;

-- Write an epoch column alongside the source TIMESTAMP
SELECT event_id, event_ts, TO_UNIX_TIMESTAMP(event_ts) AS event_epoch
FROM analytics.events.user_activity;

Pitfalls

Naive TIMESTAMP inputs are treated as UTC. A column that stores local-time naive timestamps will produce epoch values shifted by the zone offset. Always store TIMESTAMP WITH TIME ZONE when the value represents an instant.
The result truncates fractional seconds. If sub-second precision matters, use EXTRACT(EPOCH FROM ...) and keep the DOUBLE result.
Dates before 1970 produce negative epoch values; downstream code that assumes non-negative epochs will misinterpret them.
Epoch seconds as BIGINT is large enough for the foreseeable future but overflow of INTEGER (32-bit) happens in 2038. Never cast the result to 32-bit INTEGER.