Convert Unix timestamps in MySQL: FROM_UNIXTIME, UNIX_TIMESTAMP, time zones
MySQL ships two near-identical date types, FROM_UNIXTIME, and UNIX_TIMESTAMP, plus a time zone story that surprises everyone exactly once. Once you know the rules they are predictable.
FROM_UNIXTIME and UNIX_TIMESTAMP cover the round-trip
FROM_UNIXTIME(seconds) returns a DATETIME rendered in the session time zone. UNIX_TIMESTAMP(datetime) does the inverse. Both have been there since MySQL 4.
```sql SELECT FROM_UNIXTIME(1763500800); /* 2025-11-19 01:00:00 in Europe/Madrid, 2025-11-19 00:00:00 in UTC */
SELECT UNIX_TIMESTAMP('2025-11-19 00:00:00'); /* 1763500800 if session zone is UTC */ ```
The session time zone is set with SET time_zone = '+00:00' per connection, or globally in my.cnf. If you do not set it, MySQL defaults to the system zone, which is whatever the server OS reports. That is a recipe for production pain.
TIMESTAMP vs DATETIME are not the same
DATETIME stores a wall-clock value with no zone attached. The string in your SELECT is the string MySQL gives back, regardless of session zone.
TIMESTAMP is stored as UTC internally and converted to and from the session zone on read and write. It is what you want for an updated_at column that should always reflect a real instant.
The catch: TIMESTAMP has a hard ceiling at 2038-01-19 03:14:07 UTC because it uses 32-bit signed seconds since 1970. Any timestamp past that overflows. DATETIME goes to 9999. For long-lived application data, prefer DATETIME and store in UTC by convention; for audit columns where you trust MySQL to manage the conversion, TIMESTAMP is fine until 2038 starts looming.
Time zone tables are not loaded by default
Functions like CONVERT_TZ('2025-01-01 00:00:00', 'UTC', 'Europe/Madrid') only work if the time zone tables are populated. On a fresh install they are empty, and every call returns NULL silently.
To populate them on Linux:
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysqlAfter that, named zones like 'America/New_York' and 'Europe/Madrid' work. Without it you are limited to numeric offsets like '+02:00', which do not handle DST. If your code calls CONVERT_TZ and gets NULLs in production but works locally, this is the reason.
Milliseconds and microseconds
MySQL 5.6 added fractional-second support up to microseconds. DATETIME(3) stores milliseconds, DATETIME(6) microseconds. For incoming JavaScript epoch in milliseconds:
```sql /* JS Date.now() to a DATETIME(3) */ SELECT FROM_UNIXTIME(1763500837.421);
/* Round-trip to milli precision */ SELECT UNIX_TIMESTAMP(NOW(3)) * 1000; ```
If you need sub-second timestamps, declare the column as DATETIME(3) from day one. ALTER TABLE on a populated table to add precision is online in 8.0+ but still locks reads briefly on large tables.
Working example
sql/* Make sure session is in UTC */
SET time_zone = '+00:00';
/* Epoch seconds to readable */
SELECT FROM_UNIXTIME(1763500800);
/* 2025-11-19 00:00:00 */
/* Now to epoch millis (JS Date.now equivalent) */
SELECT CAST(UNIX_TIMESTAMP(NOW(3)) * 1000 AS UNSIGNED);
/* Filter rows from the last 24 hours using Unix epoch */
SELECT id
FROM events
WHERE created_at >= FROM_UNIXTIME(UNIX_TIMESTAMP() - 86400);Just need the result?
When you just want to read what 1763500837 means without firing up a MySQL client, paste the epoch into the browser-based timestamp converter. It shows seconds, milliseconds, ISO 8601, UTC and your local zone all at once, with the relative offset from now, in zero round-trips.
Open Unix Timestamp Converter →Frequently asked questions
Will my TIMESTAMP columns break in 2038?
Yes if they are 32-bit MySQL TIMESTAMP. The fix is to migrate to DATETIME (no 2038 ceiling) and store UTC by convention, or wait for whatever 64-bit replacement Oracle ships before then. Plan migrations now for data that lives past 2030.
Why does CONVERT_TZ return NULL on production but not on my laptop?
Production has not loaded the time zone tables. Run mysql_tzinfo_to_sql against the mysql database once. Until you do, named zones return NULL and only numeric offsets work, which is silent and easy to miss.
Should I use UTC_TIMESTAMP() or NOW()?
UTC_TIMESTAMP() always returns UTC regardless of session zone. NOW() returns the current time in the session zone. For application code use UTC_TIMESTAMP() and convert at display time; for ad hoc queries either is fine if you know the session zone.