Home > Database > Mysql Tutorial > How to Convert Epoch Numbers with Millisecond Precision to Human-Readable Dates in MySQL?

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

DDD
Release: 2024-10-30 03:01:28
Original
1011 people have browsed it

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>
Copy after login

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>
Copy after login

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>
Copy after login

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!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template