epoch to date on mysql

Question

I've tried using

FROM_UNIXTIME(`date`)

and got a yyyy/mm/dd hour

how can I reverse it, that is have it as

hh:mm:secs mm/dd/yyyy

Thanks

Answer 1

Add specifiers to FROM_UNIXTIME or use DATE_FORMAT:

Specifiers

%T  Time, 24-hour (hh:mm:ss)
%m  Month, numeric (00..12)
%d  Day of the month, numeric (00..31)
%Y  Year, numeric, four digits

SELECT DATE_FORMAT(FROM_UNIXTIME(`date`), '%T %m/%d/%Y)

Or

SELECT FROM_UNIXTIME(`date`, '%T %m/%d/%Y')

Answer 2

Use DATE_FORMAT

DATE_FORMAT(FROM_UNIXTIME(`date`), "%T %m/%d/%Y")

This will return hh:mm:ss dd/mm/YYYY from unix time stored in database.

Answer 3

you have to define your custom date/time format. please have a look at:

http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html#function_from-unixtime

SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(), '%h:%i:%s %M/%D/%Y');