How to Convert Epoch Numbers with Millisecond Precision to Human-Readable Dates in MySQL?

Convert Epoch Number to Human-Readable Date in MySQL

Problem:

Given an Epoch number (e.g., 1389422614485) stored as a VARCHAR datatype, how can it be efficiently converted into a human-readable date and time?

Solution:

In MySQL, the following steps can be used to convert an Epoch number with millisecond precision to a human-readable format:

1. Use the from_unixtime() function to convert the Epoch number to a UNIX timestamp.
2. Divide the Epoch number by 1000 to account for the milliseconds. This step may not be necessary in MySQL versions after 8.0.
3. For older versions of MySQL, use the floor() function to round down the result to the nearest second.

Putting it all together, the following SQL query can be used:

<code class="sql">SELECT from_unixtime(floor(epoch_number / 1000));</code>

Example:

Consider the Epoch number 1389422614485:

<code class="sql">mysql> SELECT from_unixtime(floor(1389422614485 / 1000));
+------------------------------------------+
| from_unixtime(floor(1389422614485 / 1000)) |
+------------------------------------------+
| 2014-01-11 12:13:34                      |
+------------------------------------------+</code>

In MySQL 8.0 and later, the floor() function is not necessary:

<code class="sql">mysql> SELECT from_unixtime(1389422614485 / 1000);
+------------------------------------------+
| from_unixtime(1594838230234 / 1000)        |
+------------------------------------------+
| 2020-07-15 18:37:10.2340                 |
+------------------------------------------+</code>

The above is the detailed content of How to Convert Epoch Numbers with Millisecond Precision to Human-Readable Dates in MySQL?. For more information, please follow other related articles on the PHP Chinese website!