55

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?

user3675548
  • 575
  • 1
  • 4
  • 5
  • It should be noted, unix epoch time is a 32-bit integer as of now and can't be greater than 2147483647. It also makes more sense to store it as an INT. – Devon Bessemer Jun 02 '14 at 12:54

4 Answers4

105

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                 |
+------------------------------------------+
lucasvw
  • 1,345
  • 17
  • 36
Abhik Chakraborty
  • 44,654
  • 6
  • 52
  • 63
19

Take a look at from-unixtime

mysql> SELECT FROM_UNIXTIME(1196440219);
       -> '2007-11-30 10:30:19'
cristian
  • 8,676
  • 3
  • 38
  • 44
11

You can use from_unixtime() as follows:

SELECT from_unixtime(1388618430);

which returns 2014-01-02 00:20:30

Olli
  • 1,708
  • 10
  • 21
  • @user3675548: what mysql version are you using? and what value? – Olli Jun 08 '15 at 11:58
  • 3
    If you're using a unix epoch specified in milliseconds (instead of seconds) FROM_UNIXTIME() of this value will return NULL. If this is the case, use FLOOR() of your value divided by 1000, as shown in the other answers – Aaron Jan 05 '17 at 03:12
  • I got `2014-01-01 23:20:30` – Syed Ali Oct 15 '18 at 19:08
  • @sttaq the reason for this might be that you have another timezone configured. unfortunately this function returns values depending on that. – Olli Oct 16 '18 at 07:34
  • Thanks, yup I realized that later that mysql converts the epoch time to local time zone. – Syed Ali Oct 16 '18 at 14:05
3

This wil work for both +positive and -negative epoch, in-case for old birth dates, and also if you want to specify date format

select
date_format(DATE_ADD(from_unixtime(0), interval '1389422614485'/1000 second), '%Y-%m-%d %H:%i:%s') as my_new_date;
Omari Victor Omosa
  • 2,814
  • 2
  • 24
  • 46