Converting epoch number to human readable date in mysql
epoch, mysql
Solution
Your epoch value `1389422614485` seems like having the millisecond precision. So you need to use some mysql mathematical functions along with `from_unixtime()` for generating human readable format.
mysql> select from_unixtime(floor(1389422614485/1000));
+------------------------------------------+
| from_unixtime(floor(1389422614485/1000)) |
+------------------------------------------+
| 2014-01-11 12:13:34 |
+------------------------------------------+
Update July 2020: As of MySQL 8.0, the `floor` function is no longer necessary when working with milliseconds:
mysql> select from_unixtime(1594838230234/1000);
+------------------------------------------+
| from_unixtime(1594838230234/1000) |
+------------------------------------------+
| 2020-07-15 18:37:10.2340 |
+------------------------------------------+
Problem
I have a epoch number say 1389422614485. The datatype for the value storing this value is varchar. I want to convert its value to human readable time. How can we do it? Any example for this conversion?