Unix timestamp in MySQL

SELECT UNIX_TIMESTAMP(); returns the current Unix time in seconds, as an integer. FROM_UNIXTIME() converts back, and both of them read or write the date in the session’s time_zone.

SELECT UNIX_TIMESTAMP();                                  -- 1792022032
SELECT UNIX_TIMESTAMP(NOW(3));                            -- 1792022032.531
SELECT FROM_UNIXTIME(1700000000);                         -- 2023-11-14 22:13:20
SELECT FROM_UNIXTIME(1700000000, '%Y-%m-%dT%H:%i:%sZ');   -- 2023-11-14T22:13:20Z

Those ran on MySQL 8.4.11 at 2026-10-14T23:53:52Z, with time_zone at SYSTEM and the system zone UTC. The tool below starts from that instant and reads 1792022032 as 2026-10-14 23:53:52 UTC. Given a value with fractional seconds, like NOW(3), UNIX_TIMESTAMP returns a decimal with that many digits.

The session time_zone

A Unix timestamp has no zone, but FROM_UNIXTIME returns a DATETIME, which is a wall clock, and UNIX_TIMESTAMP('…') reads its string as one. Both use the session’s time_zone:

SET time_zone = 'America/New_York';
SELECT FROM_UNIXTIME(1700000000);               -- 2023-11-14 17:13:20
SELECT UNIX_TIMESTAMP('2023-11-14 22:13:20');   -- 1700018000

In UTC the same string gave 1700000000, so one query returns different numbers on connections set to different zones. The tool shows the same 1700018000 for 2023-11-14 22:13:20 in America/New_York. Named zones need MySQL’s time zone tables: the mysql:8.4 image used here has them loaded, and other installs load them with mysql_tzinfo_to_sql. An offset like '+00:00' always works.

TIMESTAMP vs DATETIME columns

A TIMESTAMP column stores UTC and converts on the way in and out; a DATETIME keeps the wall clock it was given:

SET time_zone = '+00:00';
CREATE TABLE events (at_ts TIMESTAMP, at_dt DATETIME);
INSERT INTO events VALUES (FROM_UNIXTIME(1700000000), FROM_UNIXTIME(1700000000));
SET time_zone = 'America/New_York';
SELECT at_ts, at_dt FROM events;   -- 2023-11-14 17:13:20 | 2023-11-14 22:13:20

The 2038 limit

TIMESTAMP is a 32-bit count of seconds, so its range ends at 2038-01-19 03:14:07 UTC. In the default strict SQL mode, one second more is an error:

INSERT INTO events (at_ts) VALUES ('2038-01-19 03:14:08');
-- ERROR 1292 (22007): Incorrect datetime value: '2038-01-19 03:14:08' for column 'at_ts' at row 1
INSERT INTO events (at_dt) VALUES ('2038-01-19 03:14:08');
-- Query OK

DATETIME runs to the year 9999. The functions reach further than TIMESTAMP too: on this 64-bit server FROM_UNIXTIME(32536771199) is 3001-01-18 23:59:59, and one second more is NULL.

Milliseconds

FROM_UNIXTIME(1700000000123) is NULL: milliseconds read as seconds land past the year 3001. Divide first. FROM_UNIXTIME(1700000000123 / 1000) is 2023-11-14 22:13:20.1230, with four decimals because MySQL’s division keeps four by default.