Conversión de número de época en fecha legible por humanos en MySQL

En el ámbito de la gestión de bases de datos, a menudo es necesario convertir números de época en fechas legibles por humanos. Los números de época, que representan un punto en el tiempo desde una época definida, se usan comúnmente para almacenar datos temporales en sistemas de bases de datos como MySQL.

Considere el escenario hipotético en el que tiene un número de época, como 1389422614485, que representa un momento específico en el tiempo. El tipo de datos de este valor es varchar y desea transformarlo a un formato de fecha comprensible.

Solución detallada

Para realizar esta conversión en MySQL, deberá necesita aprovechar una combinación de funciones matemáticas y la función from_unixtime():

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

Explicación

En este ejemplo, la función from_unixtime() se emplea para convertir el número de época, que normalmente representa milisegundos desde la época, en una cadena de fecha. Dado que nuestro número de época parece tener una precisión de milisegundos, usamos Floor(1389422614485/1000) para convertirlo a segundos desde la época, que es la entrada que espera from_unixtime().

Salida

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

La salida muestra la fecha legible por humanos correspondiente al número de época dado, con el formato 'AAAA-MM-DD HH:MM:SS'.

Actualización para MySQL 8.0 y superiores

A partir de la versión 8.0 de MySQL, la función Floor ya no es necesaria cuando se trabaja con milisegundos en la función from_unixtime(). Puede proporcionar directamente el número de época en milisegundos como argumento:

mysql> select from_unixtime(1594838230234/1000);

Esto producirá el siguiente resultado:

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

Este refinado La versión de la consulta ahora también admite precisión de nanosegundos.