MySQL FROM_UNIXTIME — Converter and Guide
FROM_UNIXTIME() is MySQL's built-in function for converting a Unix timestamp integer to a human-readable datetime. It's the most common function used when displaying stored timestamps to users, formatting log entries, or joining timestamp-based data with date-based conditions.
The function accepts an optional format string (using the same specifiers as DATE_FORMAT()), letting you control the output format directly in SQL without post-processing in application code. Without a format string, it returns a DATETIME value in the format YYYY-MM-DD HH:MM:SS.
FROM_UNIXTIME syntax
FROM_UNIXTIME(unix_timestamp)
FROM_UNIXTIME(unix_timestamp, format_string)
FROM_UNIXTIME examples
Basic conversion:
SELECT FROM_UNIXTIME(1700000000);
→ '2023-11-14 22:13:20'
Date only:
SELECT FROM_UNIXTIME(1700000000, '%Y-%m-%d');
→ '2023-11-14'
Custom format:
SELECT FROM_UNIXTIME(1700000000, '%W, %M %e, %Y');
→ 'Tuesday, November 14, 2023'
In a WHERE clause:
SELECT * FROM orders WHERE FROM_UNIXTIME(created_at) > '2026-01-01';
Performance tip — convert the constant, not the column:
SELECT * FROM orders WHERE created_at > UNIX_TIMESTAMP('2026-01-01');
This allows MySQL to use an index on created_at; the previous version cannot.