Unix timestamp in PostgreSQL

SELECT floor(extract(epoch FROM now()))::bigint; returns the current Unix time in whole seconds. extract(epoch FROM …) counts seconds since 1970-01-01 00:00:00 UTC, microseconds included, and to_timestamp() goes the other way.

SELECT extract(epoch FROM now());                         -- 1792022016.801421
SELECT floor(extract(epoch FROM now()))::bigint;          -- 1792022016
SELECT floor(extract(epoch FROM now()) * 1000)::bigint;   -- 1792022016803
SELECT to_timestamp(1700000000);                          -- 2023-11-14 22:13:20+00

Those ran on PostgreSQL 18.6 at 2026-10-14T23:53:36Z, with the session TimeZone at Etc/UTC. Since PostgreSQL 14, extract returns numeric; date_part('epoch', …) still returns double precision.

to_timestamp() and the session TimeZone

to_timestamp() returns a timestamptz, an instant, which psql prints in the session’s TimeZone:

SET TimeZone = 'America/New_York';
SELECT to_timestamp(1700000000);                      -- 2023-11-14 17:13:20-05
SELECT to_timestamp(1700000000) AT TIME ZONE 'UTC';   -- 2023-11-14 22:13:20

The tool below starts on the same conversion, with its clock at the run’s instant until the page loads: 1700000000 in America/New_York is 2023-11-14 17:13:20, UTC-05:00. AT TIME ZONE 'UTC' turns the instant into a plain timestamp, the wall clock in UTC.

timestamp vs timestamptz

A timestamptz holds an instant, so its epoch is right whatever the session’s zone. A plain timestamp has no zone at all, and extract(epoch …) reads it as if it were UTC. When the column holds New York wall-clock times, the result is five hours early:

SELECT extract(epoch FROM timestamp '2023-11-14 17:13:20');
-- 1699982000.000000
SELECT extract(epoch FROM timestamp '2023-11-14 17:13:20' AT TIME ZONE 'America/New_York');
-- 1700000000.000000

On a timestamp, AT TIME ZONE says which zone the wall clock belongs to and returns a timestamptz. Store new columns as timestamptz and the question doesn’t come up.

Gotchas

• ::bigint rounds. extract(epoch FROM timestamptz '2023-11-14 22:13:20.6+00')::bigint is 1700000001, a second that hasn’t started yet; with floor() it’s 1700000000.
• ::int fails after 2038. extract(epoch FROM timestamptz '2038-01-19 03:14:08+00')::int stops with ERROR: integer out of range. Use bigint.
• Milliseconds passed as seconds. to_timestamp(1700000000123) is 55840-11-08 22:15:23.000064+00. Divide first: to_timestamp(1700000000123 / 1000.0) is 2023-11-14 22:13:20.123+00.
• now() is when the transaction started, and it doesn’t move until the transaction ends; clock_timestamp() does. After pg_sleep(2) inside one transaction:

BEGIN;
SELECT floor(extract(epoch FROM now())), floor(extract(epoch FROM clock_timestamp()));
-- 1792022016 | 1792022016
SELECT pg_sleep(2);
SELECT floor(extract(epoch FROM now())), floor(extract(epoch FROM clock_timestamp()));
-- 1792022016 | 1792022018
COMMIT;