MySQL数据库列中的时间格式无法识别 [英] time format from MySQL database column is unrecognisable

问题描述

我有一个Mysql数据库表，它有一个创建时间"列，其值类似于1538899092000，1543476095000等. 这是什么 ?以及如何将其转换为我们的常规格式? 预先感谢.

I have a Mysql database table and it has a column "Created time" which has values like 1538899092000 , 1543476095000 and so on. What is this ? and how to convert this into our regular format ? Thanks in advance.

推荐答案

它看起来像是Unix时间戳乘以1000(即毫秒而不是秒).这些时间戳在JavaScript中使用.

It looks like a Unix timestamp multiplied by 1,000 (i.e. in milliseconds rather than seconds). These kinds of timestamps are used in JavaScript.

使用FROM_UNIXTIME(created_time/1000)将其转换为DATETIME.

mysql> select from_unixtime(1543476095000/1000);
+-----------------------------------+
| from_unixtime(1543476095000/1000) |
+-----------------------------------+
| 2018-11-29 01:21:35               |
+-----------------------------------+

并使用1000 * UNIX_TIMESTAMP(datetime value)将DATETIME转换为这种格式.

And use 1000 * UNIX_TIMESTAMP(datetime value) to convert a DATETIME to this format.

这篇关于MySQL数据库列中的时间格式无法识别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！