Mysql 中日期类型bigint和datetime互转

MySql数据库中字段类型bigint 长度是10位的

mysql> select (from_unixtime(1554047999))as datatime;
+---------------------+
| datatime |
+---------------------+
| 2019-03-31 23:59:59 |
+---------------------+
1 row in set

mysql> select (unix_timestamp('2019-03-31 23:59:59'))as unixtime;
+------------+
| unixtime |
+------------+
| 1554047999 |
+------------+
1 row in set

如果bigint存储的是13位，因此转换时会有乘以1000及除以1000的操作

mysql> select (from_unixtime(1556609724438/1000))as datatime;
+--------------------------+
| datatime |
+--------------------------+
| 2019-04-30 15:35:24.4380 |
+--------------------------+

具体到表里面查询

mysql> select id,tested from table_A;
+----+------------+
| id | tested |
+----+------------+
| 6 | 1554859010 |
| 7 | 1554859194 |
+----+------------+

mysql> select id,from_unixtime(tested)AS tested from table_A;
+----+---------------------+
| id | tested |
+----+---------------------+
| 6 | 2019-04-10 09:16:50 |
| 7 | 2019-04-10 09:19:54 |
+----+---------------------+

转载于:https://www.cnblogs.com/monogem/p/10796923.html