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?
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'