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)

Ankith Reddy

Updated on: 30-Jul-2019

4K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started