Current epoch — epochtime.tools · A precision instrument for time

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 plain TIMESTAMP type maps to one of them through TIMESTAMP_TYPE_MAPPING.
  • Strings of digits passed to TO_TIMESTAMP are 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).

Official documentation

Last updated: