You can use the below functions to convert the timestamp to date and date to timestamp.
timestamp to date -> from_unixtime()
date to timestamp -> unix_timestamp()
Sample uses are as follows.
Convert Timestamp to Date in MySQL
Unformatted Display
|
1 2 3 4 5 6 7 |
mysql> select id, from_unixtime(timecreated) from my_table where id=1; +----+----------------------------+ | id | from_unixtime(timecreated) | +----+----------------------------+ | 1 | 2014-07-09 13:56:24 | +----+----------------------------+ 1 row in set (0.00 sec) |
Formatted Display
|
1 2 3 4 5 6 7 |
mysql> select id, from_unixtime(timecreated, '%Y %D %M %H:%i:%s') from my_table where id=1; +----+-------------------------------------------------+ | id | from_unixtime(timecreated, '%Y %D %M %H:%i:%s') | +----+-------------------------------------------------+ | 1 | 2014 9th July 13:56:24 | +----+-------------------------------------------------+ 1 row in set (0.00 sec) |
Convert Date to Timestamp in MySQL
|
1 2 3 4 5 6 7 |
mysql> SELECT unix_timestamp('2014-07-09 13:56:24'); +---------------------------------------+ | unix_timestamp('2014-07-09 13:56:24') | +---------------------------------------+ | 1404903384 | +---------------------------------------+ 1 row in set (0.00 sec) |
