Unix timestamps in PostgreSQL
Store times in timestamptz. to_timestamp turns Unix seconds into one, and EXTRACT(EPOCH FROM ...) turns it back.
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 ZONEconverts in opposite directions fortimestamptzandtimestampinputs. Check the type of what you pass.- Since PostgreSQL 14,
EXTRACTreturnsnumeric. Cast tobigintfor 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).