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

Get the current Unix timestamp

SELECT EXTRACT(EPOCH FROM now())::bigint;            -- seconds
SELECT (EXTRACT(EPOCH FROM now()) * 1000)::bigint;   -- milliseconds

Convert a Unix timestamp to a date

SELECT to_timestamp(1700000000);              -- 2023-11-14 22:13:20+00 (shown in the session zone)
SELECT to_timestamp(1700000000123 / 1000.0);   -- milliseconds

Convert a date to a Unix timestamp

SELECT EXTRACT(EPOCH FROM timestamptz '2026-11-14 10:00:00+00')::bigint;   -- 1794650400
SELECT EXTRACT(EPOCH FROM created_at)::bigint FROM orders;

Timezones

SELECT to_timestamp(1700000000) AT TIME ZONE 'Asia/Kolkata';   -- 2023-11-15 03:43:20 (timestamp, no zone)
SET TIME ZONE 'America/New_York';                              -- how timestamptz values are displayed

Common mistakes

  • timestamp (without time zone) stores a wall-clock reading. EXTRACT(EPOCH ...) treats it as if it were UTC, which is wrong if you stored local times.
  • AT TIME ZONE converts in opposite directions for timestamptz and timestamp inputs. Check the type of what you pass.
  • Since PostgreSQL 14, EXTRACT returns numeric. Cast to bigint for whole seconds.

Complete example

SELECT
  1700000000                                   AS epoch,
  to_timestamp(1700000000)                     AS utc,
  to_timestamp(1700000000) AT TIME ZONE 'Asia/Tokyo' AS tokyo_wall_clock,
  EXTRACT(EPOCH FROM to_timestamp(1700000000))::bigint 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: