Convert timestamp to a readable date during query

Question

How can I tell MySQL to format a timestamp as a readable date before outputting a query result in a MySQL client/console?

Answer 1

Use FROM_UNIXTIME like this:

SELECT
  FROM_UNIXTIME(timestamp_field) AS formatted_date
FROM
  tablename;

Answer 2

SELECT DATE_FORMAT(column_name, '%d/%m/%Y') FROM tablename

Reference

You can also use TIME_FORMAT