How to convert timestamp to datetime in MySQL?

To answer Janus Troelsen comment

Use UNIX_TIMESTAMP instead of TIMESTAMP

SELECT from_unixtime( UNIX_TIMESTAMP(  "2011-12-01 22:01:23.048" ) )

The TIMESTAMP function returns a Date or a DateTime and not a timestamp, while UNIX_TIMESTAMP returns a unix timestamp

Use the FROM_UNIXTIME() function in MySQL

Remember that if you are using a framework that stores it in milliseconds (for example Java's timestamp) you have to divide by 1000 to obtain the right Unix time in seconds.

DATE_FORMAT(FROM_UNIXTIME(`orderdate`), '%Y-%m-%d %H:%i:%s') as "Date" FROM `orders`

This is the ultimate solution if the given date is in encoded format like 1300464000