MySQL select formatted date from millisecond field

date, mysql, sql, time

Solution

Try using the `FROM_UNIXTIME` function like this as given in the manual

SELECT FROM_UNIXTIME(1196440219);
 -> '2007-11-30 10:30:19'

You could also use formatting like this

mysql> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),
    ->                      '%Y %D %M %h:%i:%s %x');
    -> '2007 30th November 10:30:59 2007'

Problem

I have a column in a MySQL database that contains a date as milliseconds (epoch). I want to build an SQL query that formats the date as something human readable (day, month, year, hours, minutes, seconds in any format and time zone). Is there an SQL (or MySQL-specific) function to do this?

Original source