PostgreSQL EXTRACT(EPOCH)

PostgreSQL has robust timestamp support with proper timezone handling built in. The EXTRACT(EPOCH FROM ...) function converts a timestamp to Unix time (seconds since epoch), while to_timestamp() does the reverse.

Core operations:

-- Get current Unix timestamp (seconds, with fractional part)
SELECT EXTRACT(EPOCH FROM NOW());            -- 1700000000.123456

-- Convert to integer seconds
SELECT FLOOR(EXTRACT(EPOCH FROM NOW()));     -- 1700000000

-- Convert a specific timestamp to epoch
SELECT EXTRACT(EPOCH FROM TIMESTAMP '2024-01-15 09:30:00 UTC');

-- Convert Unix timestamp to PostgreSQL timestamp
SELECT to_timestamp(1700000000);
-- '2023-11-14 22:13:20+00'

-- Arithmetic: timestamp 1 hour from now
SELECT NOW() + INTERVAL '1 hour';

timestamp vs timestamptz:

PostgreSQL has two timestamp types: timestamp (without timezone) and timestamptz (with timezone). Despite common misconception, timestamptz does not store a timezone — it stores the value in UTC and converts on display based on the session's timezone setting.

SET timezone = 'America/New_York';
SELECT '2024-01-15 09:30:00 UTC'::timestamptz;
-- '2024-01-15 04:30:00-05'  (converted to ET)

SET timezone = 'Asia/Tokyo';
SELECT '2024-01-15 09:30:00 UTC'::timestamptz;
-- '2024-01-15 18:30:00+09'  (converted to JST)

Best practice — always use timestamptz: The PostgreSQL documentation itself recommends timestamptz for almost all use cases. Using timestamp (without timezone) stores the literal value without any UTC conversion, which becomes ambiguous when servers or clients are in different timezones.

Common pitfalls:

EXTRACT(EPOCH FROM ...) returns a double precision value, not an integer. This matters when doing comparisons — use FLOOR() or cast to BIGINT for exact integer comparisons. Also, to_timestamp() always returns a timestamptz, so be mindful of timezone conversion when assigning it to a timestamp column.

Millisecond timestamps: PostgreSQL timestamptz has microsecond precision. To get epoch in milliseconds: FLOOR(EXTRACT(EPOCH FROM NOW()) * 1000).