MySQL时间戳处理指南 13位数字转日期格式的实用技巧

mysql中处理13位毫秒级时间戳需先除以1000转换为秒级，因from_unixtime函数仅支持秒级时间戳；直接使用13位时间戳会导致错误结果或null，故必须进行单位换算，例如select from_unixtime(timestamp_ms / 1000, ‘%y-%m-%d %h:%i:%s’)可将毫秒时间戳转为可读日期格式；而将日期转为13位时间戳则需使用unix_timestamp()函数结果乘以1000，并存储于bigint类型字段以避免溢出；常见问题包括使用int导致溢出、毫秒精度丢失、时区不一致及混合时间戳格式混乱，因此应统一存储为bigint类型的毫秒时间戳并在应用层或数据库层做好时区处理，确保数据一致性与准确性。

MySQL中处理13位数字的时间戳，也就是毫秒级时间戳，核心在于

FROM_UNIXTIME

函数默认只认秒。所以，拿到13位时间戳时，我们得先把它除以1000，转换成秒级，然后才能用常规的日期格式化函数来处理。这就像你给一个只认识“米”的工具，你却给了它一个“毫米”的数字，它肯定得先换算一下。

解决方案

要将存储为13位数字（毫秒）的时间戳转换为可读的日期格式，最直接的办法就是利用MySQL的数学运算和内置函数。

假设你的时间戳字段名为

timestamp_ms

，数据类型通常是

BIGINT

：

SELECT     FROM_UNIXTIME(timestamp_ms / 1000, '%Y-%m-%d %H:%i:%s') AS formatted_datetime,     FROM_UNIXTIME(timestamp_ms / 1000, '%Y-%m-%d') AS formatted_date_only,     FROM_UNIXTIME(timestamp_ms / 1000, '%H:%i:%s') AS formatted_time_only FROM     your_table_name;

这里，

timestamp_ms / 1000

将毫秒转换为秒。

FROM_UNIXTIME()

函数的第二个参数是日期格式字符串，你可以根据需要调整，比如

%Y-%m-%d %H:%i:%s

表示“年-月-日 时:分:秒”。如果需要更精确到毫秒的显示，MySQL 8.0及更高版本支持

FROM_UNIXTIME

的第二个参数中包含毫秒格式，例如

%f

，但前提是时间戳本身也包含小数秒，而我们这里的13位整数时间戳在除以1000后，小数部分会被截断，所以通常只到秒级。

MySQL处理时间戳时，为何13位与10位数字表现不同？

这其实是个很基础但又特别容易让人混淆的点。简单来说，我们常说的Unix时间戳，它是一个整数，代表从1970年1月1日00:00:00 UTC（协调世界时）开始经过的秒数。所以，一个10位的数字（比如

1678886400

）通常指的就是秒级时间戳。

但现实应用中，很多系统为了更高的精度，会记录毫秒级时间戳。这就出现了13位数字的时间戳，比如

1678886400000

。这个数字比秒级时间戳多出了三位，这多出来的三位就是毫秒。

MySQL的

FROM_UNIXTIME()

函数，它骨子里就是设计来处理秒级时间戳的。你直接给它一个13位的毫秒数，它会把它当作一个超大的秒数来处理，结果自然就错得离谱，可能直接给你返回一个“未来”的日期，或者干脆是

NULL

，因为这个秒数超出了它能理解的范围。

所以，关键就在于这个“单位”的转换。就像你和老外交流，他只懂英尺，你却一直说米，那肯定鸡同鸭讲。我们必须把“毫米”换算成“米”，

FROM_UNIXTIME

才能正确理解。

如何将日期时间转换为13位毫秒时间戳并在MySQL中存储？

反过来，如果你想把一个标准的日期时间字符串或者MySQL的

DATETIME

、

TIMESTAMP

类型数据，转换成13位的毫秒时间戳并存储，这同样需要一点小技巧。

MySQL提供了一个非常方便的函数叫

UNIX_TIMESTAMP()

。这个函数的作用就是把一个日期时间值转换成10位的Unix秒级时间戳。

比如，你想把当前时间转换成毫秒时间戳：

SELECT UNIX_TIMESTAMP(NOW()) * 1000 AS current_timestamp_ms;

如果你有一个具体的日期时间字符串，也可以这样做：

SELECT UNIX_TIMESTAMP('2023-10-27 15:30:00') * 1000 AS specific_timestamp_ms;

这里面的逻辑也很直白：

UNIX_TIMESTAMP()

给出秒数，然后我们再乘以

1000

，就得到了毫秒数。

关于存储： 既然是13位的数字，它的大小会超过普通

INT

类型的最大值（

2,147,483,647

）。所以，在数据库设计时，存储这种毫秒级时间戳的字段，务必使用

BIGINT

类型。这是个非常重要的细节，不然数据分分钟溢出，你会看到一些奇怪的负数或者截断的值，那可就麻烦了。我以前就因为这个吃过亏，排查了半天才发现是字段类型的问题。

处理13位时间戳时，我可能会遇到哪些常见问题和陷阱？

处理这种毫秒级时间戳，确实有些坑是比较容易踩到的。

1. 数据类型选择不当： 这是最常见的错误，没有之一。很多人习惯性地用

   INT

   来存储时间戳。10位秒级时间戳用

   INT

   没问题，但13位毫秒级时间戳就肯定会溢出。我见过不少系统因为这个导致时间戳变成负数或者错误的值，进而引发各种业务逻辑问题。所以，记住，

   BIGINT

   是唯一正确的选择。

2. 毫秒精度丢失： 虽然我们把13位毫秒时间戳转换成了秒级再处理，但如果你期望在MySQL中直接显示毫秒，那就会遇到问题。

   FROM_UNIXTIME()

   在转换过程中，默认是不会保留毫秒的。如果你真的需要显示毫秒，通常需要从原始的

   BIGINT

   字段中提取出来，或者在应用层进行格式化。当然，MySQL 8.0+版本在某些日期时间函数中（如

   NOW(6)

   ）可以支持微秒精度，但

   FROM_UNIXTIME

   直接从Unix时间戳转换时，通常还是只到秒。

3. 时区问题： Unix时间戳本身是UTC时间。

   FROM_UNIXTIME()

   在转换时，会根据MySQL服务器的当前时区或者当前会话的时区设置来显示日期时间。如果你的应用服务器和数据库服务器时区不一致，或者用户在不同的时区访问，就可能看到“错误”的时间。解决办法是统一时区（比如都用UTC存储和处理），或者在查询时使用

   CONVERT_TZ()

   函数进行明确的时区转换。

4. 混合时间戳格式： 有时候一个系统里可能同时存在10位（秒）和13位（毫秒）的时间戳，这会让你在处理时非常头疼。你得在业务逻辑层面做好判断，或者在数据入库时就统一成一种格式。我个人倾向于在数据库层面统一存储为毫秒级（

   BIGINT

   ），这样至少在存储层面是一致的，转换时也只需要统一除以1000。

5. 不必要的复杂性： 有些时候，如果你只是想记录一个事件发生的时间，MySQL自带的

   DATETIME

   或

   TIMESTAMP

   类型可能更直观、更方便，特别是

   TIMESTAMP

   类型还能自动处理时区转换。只有当你确实需要进行跨系统、跨语言的统一时间戳交换，或者对毫秒精度有严格要求时，才考虑使用这种数字时间戳。不要为了用而用，徒增复杂性。