Convert Unix timestamps in PostgreSQL: to_timestamp, epoch, time zones
PostgreSQL has the best date/time story of any major database, with one persistent gotcha: epoch arithmetic ignores time zones, but display does not. Knowing which side you are on stops every off-by-an-hour bug.
to_timestamp turns epoch seconds into timestamptz
to_timestamp(double precision) takes Unix epoch seconds and returns a timestamptz (timestamp with time zone). The Postgres docs phrase it as "seconds since 1970-01-01 00:00:00 UTC", and that is exactly what it stores.
SELECT to_timestamp(1763500800);
/* 2025-11-19 00:00:00+00 */If your epoch is in milliseconds (every JavaScript codebase ever) divide by 1000 first. If it is in microseconds (some Cassandra, BigQuery exports) divide by 1,000,000. Mixing them silently puts you in the year 56,000 or the year 19,701 and the symptom is "the chart is empty".
EXTRACT(EPOCH FROM ...) goes the other way
To get a Unix timestamp out of a timestamptz use EXTRACT(EPOCH FROM ...). It returns a double precision count of seconds, fractional included.
```sql SELECT EXTRACT(EPOCH FROM NOW()); /* 1763500837.421 */
SELECT (EXTRACT(EPOCH FROM NOW()) * 1000)::bigint; /* 1763500837421 (millis, like JS Date.now) */ ```
If you store timestamp (without time zone) instead of timestamptz, EXTRACT EPOCH treats it as if it were UTC, regardless of the connection's TimeZone setting. That is a frequent source of confusion: the column type wins over the session setting.
Always use timestamptz, almost never timestamp
timestamp without time zone is what people pick by default and what they regret six months later. It stores a wall-clock time with no zone attached, so the same value means different instants in different sessions.
timestamptz (a.k.a. timestamp with time zone) stores an instant in UTC and renders it in the session's TimeZone on display. Two services in different regions read the same row and both see correct local time. That is what you want.
The migration from timestamp to timestamptz is a real schema change and you have to specify how to interpret the existing values. Pick AT TIME ZONE 'UTC' if your old data was already UTC; pick the actual originating zone otherwise.
Beware of milliseconds in extensions
Several popular extensions and ORMs store epoch in milliseconds inside a bigint column instead of using timestamptz. That works, it just bypasses every nice tool Postgres gives you.
/* Bigint epoch millis to a real timestamptz */
SELECT to_timestamp(created_at_ms / 1000.0) AS created_at
FROM events;If you control the schema, store as timestamptz and never look at integer epochs again. If you are stuck with the millis column, build a view that exposes a timestamptz derived column and write the rest of the app against that view.
Working example
sql/* Epoch seconds to timestamptz */
SELECT to_timestamp(1763500800);
/* 2025-11-19 00:00:00+00 */
/* Epoch millis (JavaScript style) */
SELECT to_timestamp(1763500837421 / 1000.0);
/* Now to epoch millis */
SELECT (EXTRACT(EPOCH FROM NOW()) * 1000)::bigint;
/* Filter rows from the last hour using a Unix epoch input */
SELECT id
FROM events
WHERE created_at >= to_timestamp(1763497237);Just need the result?
When you just want to translate a Unix epoch you grabbed from a log line into a human-readable date, opening psql is overkill. Paste the number into the browser-based timestamp converter and see seconds, milliseconds, ISO 8601, UTC, and your local time at once, in zero round-trips.
Open Unix Timestamp Converter →Frequently asked questions
Should I store TIMESTAMP or TIMESTAMPTZ?
TIMESTAMPTZ. It stores an instant in UTC and renders in the session zone. Plain TIMESTAMP stores a wall-clock string with no zone; the same row means different instants to different clients, which is almost never what you want.
Why does to_timestamp give me a value an hour off?
Either you passed milliseconds without dividing by 1000, or your session TimeZone is not UTC and you are reading a timestamp column treated as local. Set TimeZone explicitly or use AT TIME ZONE 'UTC' in the projection.
How do I convert ISO 8601 strings into timestamptz?
Cast directly: '2025-11-19T00:00:00Z'::timestamptz. PostgreSQL parses RFC 3339 / ISO 8601 inputs natively, including the trailing Z and offsets like +02:00. Invalid strings raise a parse error at cast time.