Convert Unix Timestamp in PostgreSQL

PostgreSQL provides built-in functions for Unix timestamp conversion. Use to_timestamp() to convert from epoch and EXTRACT(EPOCH FROM ...) to convert to epoch.

Unix Timestamp to Timestamp

PostgreSQL

-- From seconds (standard Unix timestamp)
SELECT to_timestamp(1704067200);
-- Result: 2024-01-01 00:00:00+00

-- From milliseconds (divide by 1000)
SELECT to_timestamp(1704067200000 / 1000.0);
-- Result: 2024-01-01 00:00:00+00

-- With fractional seconds
SELECT to_timestamp(1704067200.5);
-- Result: 2024-01-01 00:00:00.5+00

-- Cast to date only
SELECT to_timestamp(1704067200)::date;
-- Result: 2024-01-01

Timestamp to Unix Timestamp

PostgreSQL

-- Current timestamp in seconds
SELECT EXTRACT(EPOCH FROM NOW());
-- Result: 1704067200.123456

-- Current timestamp as integer
SELECT FLOOR(EXTRACT(EPOCH FROM NOW()))::bigint;
-- Result: 1704067200

-- From specific timestamp
SELECT EXTRACT(EPOCH FROM TIMESTAMP '2024-01-01 00:00:00 UTC');
-- Result: 1704067200

-- From timestamptz
SELECT EXTRACT(EPOCH FROM TIMESTAMPTZ '2024-01-01 00:00:00+00');
-- Result: 1704067200

-- To milliseconds
SELECT (EXTRACT(EPOCH FROM NOW()) * 1000)::bigint;
-- Result: 1704067200000

Timezone Handling

PostgreSQL

-- to_timestamp returns timestamptz (timezone-aware)
SELECT to_timestamp(1704067200);
-- Result: 2024-01-01 00:00:00+00 (UTC)

-- Display in specific timezone
SELECT to_timestamp(1704067200) AT TIME ZONE 'America/New_York';
-- Result: 2023-12-31 19:00:00

SELECT to_timestamp(1704067200) AT TIME ZONE 'Asia/Tokyo';
-- Result: 2024-01-01 09:00:00

-- Convert between timezones
SELECT 
  TIMESTAMP '2024-01-01 00:00:00' AT TIME ZONE 'UTC' 
    AT TIME ZONE 'America/Los_Angeles';
-- Result: 2023-12-31 16:00:00

-- Set session timezone
SET timezone = 'America/New_York';
SELECT to_timestamp(1704067200);
-- Result: 2023-12-31 19:00:00-05

Formatting

PostgreSQL

-- Various formats using to_char
SELECT to_char(to_timestamp(1704067200), 'YYYY-MM-DD');
-- Result: 2024-01-01

SELECT to_char(to_timestamp(1704067200), 'Month DD, YYYY');
-- Result: January   01, 2024

SELECT to_char(to_timestamp(1704067200), 'FMMonth DD, YYYY');
-- Result: January 1, 2024 (FM removes padding)

SELECT to_char(to_timestamp(1704067200), 'YYYY-MM-DD HH24:MI:SS');
-- Result: 2024-01-01 00:00:00

SELECT to_char(to_timestamp(1704067200), 'Day, DD Mon YYYY HH12:MI AM');
-- Result: Monday   , 01 Jan 2024 12:00 AM

-- ISO 8601 format
SELECT to_char(to_timestamp(1704067200), 'YYYY-MM-DD"T"HH24:MI:SS"Z"');
-- Result: 2024-01-01T00:00:00Z

Practical Examples

PostgreSQL

-- Store Unix timestamp, query as date
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    name TEXT,
    created_at BIGINT  -- Unix timestamp in seconds
);

-- Insert with current timestamp
INSERT INTO events (name, created_at) 
VALUES ('Event', EXTRACT(EPOCH FROM NOW())::bigint);

-- Query with human-readable date
SELECT 
    name, 
    to_timestamp(created_at) AS created_date
FROM events;

-- Filter by date range using epoch
SELECT * FROM events 
WHERE created_at >= EXTRACT(EPOCH FROM '2024-01-01'::timestamptz)
  AND created_at < EXTRACT(EPOCH FROM '2024-02-01'::timestamptz);

-- Better: Use timestamptz column directly
CREATE TABLE events_better (
    id SERIAL PRIMARY KEY,
    name TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

Common Pitfalls

TIMESTAMP vs TIMESTAMPTZ

TIMESTAMP ignores timezone context. Always use TIMESTAMPTZ when working with Unix timestamps.

-- ❌ Wrong - TIMESTAMP loses timezone info
SELECT EXTRACT(EPOCH FROM TIMESTAMP '2024-01-01 00:00:00');
-- May give unexpected results depending on server timezone

-- ✅ Correct - TIMESTAMPTZ is explicit
SELECT EXTRACT(EPOCH FROM TIMESTAMPTZ '2024-01-01 00:00:00 UTC');
-- Always 1704067200

Milliseconds vs Seconds

PostgreSQL expects seconds. Passing milliseconds gives dates far in the future.

-- ❌ Wrong - milliseconds treated as seconds
SELECT to_timestamp(1704067200000);
-- Result: 55963-09-25 (way off!)

-- ✅ Correct - divide by 1000
SELECT to_timestamp(1704067200000 / 1000.0);
-- Result: 2024-01-01 00:00:00+00

Integer Division

When dividing milliseconds, use 1000.0 (float) to preserve precision. Integer division truncates fractional seconds.

Edge Cases

PostgreSQL

-- Negative timestamps (before 1970)
SELECT to_timestamp(-86400);
-- Result: 1969-12-31 00:00:00+00

-- Very old dates
SELECT to_timestamp(-2208988800);
-- Result: 1900-01-01 00:00:00+00

-- Far future
SELECT to_timestamp(4102444800);
-- Result: 2100-01-01 00:00:00+00

-- Sub-second precision
SELECT to_timestamp(1704067200.123456);
-- Result: 2024-01-01 00:00:00.123456+00

-- Epoch (the beginning)
SELECT to_timestamp(0);
-- Result: 1970-01-01 00:00:00+00

Frequently Asked Questions

What function converts Unix timestamps to dates in PostgreSQL?

Use to_timestamp(unix_seconds) to convert a Unix timestamp to a timestamp with time zone. For the reverse, use EXTRACT(EPOCH FROM timestamp).

Does PostgreSQL use seconds or milliseconds for epoch?

PostgreSQL uses seconds with decimal precision. If you have milliseconds, divide by 1000.0 before passing to to_timestamp().

What is the difference between TIMESTAMP and TIMESTAMPTZ?

TIMESTAMP stores date/time without timezone. TIMESTAMPTZ stores date/time with timezone awareness. Always use TIMESTAMPTZ for Unix timestamp conversions.

How do I get the current Unix timestamp in PostgreSQL?

Use EXTRACT(EPOCH FROM NOW()) or EXTRACT(EPOCH FROM CURRENT_TIMESTAMP). Both return the current time as a Unix timestamp.