PostgreSQL Timestamp Converter
PostgreSQL has excellent built-in support for timestamps and timezones. The two primary functions for Unix timestamp conversion in PostgreSQL are TO_TIMESTAMP(), which converts a Unix integer to a timestamptz (timestamp with timezone) value, and EXTRACT(EPOCH FROM ...), which extracts the Unix timestamp from any datetime expression.
PostgreSQL's timestamptz type stores timestamps in UTC internally and automatically converts to the session timezone for display. This is different from timestamp without time zone (timestamp), which stores the literal datetime string without any timezone information. For most applications, timestamptz is the correct choice for timestamp columns.
PostgreSQL Unix timestamp functions
Unix integer to timestamptz:
SELECT TO_TIMESTAMP(1700000000);
→ 2023-11-14 22:13:20+00
timestamptz to Unix integer:
SELECT EXTRACT(EPOCH FROM NOW())::INT;
Specific datetime to Unix:
SELECT EXTRACT(EPOCH FROM TIMESTAMPTZ '2026-04-17 10:35:00 UTC')::INT;
Display in a specific timezone:
SELECT TO_TIMESTAMP(1700000000) AT TIME ZONE 'Europe/Oslo';
Records from the last 7 days:
SELECT * FROM events WHERE ts > EXTRACT(EPOCH FROM NOW() - INTERVAL '7 days')::INT;
Millisecond precision:
SELECT (EXTRACT(EPOCH FROM NOW()) * 1000)::BIGINT;