What's missing from the other answers (as of this writing) and not directly obvious is that from_unixtime can take a second parameter to specify the format like so:

SELECT
  from_unixtime(timestamp, '%Y %D %M %H:%i:%s')
FROM 
  your_table

I think what you're looking for is FROM_UNIXTIME()

Need a unix timestamp in a specific timezone?

Here's a one liner if you have quick access to the mysql cli:

mysql> select convert_tz(from_unixtime(1467095851), 'UTC', 'MST') as 'local time';

+---------------------+
| local time          |
+---------------------+
| 2016-06-27 23:37:31 |
+---------------------+

Replace 'MST' with your desired timezone. I live in Arizona 🌵 thus the conversion from UTC to MST.

Why bother saving the field as readable? Just us AS

SELECT theTimeStamp, FROM_UNIXTIME(theTimeStamp) AS readableDate
               FROM theTable
               WHERE theTable.theField = theValue;

EDIT: Sorry, we store everything in milliseconds not seconds. Fixed it.

You can use the DATE_FORMAT function. Here's a page with examples, and the patterns you can use to select different date components.

Easy and simple way:

select from_unixtime(column_name, '%Y-%m-%d') from table_name

If you would like to convert time AND display the data in a specific format you can use this string.

date_format(convert_tz(from_unixtime(TIMESTAMP), 'UTC', 'DESIRED TZ'), '%m/%d/%y')

where you add convert_tz to a date_format string. the %m/%d/%y being month/day/year. you can find all the specific formats here https://www.w3schools.com/sql/func_mysql_date_format.asp

Since I found this question not being aware, that mysql always stores time in timestamp fields in UTC but will display (e.g. phpmyadmin) in local time zone I would like to add my findings.

I have an automatically updated last_modified field, defined as:

`last_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Looking at it with phpmyadmin, it looks like it is in local time, internally it is UTC

SET time_zone = '+04:00'; // or '+00:00' to display dates in UTC or 'UTC' if time zones are installed.
SELECT last_modified, UNIX_TIMESTAMP(last_modified), from_unixtime(UNIX_TIMESTAMP(last_modified), '%Y-%c-%d %H:%i:%s'), CONVERT_TZ(last_modified,@@session.time_zone,'+00:00') as UTC FROM `table_name`

In any constellation, UNIX_TIMESTAMP and 'as UTC' are always displayed in UTC time.

Run this twice, first without setting the time_zone.

whenever we are using FROM_UNIX like here in

SELECT
  FROM_UNIXTIME(timestamp) 
FROM 
  your_table;

make sure to know whether in what format timestamp is, like if its in seconds, we can use the above

But if its in milliseconds, we have to divide it by 1000

SELECT 
 FROM_UNIXTIME(column_name / 1000) AS readableDate 
FROM 
 table_name;