How to convert timestamp to datetime in MySQL?

We can convert the timestamp to date time with the help of FROM_UNIXTIME() function. Let us see an example. First, we will create a table with column of int type. Then we convert it to timestamp and again into date time.

Creating a table with integer type.

 mysql> create table TimestamptoDateDemo -> ( -> YourTimeStamp int(11) -> ); Query OK, 0 rows affected (0.57 sec) 

Inserting records into the table.

 mysql> insert into TimestamptoDateDemo values(1389453221); Query OK, 1 row affected (0.23 sec) 

To display all the records.

 mysql> select *from TimestamptoDateDemo; 

The following is the output.

 +---------------+ | YourTimeStamp | +---------------+ | 1389453221 | +---------------+ 1 row in set (0.00 sec) 

Syntax to convert it into timestamp.

 SELECT from_unixtime(yourColumnName) from yourTableName; 

The following is the implementation.

 mysql>select from_unixtime(YourTimeStamp) from TimestamptoDateDemo; 

Here is the output.

 +------------------------------+ | from_unixtime(YourTimeStamp) | +------------------------------+ | 2018-09-11 20:43:41 | +------------------------------+ 1 row in set (0.04 sec) 

Syntax to convert the above timestamp to datetime.

 select from_unixtime(YourColumnName, '%Y %D %M %h:%i:%s') from yourTableName; 

The following is the output that shows the datetime.

 +---------------------------------------------------+ | from_unixtime(YourTimeStamp, '%Y %D %M %h:%i:%s') | +---------------------------------------------------+ | 2018 11th September 08:43:41 | +---------------------------------------------------+ 1 row in set (0.03 sec)