Epoch seconds in, a real timestamp out (and the reverse) — for log data and APIs.
Epoch seconds → timestamp
SELECT FROM_UNIXTIME(1700000000); -- MySQL
SELECT TO_TIMESTAMP(1700000000); -- PostgreSQL (returns timestamptz, UTC)
SELECT TIMESTAMP_SECONDS(1700000000); -- BigQuery
SELECT TO_TIMESTAMP(1700000000); -- Snowflake
SELECT datetime(1700000000, 'unixepoch'); -- SQLite
SELECT to_timestamp(1700000000); -- DuckDB
Timestamp → epoch seconds
SELECT UNIX_TIMESTAMP(ts); -- MySQL
SELECT EXTRACT(EPOCH FROM ts)::bigint; -- PostgreSQL
SELECT UNIX_SECONDS(ts); -- BigQuery (ts must be TIMESTAMP)
SELECT DATE_PART(EPOCH_SECOND, ts); -- Snowflake
SELECT CAST(strftime('%s', ts) AS INT); -- SQLite
SELECT epoch(ts); -- DuckDB
Milliseconds epoch
SELECT TIMESTAMP_MILLIS(1700000000000); -- BigQuery
SELECT TO_TIMESTAMP(1700000000000 / 1000); -- Postgres / Snowflake (divide first)
SELECT datetime(1700000000000/1000, 'unixepoch'); -- SQLite
SELECT make_timestamp(1700000000000 * 1000); -- DuckDB (expects microseconds)
Gotchas: mind the unit — most builtins expect seconds; if your value is in
milliseconds (13 digits) divide by 1000 (or use the _MILLIS variant in BigQuery). Conversions are
UTC-based: Postgres to_timestamp yields timestamptz, and rendering it applies the session
time zone — convert explicitly (see the time-zone recipe) if you need a specific zone.