要将mysql中的13位毫秒级时间戳转换为标准日期格式，必须先将其除以1000转换为10位秒级时间戳，再使用from_unixtime()函数进行转换，例如select from_unixtime(your_13_digit_timestamp / 1000) as standard_datetime from your_table_name；若需自定义输出格式，可结合date_format()函数，如按年月日时分秒显示可使用date_format(from_unixtime(your_13_digit_timestamp / 1000), ‘%y-%m-%d %h:%i:%s’)；实际应用中需注意时区问题，建议统一存储utc时间并在应用层根据用户时区展示，同时确保时间戳字段使用bigint类型以避免溢出，对于大规模数据应避免在查询中对时间戳列使用函数导致索引失效，推荐将日期范围转换为时间戳后直接比较以提升性能，此外处理null值时可结合coalesce函数设定默认值，遵循这些方法可准确高效地完成时间戳转换并规避常见陷阱。
MySQL中将13位时间戳（毫秒级）转换为标准日期格式，核心在于将其转换为10位时间戳（秒级），再利用
FROM_UNIXTIME()
函数。简单来说，就是把你的13位时间戳除以1000，然后交给MySQL处理，它就能帮你准确地还原出日期和时间。
解决方案
我发现很多朋友在处理MySQL里的时间戳时，特别容易在13位和10位之间犯迷糊。我们日常看到的或者系统生成的很多时间戳，尤其是前端或者某些API返回的，往往是13位的，精确到毫秒。但MySQL内置的
FROM_UNIXTIME()
函数，它默认识别的是10位的Unix时间戳，也就是精确到秒的。所以，这中间就差了三个零。
要解决这个问题，其实非常直接，就是做个简单的除法。你需要将13位的毫秒级时间戳除以1000，把它降维成秒级，然后再使用
FROM_UNIXTIME()
。
假设你的13位时间戳字段叫做
your_13_digit_timestamp
，你可以这样进行转换：
SELECT FROM_UNIXTIME(your_13_digit_timestamp / 1000) AS standard_datetimeFROM your_table_name;
这条SQL语句会把
your_13_digit_timestamp
列中的每一个值都除以1000，然后通过
FROM_UNIXTIME()
函数将其转换成
'YYYY-MM-DD HH:MM:SS'
这样的标准日期时间格式。
如果你需要更精细的格式控制，比如只显示日期、或者只显示小时分钟，那就可以结合
DATE_FORMAT()
函数来用。这就像是给你的日期时间穿上不同的“衣服”。
SELECT DATE_FORMAT(FROM_UNIXTIME(your_13_digit_timestamp / 1000), '%Y-%m-%d %H:%i:%s') AS formatted_datetime_full, DATE_FORMAT(FROM_UNIXTIME(your_13_digit_timestamp / 1000), '%Y-%m-%d') AS formatted_date_only, DATE_FORMAT(FROM_UNIXTIME(your_13_digit_timestamp / 1000), '%H:%i:%s') AS formatted_time_onlyFROM your_table_name;
在实际操作中，确保你的13位时间戳字段的数据类型是
BIGINT
，这样才能完整存储那么长的数字。如果存成了
INT
，那肯定会溢出，数据就不对了。
为什么我的13位时间戳转换后总是不对？
这几乎是我在职业生涯中被问到频率最高的问题之一了。说实话，我刚开始接触这块的时候，也踩过不少坑。核心原因，就像前面提到的，就是单位不对等。你给MySQL的是毫秒，但它期待的是秒。
Unix时间戳（Unix timestamp）是一个很重要的概念，它通常指的是从1970年1月1日00:00:00 UTC（协调世界时）开始经过的秒数。所以，当你在MySQL里使用
FROM_UNIXTIME()
时，它就是按照这个“秒数”的定义去解析的。
如果你手头有一个13位的时间戳，比如
1678886400000
，它代表的是2023年3月15日00:00:00 GMT。如果你直接把它丢给
FROM_UNIXTIME()
，不除以1000，MySQL会把它当作一个非常非常遥远的未来时间，因为它会认为这是
1678886400000
秒。这简直是天文数字，转换出来的结果可能会是
55219-09-17 08:00:00
（具体取决于你的时区），这显然不是你想要的结果。
所以，当你发现转换出来的日期时间“不对劲”，或者“太未来了”，那八成就是忘了除以1000。这就像是你手上有一堆毫秒级的数据，但数据库只认秒，你不做这个单位换算，那结果肯定天差地别。别看这只是一个简单的除法，但它背后的逻辑是理解Unix时间戳的关键。
除了标准格式，我还能将时间戳转换为哪些日期格式？
MySQL在日期时间格式化方面提供了非常强大的功能，主要就是通过
DATE_FORMAT()
函数来实现。它允许你根据各种格式代码（format specifiers）来定义输出的日期时间字符串。这就像是给数据穿上各种定制的衣服，满足不同展示需求。
这里列举一些常用的格式代码，你可以随意组合：
%Y
: 四位年份 (e.g., 2023)
%Y
: 两位年份 (e.g., 23)
%m
: 两位月份 (01-12)
%c
: 月份 (1-12)
%d
: 两位日期 (01-31)
%e
: 日期 (1-31)
%H
: 两位小时 (00-23, 24小时制)
%H
: 两位小时 (01-12, 12小时制)
%I
: 两位小时 (01-12, 12小时制)
%I
: 两位分钟 (00-59)
%s
: 两位秒数 (00-59)
%f
: 微秒 (000000-999999) – 注意13位时间戳是毫秒，这个是微秒
%p
: AM或PM
%W
: 星期几的完整名称 (e.g., Monday)
%a
: 星期几的缩写 (e.g., Mon)
%j
: 一年中的第几天 (001-366)
结合
FROM_UNIXTIME(your_13_digit_timestamp / 1000)
，你可以玩出很多花样：
只获取日期部分 (YYYY-MM-DD):
SELECT DATE_FORMAT(FROM_UNIXTIME(your_13_digit_timestamp / 1000), '%Y-%m-%d') AS date_only;
只获取时间部分 (HH:MM:SS):
SELECT DATE_FORMAT(FROM_UNIXTIME(your_13_digit_timestamp / 1000), '%H:%i:%s') AS time_only;
获取带AM/PM的12小时制时间:
SELECT DATE_FORMAT(FROM_UNIXTIME(your_13_digit_timestamp / 1000), '%h:%i:%s %p') AS time_12_hour;
获取中文星期几:这需要一点技巧，通常是结合
ELT()
和
DAYOFWEEK()
函数，因为
DATE_FORMAT
本身不直接支持中文。
SELECT CONCAT(DATE_FORMAT(FROM_UNIXTIME(your_13_digit_timestamp / 1000), '%Y年%m月%d日 '), ELT(DAYOFWEEK(FROM_UNIXTIME(your_13_digit_timestamp / 1000)), '星期日', '星期一', '星期二', '星期三', '星期四', '星期五', '星期六')) AS formatted_chinese_date;
这个例子稍微复杂一点，但它展示了
DATE_FORMAT
的灵活性以及与其他函数结合的可能性。掌握了这些格式代码，你就拥有了对日期时间输出格式的完全控制权。
在实际应用中，处理时间戳转换有哪些常见陷阱和最佳实践？
实际开发中，时间戳转换远不止一个简单的除法那么轻松。我遇到过不少因为处理不当导致的数据混乱、时区错位甚至性能问题。这里我总结一些常见的陷阱和一些我个人认为的最佳实践。
常见陷阱：
时区问题： 这是最最常见的陷阱，没有之一。
FROM_UNIXTIME()
函数默认会使用MySQL服务器或当前会话的时区来解释时间戳。如果你的时间戳是UTC时间，但服务器设置的是CST（北京时间），那转换出来的结果就会有8小时的偏差。我见过很多系统，前端传的是UTC时间戳，后端直接存，展示的时候又没做时区转换，结果用户看到的时间总是“不对”。例子： UTC
1678886400
（2023-03-15 00:00:00 UTC）在东八区服务器上直接
FROM_UNIXTIME()
，会显示为
2023-03-15 08:00:00
。数据类型选择不当： 有些开发者习惯性地把时间戳字段定义为
VARCHAR
。这会带来两个问题：一是存储效率低，二是进行日期计算或排序时会非常麻烦，需要额外的类型转换，性能受损。NULL值处理： 如果你的时间戳字段允许为
NULL
，那么在执行转换时，
FROM_UNIXTIME(NULL)
会返回
NULL
，这通常是符合预期的。但如果你有特定的需求，比如希望
NULL
时间戳显示为某个默认值，就需要用
COALESCE()
或其他条件判断。大规模数据转换性能： 对于包含数百万甚至上亿行记录的表，在查询时对时间戳列进行实时的
FROM_UNIXTIME(timestamp / 1000)
转换，会带来显著的性能开销，因为它无法利用索引。
最佳实践：
明确时区策略：统一存储UTC时间： 我强烈建议数据库中所有时间戳都统一存储为UTC时间（无论10位还是13位）。这是国际化应用的标准做法，可以避免大量时区转换的麻烦。在应用层处理时区转换： 在向用户展示时，根据用户的时区偏好进行转换。MySQL也提供了
CONVERT_TZ()
函数，但通常在应用层处理更灵活。设置会话时区： 如果你的应用确实需要在MySQL层面处理时区，可以在连接数据库后，通过
SET time_zone = 'your_timezone';
来设置当前会话的时区。选择正确的数据类型：存储13位时间戳： 使用
BIGINT
类型。存储10位时间戳： 使用
INT
或
BIGINT
。直接存储日期时间： 如果你不需要时间戳的原始数字形式，并且主要进行日期时间查询和计算，那么直接使用MySQL的
DATETIME
或
TIMESTAMP
类型是更优的选择。它们有内置的日期时间函数，索引效率高，也更直观。例如，在数据写入前，就将13位时间戳在应用层转换成
DATETIME
格式再插入。索引优化： 如果你的查询经常涉及时间范围筛选（例如
WHERE your_13_digit_timestamp BETWEEN ... AND ...
），确保
your_13_digit_timestamp
字段上有索引。但要注意，如果查询条件里包含了
FROM_UNIXTIME()
这样的函数，索引可能就失效了。解决方案： 尽量避免在
WHERE
子句中对索引列使用函数。例如，如果查询某个日期范围，可以把日期范围转换为时间戳再进行比较：
-- 查找2023年3月15日当天的数据SELECT *FROM your_table_nameWHERE your_13_digit_timestamp >= UNIX_TIMESTAMP('2023-03-15 00:00:00') * 1000 AND your_13_digit_timestamp < UNIX_TIMESTAMP('2023-03-16 00:00:00') * 1000;
这样
your_13_digit_timestamp
上的索引就能被有效利用。
数据清洗和迁移： 如果你现有数据是13位时间戳且存储为
VARCHAR
或不规范，考虑进行一次性数据清洗和类型迁移。这可能涉及编写脚本批量转换和更新数据，确保数据质量。
处理时间戳，尤其是涉及到不同精度和时区时，确实需要多一份细心。但只要理解了其背后的原理，并遵循一些最佳实践，就能避免很多不必要的麻烦。
以上就是MySQL日期转换方案 13位时间戳转标准格式的完整教程的详细内容，更多请关注创想鸟其它相关文章！
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 chuangxiangniao@163.com 举报，一经查实，本站将立刻删除。
发布者：程序猿，转转请注明出处：https://www.chuangxiangniao.com/p/15604.html
微信扫一扫 
支付宝扫一扫 
