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 (with the session TimeZone set to UTC) -- 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 floor(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 floor(extract(epoch from now()))::bigint (a plain ::bigint cast rounds, so it can return the next second). 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.