Cách chuyển timestamp sang datetime trong MySQL?

Dùng hàm FROM_UNIXTIME().

Tạo bảng kiểu INT

mysql> CREATE TABLE TimestamptoDateDemo (
    -> YourTimeStamp INT(11)
    -> );
Query OK, 0 rows affected (0.57 sec)

mysql> INSERT INTO TimestamptoDateDemo VALUES(1389453221);
Query OK, 1 row affected (0.23 sec)

mysql> SELECT * FROM TimestamptoDateDemo;

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

Chuyển sang timestamp

mysql> SELECT FROM_UNIXTIME(YourTimeStamp) FROM TimestamptoDateDemo;

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

Chuyển sang datetime với định dạng tùy chỉnh

SELECT FROM_UNIXTIME(YourColumnName, '%Y %D %M %h:%i:%s') FROM YourTableName;

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