13位时间戳是毫秒级，需除以1000转为秒后用FROM_UNIXTIME转换，再通过DATE_FORMAT格式化输出，注意处理NULL值、时区和性能问题。
在MySQL中，将13位时间戳（毫秒级）转换为可读的日期时间格式，核心在于先将其转换为秒级时间戳，再利用
FROM_UNIXTIME
函数进行转换。
解决方案
要将存储为
BIGINT
类型的13位时间戳字段（例如
timestamp_ms
）转换为标准的日期时间格式，你需要用它除以1000，然后传入
FROM_UNIXTIME
函数。
例如，如果你有一个名为
your_table
的表，其中包含一个名为
timestamp_ms
的13位时间戳列，你可以这样查询：
SELECT timestamp_ms, FROM_UNIXTIME(timestamp_ms / 1000) AS converted_datetimeFROM your_table;
如果你需要更具体的日期格式，可以结合
DATE_FORMAT
函数：
SELECT timestamp_ms, DATE_FORMAT(FROM_UNIXTIME(timestamp_ms / 1000), '%Y-%m-%d %H:%i:%s') AS formatted_datetimeFROM your_table;
这会把像
1678886400000
这样的毫秒级时间戳，转换成
2023-03-15 08:00:00
这样的标准格式。
为什么我的时间戳是13位而不是10位？它代表什么？
这确实是个常见的问题，尤其当你从不同系统集成数据时。简单来说，10位时间戳通常表示的是自Unix纪元（1970年1月1日 00:00:00 UTC）以来的秒数，而13位时间戳则表示的是毫秒数。
很多现代编程语言和框架，比如Java的
System.currentTimeMillis()
，JavaScript的
Date.now()
，或者一些前端框架在生成时间戳时，默认就是毫秒级的。这可能是为了更精细地记录时间，因为秒级在某些场景下精度不够。我个人在处理前后端数据交互时，就经常遇到前端传过来的是13位，而数据库里习惯用10位，或者需要转换为可读格式的情况。这种差异往往是导致时间转换出错的根源，所以识别它非常重要。
如何在MySQL中将13位时间戳转换为指定日期格式？
将13位时间戳转换为你想要的具体日期格式，关键在于
DATE_FORMAT()
函数。这个函数非常灵活，允许你定义输出的字符串格式。
你已经知道需要先将毫秒转换为秒：
FROM_UNIXTIME(timestamp_ms / 1000)
。现在，我们把这个结果作为
DATE_FORMAT()
的第一个参数，第二个参数就是你想要的格式字符串。
一些常用的格式化示例：
标准日期时间（年-月-日 时:分:秒）:
SELECT DATE_FORMAT(FROM_UNIXTIME(timestamp_ms / 1000), '%Y-%m-%d %H:%i:%s') AS standard_datetime FROM your_table;
只显示日期（年/月/日）:
SELECT DATE_FORMAT(FROM_UNIXTIME(timestamp_ms / 1000), '%Y/%m/%d') AS just_date FROM your_table;
只显示时间（时:分:秒）:
SELECT DATE_FORMAT(FROM_UNIXTIME(timestamp_ms / 1000), '%H:%i:%s') AS just_time FROM your_table;
带有星期几和AM/PM:
SELECT DATE_FORMAT(FROM_UNIXTIME(timestamp_ms / 1000), '%W, %M %D %Y %r') AS verbose_datetime FROM your_table;-- 示例输出: Wednesday, March 15th 2023 08:00:00 AM
选择合适的格式取决于你的应用场景。比如，如果是在报表里展示，你可能希望日期格式更直观；如果是日志分析，精确到秒甚至毫秒（虽然MySQL默认只到秒，但你可以在应用程序层面处理毫秒部分）可能更重要。理解这些格式化符号（如
%Y
代表四位年份，
%m
代表两位月份等）是掌握
DATE_FORMAT
的关键。
处理时间戳时可能遇到的常见问题及解决方案
在实际操作中，转换时间戳远不止一个函数调用那么简单，总会有些“坑”等着你。
一个我经常碰到的问题是数据质量。如果你的
timestamp_ms
字段里混入了非数字字符，或者干脆是
NULL
，
timestamp_ms / 1000
这个操作就可能报错，或者返回
NULL
。对于这种情况，你可以考虑在查询前进行数据清洗，或者使用
IFNULL
、
COALESCE
等函数来处理
NULL
值，甚至用
CAST
或
TRY_CAST
（MySQL 8+）来尝试转换，如果失败则返回
NULL
。比如，
SELECT FROM_UNIXTIME(CAST(timestamp_ms AS UNSIGNED) / 1000)
可以确保输入是数字类型，避免一些隐式转换的风险。
另一个值得注意的是时区问题。
FROM_UNIXTIME()
函数默认是根据MySQL服务器的时区来解释时间戳的。如果你的13位时间戳是UTC时间（这很常见，因为时间戳通常是无时区概念的），但你的MySQL服务器设置的是本地时区，那么转换出来的结果就会有偏差。这时，你需要明确地进行时区转换。例如，如果你的时间戳是UTC，而你希望在查询结果中看到的是北京时间（UTC+8），你可以这样做：
SELECT CONVERT_TZ(FROM_UNIXTIME(timestamp_ms / 1000), 'UTC', 'Asia/Shanghai') AS local_datetimeFROM your_table;
当然，前提是你的MySQL服务器已经加载了时区信息。
还有性能考量。如果你在一个非常大的表上频繁地进行
FROM_UNIXTIME(timestamp_ms / 1000)
这样的计算，尤其是在
WHERE
子句中，性能会是一个问题，因为这会阻止索引的使用。在这种情况下，更好的做法可能是在数据插入时就将其转换为
DATETIME
类型存储，或者在MySQL 5.7+版本中，可以考虑使用生成列（Generated Columns）来存储转换后的日期时间，这样既能保留原始时间戳，又能有一个索引友好的日期时间列供查询。
例如，创建一个生成列：
ALTER TABLE your_table ADD COLUMN created_at_dt DATETIME AS (FROM_UNIXTIME(timestamp_ms / 1000));
这样
created_at_dt
列会自动计算并存储，你可以像普通列一样对其进行索引和查询，大大提升了效率。当然，这取决于你的MySQL版本和具体需求。
以上就是MySQL时间格式处理 13位时间戳转日期格式的完整流程的详细内容，更多请关注创想鸟其它相关文章！
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 chuangxiangniao@163.com 举报，一经查实，本站将立刻删除。
发布者：程序猿，转转请注明出处：https://www.chuangxiangniao.com/p/12641.html
微信扫一扫 
支付宝扫一扫 
