How to convert timestamp to date MySQL

问题:

I'm following a guide on YouTube on how to make an Image Uploading website, by phpacademy, great guide. However, I'm trying to extend it a bit by displaying it in a human-readable way.

At the moment the albums.timestamp is saved on the database as UNIX_TIMESTAMP() and results in 1348372089 for example. Now, if I want to convert these numbers into a regular date and time, such as 23-09-2012 05:00 (European standard), how should I proceed?

I have tried DATE(albums.timestamp) and CONVERT(albums.timestamp, 120), unix_timestamp(albums.timestamp) as well as CAST(120 as albums.timestamp), but none of them works.

回答1:

Use FROM_UNIXTIME(str,format). http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_from-unixtime

The second parameter will let you format the date string to your liking.

Formatting characters:
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format

回答2:

FROM_UNIXTIME(albums.timestamp) works though!