Converting Epoch Timestamps to Human-Readable Dates in MySQL

In MySQL, an epoch timestamp is a numeric representation of a date and time. It is a count of milliseconds since the Unix epoch, which is January 1, 1970, at 00:00:00 UTC.

To convert an epoch timestamp to a human-readable date, you can use the from_unixtime() function. This function takes an epoch timestamp as its argument and returns a string containing the date and time in a specified format.

The following example shows how to convert an epoch timestamp of 1389422614485 to a human-readable date:

SELECT from_unixtime(1389422614485);

This will return the following string:

2014-01-11 12:13:34

Note that the epoch timestamp in the example has a millisecond precision. For epoch timestamps that represent seconds, you can use the following syntax:

SELECT from_unixtime(1389422614);

This will return the following string:

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:

SELECT from_unixtime(1594838230234/1000);

This will return:

2020-07-15 18:37:10.2340