Unix timestamps in Snowflake
Snowflake converts Unix numbers with TO_TIMESTAMP and reads them back with DATE_PART. Which of its three timestamp types you use decides how zones apply.
Get the current Unix timestamp
SELECT DATE_PART(EPOCH_SECOND, CURRENT_TIMESTAMP());
SELECT DATE_PART(EPOCH_MILLISECOND, CURRENT_TIMESTAMP());
Convert a Unix timestamp to a date
SELECT TO_TIMESTAMP_NTZ(1700000000); -- 2023-11-14 22:13:20.000
SELECT TO_TIMESTAMP_NTZ(1700000000123, 3); -- scale 3 = milliseconds
SELECT TO_TIMESTAMP_NTZ(1700000000123456, 6);
Convert a date to a Unix timestamp
SELECT DATE_PART(EPOCH_SECOND, '2026-11-14 10:00:00'::TIMESTAMP_NTZ); -- 1794650400
SELECT DATE_PART(EPOCH_SECOND, '2026-11-14 15:30:00 +05:30'::TIMESTAMP_TZ); -- 1794650400
Timezones
SELECT CONVERT_TIMEZONE('UTC', 'Asia/Kolkata', TO_TIMESTAMP_NTZ(1700000000));
-- 2023-11-15 03:43:20.000
Common mistakes
- Give the scale for anything other than seconds. Without it, numbers are read as seconds, so a millisecond value lands tens of thousands of years away or fails.
- TIMESTAMP_NTZ has no zone, TIMESTAMP_LTZ shows in the session's
TIMEZONE, and TIMESTAMP_TZ stores an offset. The plainTIMESTAMPtype maps to one of them throughTIMESTAMP_TYPE_MAPPING. - Strings of digits passed to
TO_TIMESTAMPare interpreted by their size. Cast to a number first to get predictable results.
Complete example
SELECT
1700000000 AS epoch,
TO_TIMESTAMP_NTZ(1700000000) AS utc,
CONVERT_TIMEZONE('UTC', 'Asia/Tokyo', TO_TIMESTAMP_NTZ(1700000000)) AS tokyo,
DATE_PART(EPOCH_SECOND, TO_TIMESTAMP_NTZ(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).