UNIX_TIMESTAMP

Return the current Unix epoch seconds, or parse a string and return its epoch seconds.

Category: datetimeReturns: BIGINTDialect: Standard

Syntax

UNIX_TIMESTAMP([str [, fmt]])

Description

## Overview Returns Unix epoch seconds. With no arguments returns the current time as epoch seconds. With a string argument parses the string as a timestamp and returns the corresponding epoch. An optional format pattern controls how the string is parsed. Use UNIX_TIMESTAMP when your downstream systems (analytics pipelines, message formats, external APIs) expect integer epoch values rather than typed TIMESTAMP columns. ## Behavior - Returns a BIGINT representing whole seconds since 1970-01-01 00:00:00 UTC. - Zero-argument form uses CURRENT_TIMESTAMP and returns its epoch in whole seconds. - One- or two-argument form parses the string in the session zone when the format does not include a zone token. - Returns NULL if the string argument is NULL. - Parsing errors raise a runtime error; wrap with TRY or validate input upstream. - Fractional seconds in the input are silently truncated in the integer result. ## Timezone handling - For naive string inputs, the timestamp is interpreted in the session zone before conversion to UTC epoch. - Strings with embedded zone offsets (for example '2025-01-01 00:00:00+00:00') are parsed zone-aware. - For consistent cross-session behavior, store TIMESTAMP WITH TIME ZONE and convert explicitly with TO_UNIX_TIMESTAMP. ## Format specifiers - Percent-style tokens: %Y, %m, %d, %H, %i, %s. See DATE_FORMAT for the full list. ## Compatibility - Traditional epoch conversion function. For strictly typed conversion from TIMESTAMP to BIGINT use TO_UNIX_TIMESTAMP.

Parameters

Name	Type	Description
`str`		Specifies the timestamp string to parse. When omitted, UNIX_TIMESTAMP returns the current epoch.
`fmt`		Specifies the parse format pattern. When omitted, an ISO 8601 datetime form is assumed.

Examples

-- Current Unix epoch
SELECT UNIX_TIMESTAMP() AS now_epoch;

-- Parse an ISO datetime string to epoch seconds
SELECT UNIX_TIMESTAMP('2025-01-01 00:00:00') AS jan_1_epoch;

-- Parse with an explicit percent-style format
SELECT UNIX_TIMESTAMP('15/03/2025', '%d/%m/%Y') AS epoch;

-- Round-trip through FROM_UNIXTIME
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP('2025-06-15 12:00:00')) AS restored;

-- Compute elapsed epoch seconds per request
SELECT request_id, UNIX_TIMESTAMP(finished_at) - UNIX_TIMESTAMP(started_at) AS latency_secs
FROM telemetry.web.requests;

-- Bucket events into 5-minute windows
SELECT (UNIX_TIMESTAMP(event_ts) / 300) * 300 AS bucket_epoch, COUNT(*) AS events
FROM telemetry.web.requests
GROUP BY bucket_epoch
ORDER BY bucket_epoch;

Pitfalls

Naive string inputs are parsed in the session zone. The same string submitted from two different sessions can yield different epoch values. For deterministic parsing, include a zone token in the format.
UNIX_TIMESTAMP returns whole seconds. Fractional seconds in the input are truncated; use EXTRACT(EPOCH FROM TO_TIMESTAMP(str)) for sub-second precision.
The zero-argument form is transaction-stable if the engine delegates to CURRENT_TIMESTAMP. Avoid assuming it re-reads the wall clock on every call.
Bucketing via integer division (FLOOR(UNIX_TIMESTAMP(ts) / 300) * 300) is alignment-sensitive. For arbitrary origin alignment prefer DATE_BIN.