13位时间戳是毫秒级unix时间戳，需除以1000转为秒级再用FROM_UNIXTIME()转换为日期，结合date_FORMAT()可自定义格式，注意数据类型、NULL值、时区及索引性能问题。
在mysql中处理13位时间戳（通常是毫秒级）并将其转换为可读的日期格式，核心思路其实很简单：你需要先将这个毫秒级时间戳转换为秒级，然后再使用MySQL内置的
FROM_UNIXTIME()
函数。具体操作就是将13位时间戳除以1000。
解决方案
要将一个存储为13位（毫秒）的时间戳字段（假设名为
timestamp_ms
，类型为
BIGINT
）转换为标准的日期时间格式，你可以使用以下sql语句：
SELECT FROM_UNIXTIME(timestamp_ms / 1000) AS converted_datetime FROM your_table_name;
如果你需要更精细的日期时间格式控制，可以结合
DATE_FORMAT()
函数：
SELECT DATE_FORMAT(FROM_UNIXTIME(timestamp_ms / 1000), '%Y-%m-%d %H:%i:%s') AS formatted_datetime FROM your_table_name;
这基本上就是解决问题的关键。我个人在处理来自各种系统（比如前端JavaScript的
Date.now()
或者Java的
System.currentTimeMillis()
）传来的时间戳时，经常会遇到这种13位的情况。一开始可能会有点疑惑，因为MySQL的
FROM_UNIXTIME
默认是针对10位秒级时间戳设计的，但只要理解了毫秒和秒的换算关系，一切就迎刃而解了。
为什么我的MySQL时间戳是13位，而不是常见的10位？
这个问题问得好，这其实是很多开发者初次遇到时会有的疑问。简单来说，时间戳的位数差异反映了其精度。
我们常说的“Unix时间戳”或者“Epoch时间”通常是指从1970年1月1日00:00:00 UTC（协调世界时）开始经过的秒数，这是一个10位的整数。这是最传统、最广泛使用的Unix时间戳格式。
然而，在现代应用开发中，尤其是在需要更高时间精度或者跨语言、跨平台数据交换的场景下，毫秒级时间戳变得非常流行。例如：
- JavaScript的
Date.now()
：
这个函数返回的就是当前时间的毫秒数，一个典型的13位数字（例如：1678886400000
）。
- Java的
System.currentTimeMillis()
：
同样，它也返回当前时间的毫秒数，也是13位。 - 一些前端框架或API设计： 为了捕获更细粒度的事件发生时间，或者在分布式系统中保持时间同步的精确性，很多系统会选择存储毫秒级时间戳。
所以，当你在MySQL数据库中看到13位时间戳时，几乎可以肯定它代表的是毫秒级的Unix时间戳。这并不是什么“错误”，而是一种更高精度的时间表示方式。我曾经手头的一个项目，后端是Java，前端是React，数据传递过程中就自然而然地使用了毫秒时间戳，存入数据库时也原样保留，直到需要报表展示时才进行转换。
如何将13位时间戳精确转换为特定日期格式？
将13位时间戳转换为特定日期格式，我们主要依赖
FROM_UNIXTIME()
和
DATE_FORMAT()
这两个函数。
FROM_UNIXTIME()
负责将Unix时间戳（秒级）转换为MySQL的
DATETIME
或
TIMESTAMP
类型，而
DATE_FORMAT()
则允许你自定义输出的日期时间字符串格式。
正如前面提到的，关键在于先将13位毫秒时间戳除以1000，得到10位秒级时间戳。然后，你可以使用
DATE_FORMAT()
函数的各种格式化符号来达到你想要的精确格式。
-- 假设你的13位时间戳字段是 event_timestamp_ms SELECT DATE_FORMAT(FROM_UNIXTIME(event_timestamp_ms / 1000), '%Y-%m-%d %H:%i:%s') AS "精确到秒", DATE_FORMAT(FROM_UNIXTIME(event_timestamp_ms / 1000), '%Y年%m月%d日 %H时%i分%s秒') AS "中文格式", DATE_FORMAT(FROM_UNIXTIME(event_timestamp_ms / 1000), '%W, %M %D, %Y %r') AS "英文口语化格式", DATE_FORMAT(FROM_UNIXTIME(event_timestamp_ms / 1000), '%Y%m%d%H%i%s') AS "无分隔符格式" FROM your_log_table;
这里列举了一些常用的格式化符号：
-
%Y
: 四位年份 (e.g., 2023)
-
%m
: 两位月份 (01-12)
-
%d
: 两位日期 (01-31)
-
%H
: 两位小时 (00-23)
-
%i
: 两位分钟 (00-59)
-
%s
: 两位秒 (00-59)
-
%f
: 微秒 (000000-999999) – 注意：
FROM_UNIXTIME
本身不直接支持毫秒或微秒精度，它处理的是秒。如果你原始的13位时间戳需要保留毫秒精度，你可能需要单独处理毫秒部分，或者考虑MySQL 5.6.4+版本引入的
DATETIME(N)
类型，它支持微秒精度，但转换时仍需技巧。对于13位时间戳，通常我们只取到秒级。
-
%W
: 星期几的完整名称 (e.g., Monday)
-
%m
: 月份的完整名称 (e.g., March)
-
%d
: 带有英文后缀的日期 (e.g., 1st, 2nd)
-
%r
: 12小时制时间 (e.g., 09:30:00 PM)
选择合适的格式化符号组合，就能满足绝大多数的日期时间显示需求。
转换过程中可能遇到的坑及优化建议？
尽管转换看起来简单，但在实际应用中，还是有一些“坑”需要留意，以及一些优化建议可以考虑。
可能遇到的坑：
- 数据类型问题： 最常见的就是时间戳字段不是数字类型（比如是
VARCHAR
）。如果你的13位时间戳被存储为字符串，那么直接进行
/ 1000
的数学运算可能会出错，或者导致隐式类型转换，影响性能。我建议这类时间戳字段应该始终是
BIGINT
类型，确保数据完整性和计算效率。如果确实是
VARCHAR
，你可能需要先用
CAST(timestamp_str AS UNSIGNED)
或
CONVERT(timestamp_str, UNSIGNED)
进行转换。
- 空值（NULL）处理： 如果你的时间戳字段中存在
NULL
值，
FROM_UNIXTIME(NULL)
会返回
NULL
。这通常是符合预期的行为，但如果你希望
NULL
时间戳显示为特定的默认值（比如空字符串或某个固定日期），你需要使用
IFNULL()
或
COALESCE()
函数来处理。
- 无效的时间戳： 极少数情况下，你可能会遇到非法的数字（例如负数，或者一个远远超出合理范围的巨大数字）。
FROM_UNIXTIME()
对于负数或非常大的数字可能会返回
NULL
或不预期的结果。在应用层插入数据前进行校验是个好习惯。
- 时区问题：
FROM_UNIXTIME()
默认返回的是服务器时区的时间。如果你的13位时间戳是UTC时间，而你的MySQL服务器时区不是UTC，那么转换后的时间可能会有偏差。为了确保准确性，通常建议在数据库中统一存储UTC时间戳，并在显示时根据用户所在时区进行转换，或者在mysql连接时设置会话时区。
优化建议：
- 索引考量： 如果你经常需要根据转换后的日期时间进行查询（例如
WHERE DATE_FORMAT(FROM_UNIXTIME(timestamp_ms / 1000), '%Y-%m-%d') = '2023-03-15'
），那么这种写法会导致函数内部执行，无法利用
timestamp_ms
字段上的索引。这对于大数据量查询来说是个性能杀手。
- 解决方案： 考虑在表中新增一个
DATETIME
类型的列，专门用于存储转换后的日期时间，并在这个新列上建立索引。你可以在数据写入时同步更新这个日期时间列，或者使用触发器来自动维护。
- 另一种方式是，如果查询条件是日期范围，可以尝试将日期范围转换为13位时间戳范围进行查询，例如：
WHERE timestamp_ms BETWEEN UNIX_TIMESTAMP('2023-03-15 00:00:00') * 1000 AND UNIX_TIMESTAMP('2023-03-15 23:59:59') * 1000。这样可以利用
timestamp_ms
上的索引。
- 解决方案： 考虑在表中新增一个
- 应用层转换： 如果你的应用对性能要求极高，并且大部分时间戳的转换都发生在数据读取之后，考虑将13位时间戳直接传给应用层，在应用代码中进行转换和格式化。大多数编程语言处理时间戳和日期格式化的效率都非常高。
- 视图（View）： 对于经常需要转换并展示的场景，可以创建一个视图，将转换逻辑封装在视图中。这样，每次查询视图时，都会得到已经转换好的日期时间，简化了查询语句。
-- 创建一个视图，包含转换后的日期时间 CREATE VIEW your_table_with_datetime_view AS SELECT *, -- 包含所有原始字段 DATE_FORMAT(FROM_UNIXTIME(timestamp_ms / 1000), '%Y-%m-%d %H:%i:%s') AS formatted_datetime FROM your_table_name; -- 查询视图 SELECT * FROM your_table_with_datetime_view WHERE formatted_datetime LIKE '2023-03%';
请注意，虽然视图可以简化查询，但其底层仍然是执行函数转换，因此对大量数据的过滤查询性能影响与直接使用函数相同。所以，索引优化方案通常是更根本的解决之道。
