Epoch Converter in SQL
Convert Unix timestamps to dates and back in SQL: the idiomatic code, the gotchas, and a live converter.
How do you convert a Unix timestamp to a date in SQL?
In Postgres, SELECT to_timestamp(1777010400) returns a timestamptz, and extract(epoch from ts) goes back. In MySQL, use FROM_UNIXTIME(1777010400) and UNIX_TIMESTAMP(dt). The gotcha is that FROM_UNIXTIME renders in the session timezone, so two clients can see different wall-clock strings for the same stored number.
-- Postgres: epoch seconds to timestamptz (UTC instant) SELECT to_timestamp(1777010400); -- 2026-04-24 06:00:00+00 -- MySQL: epoch seconds to DATETIME (session time zone applies!) SELECT FROM_UNIXTIME(1777010400); -- Force UTC output regardless of session zone: SELECT CONVERT_TZ(FROM_UNIXTIME(1777010400), @@session.time_zone, '+00:00');
-- Postgres: timestamptz to epoch seconds
SELECT extract(epoch FROM timestamptz '2026-04-24 06:00:00+00')::bigint;
-- 1777010400
-- MySQL: DATETIME to epoch seconds (string read in session time zone)
SELECT UNIX_TIMESTAMP('2026-04-24 06:00:00');Postgres and MySQL disagree about who owns the timezone
Postgres's to_timestamp returns a timestamptz, which stores a pure UTC instant and converts to the client's TimeZone setting only at display time, so the stored value is unambiguous. MySQL's FROM_UNIXTIME instead bakes the session time_zone into the result before you ever see it, and UNIX_TIMESTAMP reads its string argument in that same session zone. Two connections with different zones will round-trip the same epoch into different DATETIME strings, which is how a nightly report and an application query end up disagreeing by exactly five hours. Pin the session with SET time_zone = '+00:00' at connection setup, or wrap conversions in CONVERT_TZ as shown in the snippet.
Column types, precision, and the 2038 cliff
MySQL's TIMESTAMP column is a 32-bit value that ends at 2038-01-19 03:14:07 UTC; MySQL 8.0.28 raised the ceiling only for some operations, so schemas that must outlive 2038 should store DATETIME plus an explicit UTC convention, or a BIGINT of epoch seconds. Postgres timestamptz has microsecond precision and a range measured in hundreds of thousands of years, so the cliff does not apply. One precision note for both engines: extract(epoch from ...) in Postgres returns a numeric with fractional seconds, so cast to bigint when a consumer expects a whole number, and remember that a JavaScript client is probably sending you milliseconds that need dividing by 1000 before comparison.
Frequently asked
- Why does FROM_UNIXTIME return a different time than I expect?
- FROM_UNIXTIME converts to the MySQL session time_zone, which often defaults to the server's system zone rather than UTC. Run SET time_zone = '+00:00' on the connection, or wrap the call in CONVERT_TZ(..., @@session.time_zone, '+00:00') to get stable UTC output.
- How do I get the current Unix timestamp in SQL?
- Postgres: SELECT extract(epoch from now())::bigint. MySQL: SELECT UNIX_TIMESTAMP(). Both return epoch seconds. If a downstream JavaScript consumer needs milliseconds, multiply by 1000 in the query or in application code, but do it in exactly one place.
- Is MySQL TIMESTAMP affected by the 2038 problem?
- Yes. The TIMESTAMP column type tops out at 2038-01-19 03:14:07 UTC because it is stored as 32-bit epoch seconds. For dates beyond that, use DATETIME with an agreed UTC convention or a BIGINT of epoch seconds. Postgres timestamptz has no comparable limit.
More in this series
// The subscription desk
The Inference Report
The weekly briefing for AI engineers: model releases, pricing moves, benchmarks, and the news that changes what you should build with and what it costs. Every Tuesday, 5-minute read. No fluff.
Join AI engineers who stopped overpaying for tokens. Unsubscribe anytime.