Convert timestamp to date in MySQL query

DATE_FORMAT(FROM_UNIXTIME(`user.registration`), '%e %b %Y') AS 'date_formatted'

To just get a date you can cast it

cast(user.registration as date)

and to get a specific format use date_format

date_format(registration, '%Y-%m-%d')

SQLFiddle demo

Convert timestamp to date in MYSQL

Make the table with an integer timestamp:

mysql> create table foo(id INT, mytimestamp INT(11));Query OK, 0 rows affected (0.02 sec)

Insert some values

mysql> insert into foo values(1, 1381262848);Query OK, 1 row affected (0.01 sec)

Take a look

mysql> select * from foo;+------+-------------+| id   | mytimestamp |+------+-------------+|    1 |  1381262848 |+------+-------------+1 row in set (0.00 sec)

Convert the number to a timestamp:

mysql> select id, from_unixtime(mytimestamp) from foo;+------+----------------------------+| id   | from_unixtime(mytimestamp) |+------+----------------------------+|    1 | 2013-10-08 16:07:28        |+------+----------------------------+1 row in set (0.00 sec)

Convert it into a readable format:

mysql> select id, from_unixtime(mytimestamp, '%Y %D %M %H:%i:%s') from foo;+------+-------------------------------------------------+| id   | from_unixtime(mytimestamp, '%Y %D %M %H:%i:%s') |+------+-------------------------------------------------+|    1 | 2013 8th October 04:07:28                       |+------+-------------------------------------------------+1 row in set (0.00 sec)