MySQL时间戳处理实战 13位时间戳转日期格式的SQL语句

要将mysql中的13位毫秒级时间戳转换为可读的日期格式，核心操作是先将其除以1000转换为10位秒级unix时间戳，再使用from_unixtime()函数格式化为yyyy-mm-dd hh:mm:ss等形式；若需保留毫秒，可通过字符串拼接方式添加毫秒部分，即使用concat结合lpad处理timestamp_ms % 1000得到三位毫秒数；13位时间戳常见于Java或JavaScript等系统，因其精确到毫秒，而标准unix时间戳为10位秒级；除from_unixtime外，unix_timestamp()用于日期转时间戳，date_format()用于格式化日期输出，str_to_date()用于字符串转日期；处理时区问题时需注意unix时间戳基于utc，mysql服务器和应用的时区设置应保持一致，推荐统一使用utc并利用set time_zone或convert_tz()函数进行时区管理，以避免时间偏差。

MySQL中将13位时间戳（毫秒级）转换为可读的日期格式，核心操作是先将13位时间戳除以1000，将其转换为标准的10位秒级Unix时间戳，再利用MySQL的

FROM_UNIXTIME()

函数进行格式化。

解决方案

要将存储在MySQL数据库中的13位时间戳转换为我们常见的日期时间格式，比如

YYYY-MM-DD HH:MM:SS

，你可以使用以下sql语句：

假设你的表名为

your_table

，存储13位时间戳的列名为

timestamp_ms

：

select     timestamp_ms,     FROM_UNIXTIME(timestamp_ms / 1000, '%Y-%m-%d %H:%i:%s') AS formatted_datetime FROM     your_table;

如果你需要精确到毫秒，

FROM_UNIXTIME

本身无法直接支持毫秒级的输出格式，因为它处理的是秒。但你可以结合其他字符串操作来实现，或者存储为

DECIMAL

类型然后进行处理。不过，通常情况下，秒级精度对大多数业务场景已经足够。如果非要保留毫秒，你可能需要将毫秒部分单独提取出来，或者在应用层进行处理。

例如，如果想在SQL中展示毫秒部分，可以这么做（虽然这不是

FROM_UNIXTIME

直接提供的功能，而是字符串拼接）：

SELECT     timestamp_ms,     CONCAT(         FROM_UNIXTIME(timestamp_ms / 1000, '%Y-%m-%d %H:%i:%s'),         '.',         LPAD(timestamp_ms % 1000, 3, '0')     ) AS formatted_datetime_with_ms FROM     your_table;

这里

timestamp_ms % 1000

用于获取毫秒部分，

LPAD

确保它始终是三位数（例如，

5

变成

005

）。

为什么我的时间戳是13位，而不是常见的10位？

说实话，我刚开始接触13位时间戳的时候也愣了一下，觉得和常见的10位Unix时间戳不太一样。后来才发现，这多出来的三位数，其实代表的是毫秒（milliseconds）。我们平时最常说的Unix时间戳，通常指的是从1970年1月1日UTC（协调世界时）午夜0点0分0秒开始，到某个时间点所经过的秒数，所以它是10位的。

而13位时间戳，则精确到了毫秒级别。这在很多现代系统里非常常见，特别是那些对时间精度要求比较高的场景，比如日志记录、事件排序或者分布式系统中的时间同步。例如，Java里的

System.currentTimeMillis()

方法，或者JavaScript里的

Date.now()

方法，它们默认返回的就是13位的毫秒级时间戳。很多前端框架或后端服务在生成时间戳时，也会习惯性地使用这种更高精度的时间戳。所以，当你看到13位时间戳时，别慌，它只是比你想象的更“细致”了一点。

除了FROM_UNIXTIME，还有哪些时间戳处理函数值得关注？

除了我们今天的主角

FROM_UNIXTIME()

，MySQL里还有一些处理日期和时间戳的“好帮手”，它们各有侧重，用好了能省不少事。

• UNIX_TIMESTAMP()

  : 这个函数是

  FROM_UNIXTIME()

  的“逆操作”。如果你有一个

  DATETIME

  或

  TIMESTAMP

  类型的列，想把它转换成10位的Unix时间戳（秒级），就可以用它。比如

  SELECT UNIX_TIMESTAMP('2023-10-27 10:30:00');

  会返回一个10位数字。我个人觉得，在需要进行时间戳比较或者存储时，它非常实用。

• DATE_FORMAT()

  : 如果你已经有一个

  DATE

  、

  DATETIME

  或

  TIMESTAMP

  类型的值，但想把它显示成特定的格式（比如只显示年月日，或者只显示小时分钟），

  DATE_FORMAT()

  就是你的首选。它的灵活性非常高，你可以定义各种复杂的格式字符串。例如，

  SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日 %H点%i分');

  ，这比你手动拼接字符串方便多了。

• STR_TO_DATE()

  : 这个函数是用来将一个字符串解析成

  DATE

  、

  TIME

  或

  DATETIME

  值的。当你的日期数据是以字符串形式存储，并且格式不标准时，

  STR_TO_DATE()

  就派上用场了。你需要提供字符串和它对应的格式模板。比如，

  SELECT STR_TO_DATE('27-OCT-2023', '%d-%M-%Y');

  。这在数据导入或者清洗时，经常会用到。

理解这些函数的区别和适用场景，能让你在处理MySQL的时间数据时更加游刃有余。它们都是构建高效SQL查询和数据处理流程的重要工具。

处理时间戳时，如何避免常见的时区陷阱？

时间戳这东西，看似简单，但一碰到时区，就容易让人头疼。我个人就踩过不少坑，数据在不同环境里显示的时间对不上，那真是排查起来想撞墙。这里有几个关键点，能帮你避免时区带来的麻烦：

1. Unix时间戳是UTC时间: 这是最重要的一点。无论你在哪个时区生成Unix时间戳，它都代表着从1970年1月1日00:00:00 UTC开始经过的秒数（或毫秒数）。所以，时间戳本身是不带时区信息的，它是“全球统一”的。问题出在把它转换成可读日期时。

2. MySQL服务器的时区设置: 当你使用

   FROM_UNIXTIME()

   将Unix时间戳转换为

   DATETIME

   或

   TIMESTAMP

   时，MySQL会根据它当前的时区设置来解释这个时间戳。如果你的MySQL服务器时区是UTC，那么转换出来的就是UTC时间。如果服务器时区是中国上海（CST），那么转换出来的就是CST时间。

   你可以通过

   SHOW VARIABLES LIKE 'time_zone';

   来查看当前MySQL服务器的时区设置。如果它显示的是

   SYSTEM

   ，那么它会跟随操作系统时区。

3. 应用程序与数据库时区一致性: 最常见的时区问题往往发生在应用程序和数据库的时区设置不一致时。比如，你的应用程序是基于UTC开发的，但数据库服务器的时区却是本地时区。当应用程序存入一个UTC时间戳，数据库却以本地时区来显示或处理时，就可能出现8小时（或不同时区差）的偏差。

   解决方案：

   • 统一使用UTC: 我个人推荐，无论是数据库还是应用程序，内部处理时间时都尽量统一使用UTC时间。只在最终展示给用户时，才根据用户的时区偏好进行转换。
   • 设置会话时区: 你可以在每次连接到MySQL后，通过

     SET time_zone = '+00:00';

     （设置为UTC）或

     SET time_zone = 'Asia/Shanghai';

     （设置为特定时区）来指定当前会话的时区。这样可以确保你的SQL查询在特定时区下执行。

   • CONVERT_TZ()

     函数: 如果你需要将一个日期时间值从一个时区转换到另一个时区，

     CONVERT_TZ(dt, from_tz, to_tz)

     函数非常有用。例如，

     SELECT CONVERT_TZ('2023-10-27 10:00:00', 'UTC', 'Asia/Shanghai');

     会将UTC时间转换为上海时间。

理解并管理好时区，是处理时间戳数据不可或缺的一部分。它能帮你避免很多不必要的错误和数据混乱。