Unix timestamps in BigQuery
BigQuery has a pair of functions for each unit: UNIX_* turns a TIMESTAMP into a number, and TIMESTAMP_* turns it back.
Get the current Unix timestamp
SELECT UNIX_SECONDS(CURRENT_TIMESTAMP());
SELECT UNIX_MILLIS(CURRENT_TIMESTAMP());
Convert a Unix timestamp to a date
SELECT TIMESTAMP_SECONDS(1700000000); -- 2023-11-14 22:13:20 UTC
SELECT TIMESTAMP_MILLIS(1700000000123);
SELECT TIMESTAMP_MICROS(1700000000123456);
Convert a date to a Unix timestamp
SELECT UNIX_SECONDS(TIMESTAMP '2026-11-14 10:00:00+00'); -- 1794650400
SELECT UNIX_SECONDS(TIMESTAMP('2026-11-14 15:30:00', 'Asia/Kolkata')); -- 1794650400
Timezones
SELECT FORMAT_TIMESTAMP('%F %T', TIMESTAMP_SECONDS(1700000000), 'Asia/Kolkata');
-- 2023-11-15 03:43:20
SELECT DATETIME(TIMESTAMP_SECONDS(1700000000), 'America/New_York'); -- wall-clock DATETIME
Common mistakes
- A
DATETIMEhas no zone.UNIX_SECONDSneeds aTIMESTAMP, so convert withTIMESTAMP(dt, 'zone')first. UNIX_SECONDSrounds down to the whole second. UseUNIX_MILLISorUNIX_MICROSto keep fractions.- Firebase and GA4 exports store
event_timestampin microseconds. UseTIMESTAMP_MICROS.
Complete example
SELECT
1700000000 AS epoch,
TIMESTAMP_SECONDS(1700000000) AS utc,
DATETIME(TIMESTAMP_SECONDS(1700000000), 'Asia/Tokyo') AS tokyo,
UNIX_SECONDS(TIMESTAMP_SECONDS(1700000000)) AS back;
Check any value with the epoch converter, or convert a whole column with the batch converter. All examples use 1700000000 (2023-11-14 22:13:20 UTC) and 1794650400 (2026-11-14 10:00:00 UTC).