MysqlHow-ToBeginner · 3 min read

How to Use from_unixtime in MySQL: Syntax and Examples

Use the from_unixtime(unix_timestamp) function in MySQL to convert a Unix timestamp (seconds since 1970-01-01) into a readable date and time string. You can also add a format string like from_unixtime(unix_timestamp, format) to customize the output.

📐

Syntax

The from_unixtime function converts a Unix timestamp to a readable date/time string.

unix_timestamp: The number of seconds since January 1, 1970 (Unix epoch).
format (optional): A string to specify the output date/time format, using MySQL date format specifiers.

sql

FROM_UNIXTIME(unix_timestamp)
FROM_UNIXTIME(unix_timestamp, format)

💻

Example

This example shows how to convert a Unix timestamp to a readable date/time and how to format it.

sql

SELECT FROM_UNIXTIME(1609459200) AS readable_datetime,
       FROM_UNIXTIME(1609459200, '%Y-%m-%d %H:%i:%s') AS formatted_datetime;

Output

readable_datetime | formatted_datetime ------------------------|------------------- 2021-01-01 00:00:00 | 2021-01-01 00:00:00

⚠️

Common Pitfalls

Common mistakes include:

Passing a timestamp in milliseconds instead of seconds (MySQL expects seconds).
Using incorrect format specifiers in the format string.
Confusing from_unixtime with unix_timestamp which does the opposite conversion.

sql

/* Wrong: timestamp in milliseconds */
SELECT FROM_UNIXTIME(1609459200000);

/* Right: timestamp in seconds */
SELECT FROM_UNIXTIME(1609459200);

📊

Quick Reference

Function	Description	Example
FROM_UNIXTIME(unix_timestamp)	Converts Unix timestamp to datetime	FROM_UNIXTIME(1609459200) → '2021-01-01 00:00:00'
FROM_UNIXTIME(unix_timestamp, format)	Converts and formats datetime	FROM_UNIXTIME(1609459200, '%Y-%m-%d') → '2021-01-01'

✅

Key Takeaways

FROM_UNIXTIME converts Unix timestamps (seconds) to readable date/time strings.

Use the optional format string to customize the output format.

Always provide timestamps in seconds, not milliseconds.

FROM_UNIXTIME is the reverse of UNIX_TIMESTAMP function.

Check format specifiers carefully to avoid unexpected results.