Unix timestamps in MySQL
UNIX_TIMESTAMP and FROM_UNIXTIME convert both ways, but both read and write datetimes in the session time zone.
Get the current Unix timestamp
SELECT UNIX_TIMESTAMP(); -- seconds
SELECT ROUND(UNIX_TIMESTAMP(NOW(3)) * 1000); -- milliseconds
Convert a Unix timestamp to a date
SET time_zone = '+00:00';
SELECT FROM_UNIXTIME(1700000000); -- 2023-11-14 22:13:20
SELECT FROM_UNIXTIME(1700000000123 / 1000); -- 2023-11-14 22:13:20.1230
Convert a date to a Unix timestamp
SET time_zone = '+00:00';
SELECT UNIX_TIMESTAMP('2026-11-14 10:00:00'); -- 1794650400
Timezones
-- Named zones need the time zone tables loaded (mysql_tzinfo_to_sql)
SET time_zone = '+00:00';
SELECT CONVERT_TZ(FROM_UNIXTIME(1700000000), '+00:00', 'Asia/Kolkata');
Common mistakes
- Both functions use the session
time_zone. The same query gives different answers on servers with different settings. Settime_zone = '+00:00'in your connection. TIMESTAMPcolumns end at 2038-01-19 03:14:07 UTC. UseDATETIMEor aBIGINTfor later dates. See Year 2038 Year 2038 checker Unix time 2 billion.- On 64-bit platforms,
FROM_UNIXTIMEaccepts values up to 32536771199.999999 (3001-01-18 UTC). Larger values return 0. - Named zones in
CONVERT_TZreturn NULL until the zone tables are loaded.
Complete example
SET time_zone = '+00:00';
SELECT
UNIX_TIMESTAMP() AS now_s,
FROM_UNIXTIME(1700000000) AS utc,
UNIX_TIMESTAMP(FROM_UNIXTIME(1700000000)) AS back;
Check any value with the epoch converter, or convert a whole column with the batch converter. All examples use 1700000000 (2023-11-14 22:13:20 UTC) and 1794650400 (2026-11-14 10:00:00 UTC).