How do I convert epoch timestamps to human-readable dates in MySQL?

Converting Epoch Timestamps to Human-Readable Dates in MySQL

In MySQL, epoch timestamps represent the number of seconds or milliseconds since a specific point in time, known as the epoch (January 1, 1970, 00:00:00 UTC). Converting these numerical values into human-readable dates can be done using the from_unixtime() function.

Step 1: Verify Timestamp Precision

The precision of the epoch timestamp is crucial for the conversion process. Some timestamps may have millisecond precision, while others may only record seconds. This precision affects the subsequent steps.

Step 2: Handle Millisecond Precision

For timestamps with millisecond precision, we need to divide by 1000 before using from_unixtime() to account for the additional decimal places:

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

Update: As of MySQL 8.0, the floor function is no longer required when working with milliseconds:

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

Step 3: Handle Second Precision

If the timestamp only has second precision, we can directly use from_unixtime():

<code class="mysql">mysql> select from_unixtime(1389422614);
+---------------------------------------+
| from_unixtime(1389422614)           |
+---------------------------------------+
| 2014-01-11 12:13:34                  |
+---------------------------------------+</code>

By following these steps, you can effortlessly convert epoch timestamps into human-readable dates in MySQL, allowing you to display chronological data in a comprehensible and user-friendly format.

The above is the detailed content of How do I convert epoch timestamps to human-readable dates in MySQL?. For more information, please follow other related articles on the PHP Chinese website!