Convert Unix Timestamp in Snowflake, BigQuery, and Redshift — Warehouse SQL

Cloud warehouses each have their own Unix timestamp functions and they are all subtly different. Snowflake uses TO_TIMESTAMP. BigQuery uses TIMESTAMP_SECONDS. Redshift uses a Postgres-style EXTRACT(epoch ...). Athena uses from_unixtime like Hive.

This guide gives you the right function for each warehouse with working SQL. Save it as a cheat sheet — you will reach for one of these every week.

Snowflake — TO_TIMESTAMP and DATE_PART

-- Unix seconds to TIMESTAMP_NTZ (no timezone)
SELECT TO_TIMESTAMP(1711000000);
-- 2024-03-21 07:46:40.000

-- With explicit unit
SELECT TO_TIMESTAMP(1711000000, 0);   -- seconds (default)
SELECT TO_TIMESTAMP(1711000000123, 3); -- milliseconds
SELECT TO_TIMESTAMP(1711000000123456, 6); -- microseconds

-- For TIMESTAMP_TZ (with timezone)
SELECT TO_TIMESTAMP_TZ(1711000000);

-- Convert TIMESTAMP back to Unix epoch seconds
SELECT DATE_PART(epoch_second, '2024-03-21 07:46:40'::TIMESTAMP);
-- 1711000000

-- Current Unix timestamp
SELECT DATE_PART(epoch_second, CURRENT_TIMESTAMP());

Snowflake's TO_TIMESTAMP is overloaded — pass an integer and a scale (0=sec, 3=ms, 6=us, 9=ns) and it converts correctly. This is cleaner than every other warehouse because you do not have to division-shift the value yourself.

Snowflake distinguishes TIMESTAMP_NTZ (no timezone), TIMESTAMP_LTZ (local timezone — converts to session zone), and TIMESTAMP_TZ (stores explicit zone). Default to TIMESTAMP_NTZ for ingested Unix timestamps.

BigQuery — TIMESTAMP_SECONDS, MILLIS, MICROS

-- Unix seconds to TIMESTAMP
SELECT TIMESTAMP_SECONDS(1711000000);
-- 2024-03-21 07:46:40 UTC

-- Milliseconds
SELECT TIMESTAMP_MILLIS(1711000000123);

-- Microseconds
SELECT TIMESTAMP_MICROS(1711000000123456);

-- Convert TIMESTAMP back to Unix
SELECT UNIX_SECONDS(TIMESTAMP '2024-03-21 07:46:40 UTC');
SELECT UNIX_MILLIS(CURRENT_TIMESTAMP());
SELECT UNIX_MICROS(CURRENT_TIMESTAMP());

-- Current time
SELECT CURRENT_TIMESTAMP();
SELECT CURRENT_DATETIME();   -- Without timezone
SELECT CURRENT_DATE();

BigQuery has the cleanest API of any warehouse — three explicit functions for the three precision levels, both directions. The result type is always TIMESTAMP (which is in UTC). Use DATETIME if you want a value without timezone, but be aware that DATETIME comparisons with TIMESTAMP require explicit casting.

Sell Custom Apparel — We Handle Printing & Free Shipping

Redshift and Athena — Postgres and Hive Heritage

-- REDSHIFT (Postgres heritage)
SELECT timestamp 'epoch' + 1711000000 * interval '1 second';
-- 2024-03-21 07:46:40

-- Or use TIMESTAMP_FROM_UNIXTIME (added in newer Redshift versions)
SELECT TIMESTAMP_FROM_UNIXTIME(1711000000);

-- Reverse direction
SELECT EXTRACT(epoch FROM '2024-03-21 07:46:40'::timestamp);
-- 1711000000

-- ATHENA / Trino / Presto (Hive heritage)
SELECT from_unixtime(1711000000);
-- 2024-03-21 07:46:40.000

-- For milliseconds in Athena
SELECT from_unixtime(1711000000123 / 1000);

-- Reverse direction
SELECT to_unixtime(timestamp '2024-03-21 07:46:40');
-- 1711000000.0

Redshift inherits its timestamp story from Postgres but is missing some of the newer functions. The "epoch + interval" trick is the universal pattern that works in any version.

Athena is built on Trino/Presto and uses Hive-style function names. from_unixtime takes seconds — divide millisecond timestamps yourself.

Warehouse Cheat Sheet

Warehouse	Unix → Date	Date → Unix	Current Unix
Snowflake	TO_TIMESTAMP(ts)	DATE_PART(epoch_second, dt)	DATE_PART(epoch_second, CURRENT_TIMESTAMP())
BigQuery	TIMESTAMP_SECONDS(ts)	UNIX_SECONDS(dt)	UNIX_SECONDS(CURRENT_TIMESTAMP())
Redshift	TIMESTAMP_FROM_UNIXTIME(ts)	EXTRACT(epoch FROM dt)	EXTRACT(epoch FROM GETDATE())
Athena/Trino	from_unixtime(ts)	to_unixtime(dt)	to_unixtime(now())
Databricks	from_unixtime(ts)	unix_timestamp(dt)	unix_timestamp()
Hive	from_unixtime(ts)	unix_timestamp(dt)	unix_timestamp()

For verification of any specific value, the free Unix timestamp converter auto-detects seconds vs milliseconds and shows UTC alongside human-readable output.

Frequently Asked Questions

How do I convert a Unix timestamp in Snowflake?

Use TO_TIMESTAMP(unix_value) for seconds. For milliseconds use TO_TIMESTAMP(value, 3). For microseconds use TO_TIMESTAMP(value, 6). The second argument is the precision scale, which is unique to Snowflake and means you do not have to divide the value yourself.

How do I convert a Unix timestamp in BigQuery?

BigQuery has three functions for the three precision levels: TIMESTAMP_SECONDS, TIMESTAMP_MILLIS, and TIMESTAMP_MICROS. Pick the one that matches your input. Going the other direction, use UNIX_SECONDS, UNIX_MILLIS, UNIX_MICROS.

How do I convert a Unix timestamp in Redshift?

Redshift inherits from Postgres so use timestamp "epoch" + interval pattern: SELECT timestamp "epoch" + ts * interval "1 second". Newer Redshift versions also support TIMESTAMP_FROM_UNIXTIME(ts). Both work in production.

How do I convert a Unix timestamp in Athena?

Athena uses from_unixtime(ts) like Hive and Trino. The function takes seconds — divide your value by 1000 first if it is in milliseconds. Going the other way, use to_unixtime(timestamp).

Why does my warehouse return UTC instead of local time?

Most warehouse timestamp functions return UTC by design. To display in a specific timezone, use AT TIME ZONE in BigQuery and Postgres-family, CONVERT_TIMEZONE in Snowflake and Redshift, or AT TIME ZONE in Athena. The underlying value never changes — only the display does.

Should I store timestamps as Unix integers or as TIMESTAMP columns in a warehouse?

Use TIMESTAMP columns in the warehouse. They support indexing, partition pruning, time-zone aware functions, and date arithmetic out of the box. Store the original Unix value as a separate BIGINT only if you need exact byte-for-byte fidelity with an upstream source.