Converting Epoch Number to Human Readable Date in MySQL

In the realm of database management, it's often necessary to convert epoch numbers into human-readable dates. Epoch numbers, which represent a point in time since a defined epoch, are commonly used to store temporal data in database systems like MySQL.

Consider the hypothetical scenario where you have an epoch number, such as 1389422614485, which represents a specific point in time. The datatype of this value is varchar, and you desire to transform it into a comprehensible date format.

Detailed Solution

To accomplish this conversion in MySQL, you'll need to leverage a combination of mathematical functions and the from_unixtime() function:

mysql> select from_unixtime(floor(1389422614485/1000));

Explanation

In this example, the from_unixtime() function is employed to convert the epoch number, which typically represents milliseconds since the epoch, into a date string. Since our epoch number appears to have millisecond precision, we use floor(1389422614485/1000) to convert it to seconds since the epoch, which is the input that from_unixtime() expects.

Output

 ------------------------------------------ 
| from_unixtime(floor(1389422614485/1000)) |
 ------------------------------------------ 
| 2014-01-11 12:13:34                      |
 ------------------------------------------ 

The output displays the human-readable date corresponding to the given epoch number, with the format being 'YYYY-MM-DD HH:MM:SS'.

Update for MySQL 8.0 and Above

As of MySQL version 8.0, the floor function is no longer necessary when working with milliseconds in the from_unixtime() function. You can directly provide the epoch number in milliseconds as the argument:

mysql> select from_unixtime(1594838230234/1000);

This will yield the following output:

 ------------------------------------------ 
| from_unixtime(1594838230234/1000)        |
 ------------------------------------------ 
| 2020-07-15 18:37:10.2340                 |
 ------------------------------------------ 

This refined version of the query now supports nanosecond precision as well.