FROM_UNIXTIME() in MySQL 8: Convert a UNIX timestamp to a date

Understanding UNIX Timestamps

A UNIX timestamp consists of the number of seconds that have elapsed since January 1, 1970, at 00:00:00 Coordinated Universal Time (UTC), not including leap seconds. It’s a simple way to track time, as it’s just a single number, but it’s not very user-friendly for display purposes.

Timestamps are a common way to record dates and times in databases, and UNIX timestamps are particularly widespread. In MySQL, the FROM_UNIXTIME() function provides a convenient way to convert these UNIX timestamps into a human-readable date format. This function can be incredibly useful when presenting data in reports or working with date-related logic in MySQL queries.

The Basics of FROM_UNIXTIME()

The MySQL FROM_UNIXTIME() function allows you to turn these raw numbers into a more understandable datetime format. The syntax for this function is straightforward:

SELECT FROM_UNIXTIME(unix_timestamp[, format]);

Parameters:

• unix_timestamp: is the UNIX timestamp you want to convert.
• format: (optional) is a string that specifies the datetime format. If omitted, the default format is ‘YYYY-MM-DD HH:MM:SS’.

Let’s see how it works with a simple example:

SELECT FROM_UNIXTIME(1617184800);-- Result: 2021-03-31 00:00:00

Customizing Date and Time Formats

The real power of FROM_UNIXTIME() becomes apparent when you customize the datetime format. You can do so using the date and time format specifiers:

• %Y – Year as a numeric, four digits
• %m – Month, numeric (00-12)
• %d – Day of the month, numeric (00-31)
• %H – Hour (00-23)
• %i – Minutes, numeric (00-59)
• %s – Seconds (00-59)
• …

With these specifiers, you can transform the timestamp into any date-time format you require:

SELECT FROM_UNIXTIME(1617184800, '%Y-%m-%d');-- Result: 2021-03-31

Handling Time Zones

One important thing to remember is that FROM_UNIXTIME() assumes the timestamp is in UTC. However, it converts the time to the time zone of the MySQL server. This can lead to confusion if the server’s time zone differs from your local time zone. To address this, you can set the session time zone within your SQL session:

SET time_zone = '+00:00'; SELECT FROM_UNIXTIME(1617184800);

Using FROM_UNIXTIME() in Joins and Where Clauses

The function is also helpful in JOIN and WHERE clauses where you need to compare dates or filter data based on a date range:

SELECT posts.title
FROM posts
JOIN timestamps ON posts.id = timestamps.post_id
WHERE FROM_UNIXTIME(timestamps.created_at) >= '2021-01-01';

Working With NULL and Invalid Data

If the UNIX timestamp is NULL or invalid, FROM_UNIXTIME() returns NULL. It’s important to take this into account to avoid unexpected results:

SELECT FROM_UNIXTIME(NULL); -- Result: NULL
SELECT FROM_UNIXTIME('invalid data'); -- Result: NULL

Performance Tips

While FROM_UNIXTIME() is a powerful function, it can impact query performance if used indiscriminately on large datasets. Here are some tips to optimize its usage:

• Avoid using FROM_UNIXTIME() in the WHERE clause on indexed timestamp columns. Doing so will prevent the use of the index.
• Consider converting your UNIX timestamps to datetime format before inserting them into the database if you’ll need to work with them frequently in a human-readable format.

Conclusion

In this article, we’ve explored how to use the FROM_UNIXTIME() function in MySQL to convert a UNIX timestamp to a human-readable date. With this function, it’s easy to format timestamps for reporting and analysis while handling time zones and ensuring performance is optimized. Properly converting and formatting timestamps can streamline your database interactions and make your applications more robust and user-friendly.