SQL TO_TIMESTAMP: Parsing Strings and Building Timestamps from Unix Epoch

PostgreSQL's TO_TIMESTAMP does two very different jobs under one name: parse a string by a format template, and build a moment in time from a number of Unix epoch seconds. Both forms return timestamptz, and that single fact is the source of most of the time-zone confusion around the function.

Parsing a string by template

The first mode takes a string and a format template. The template tells the parser how to read the input: YYYY is the year, MM the month, DD the day, HH24 the 24-hour clock, MI minutes, SS seconds.

SELECT TO_TIMESTAMP('2024-03-15 14:30', 'YYYY-MM-DD HH24:MI') AS ts;
-- 2024-03-15 14:30:00+00 (with server timezone UTC)

The classic use is ingesting messy data where the date arrives as a string in a non-standard shape:

INSERT INTO users (id, email, name, country, created_at)
VALUES (1, 'kate@example.com', 'Kate', 'DE',
        TO_TIMESTAMP('15/03/2024 09:05', 'DD/MM/YYYY HH24:MI'));

Handy properties of the template mode:

• TO_TIMESTAMP is more forgiving than a ::timestamptz cast: it usually tolerates extra whitespace.
• The result is always timestamptz, anchored to the session's current TimeZone.
• For a bare date with no time there is a sibling TO_DATE with the same template language.

Building time from the Unix epoch

The second mode takes a single number: the seconds elapsed since 1970-01-01 00:00:00 UTC. A fractional part supplies sub-second precision.

SELECT TO_TIMESTAMP(1710513000)      AS from_epoch,
       TO_TIMESTAMP(1710513000.5)    AS with_millis;
-- 2024-03-15 14:30:00+00 | 2024-03-15 14:30:00.5+00

This is a lifesaver when time is stored as a bigint (the usual shape of backend logs and events):

SELECT id, user_id, amount,
       TO_TIMESTAMP(created_at) AS created_ts
FROM orders
WHERE created_at >= 1704067200;  -- since 2024-01-01 UTC

The key point: the number is always interpreted as UTC seconds. The instant itself is absolute; only its textual rendering depends on the session TimeZone.

Time zones: the big gotcha

Both forms return timestamptz. That means an absolute UTC instant is stored internally and shown in your session's zone on output. The same query yields different text under a different SET TIME ZONE.

SET TIME ZONE 'UTC';
SELECT TO_TIMESTAMP('2024-03-15 14:30', 'YYYY-MM-DD HH24:MI');
-- 2024-03-15 14:30:00+00

SET TIME ZONE 'America/New_York';
SELECT TO_TIMESTAMP('2024-03-15 14:30', 'YYYY-MM-DD HH24:MI');
-- 2024-03-15 14:30:00-04

Gotcha: when parsing a string the template carries no zone, so the year-month-day-hour digits are read as the session's local time, not as UTC. If the string is really in UTC, line it up explicitly:

SELECT TO_TIMESTAMP('2024-03-15 14:30', 'YYYY-MM-DD HH24:MI')
         AT TIME ZONE 'UTC' AS naive_utc;

Round-tripping with EXTRACT(EPOCH)

The round trip closes with EXTRACT(EPOCH FROM ...), which returns the Unix epoch seconds from a timestamptz. The pair TO_TIMESTAMP and EXTRACT(EPOCH ...) is reversible.

SELECT EXTRACT(EPOCH FROM TIMESTAMPTZ '2024-03-15 14:30:00+00') AS epoch;
-- 1710513000

SELECT TO_TIMESTAMP(EXTRACT(EPOCH FROM created_at)) = created_at AS same
FROM orders
LIMIT 1;
-- t

This is convenient for deltas and for storing time in numeric APIs. An order's age in hours is one subtraction of epochs:

SELECT id,
       (EXTRACT(EPOCH FROM now()) - EXTRACT(EPOCH FROM created_at)) / 3600.0
         AS age_hours
FROM orders
WHERE status = 'paid';

MySQL and ClickHouse differences

The name TO_TIMESTAMP belongs to the PostgreSQL and Oracle world; other engines spell it differently.

• MySQL parses a string with STR_TO_DATE(str, format) using its own format codes (%Y, %m, %d, %H, %i). It builds a moment from the epoch with FROM_UNIXTIME(seconds) and goes back with UNIX_TIMESTAMP(ts).

SELECT STR_TO_DATE('2024-03-15 14:30', '%Y-%m-%d %H:%i') AS parsed,
       FROM_UNIXTIME(1710513000)                          AS from_epoch;

• ClickHouse uses parseDateTime (MySQL-like syntax) and fromUnixTimestamp(seconds), with toUnixTimestamp(ts) for the reverse.

SELECT fromUnixTimestamp(1710513000) AS from_epoch,
       toUnixTimestamp(now())        AS to_epoch;

Keep the split in mind: a single number of seconds is an absolute UTC instant, while a templated string always drags along the question of which zone it is in. Hold the two modes apart and TO_TIMESTAMP stops surprising you.