要将13位毫秒级时间戳转换为可读日期，需先除以1000转为秒级Unix时间戳，再用FROM_UNIXTIME()函数转换为日期时间格式，若时间戳存储为字符串应先用CAST转为数字，为提升查询性能建议添加计算列并建立索引。
在MySQL中，将13位数字（通常代表毫秒级时间戳）转换为可读的日期格式，核心思路是将13位数字除以1000，使其变为标准的10位Unix时间戳（秒级），然后利用
FROM_UNIXTIME()
函数进行转换。
解决方案
要将存储为13位数字的毫秒级时间戳转换为MySQL的日期时间格式，你可以使用如下SQL语句：
SELECT FROM_UNIXTIME(your_13_digit_timestamp_column / 1000) AS formatted_datetimeFROM your_table_name;
这里，
your_13_digit_timestamp_column
是你存储13位时间戳的列名，
your_table_name
是你的表名。
FROM_UNIXTIME()
函数接受一个Unix时间戳（即从1970年1月1日00:00:00 UTC开始的秒数）作为参数，并将其转换为一个
DATETIME
或
TIMESTAMP
格式的值。由于我们的原始数据是毫秒级的，所以需要先除以1000，将其精确到秒，这样
FROM_UNIXTIME()
才能正确解析。
为什么我的13位数字时间戳转换后结果不对？
这确实是个很常见的“坑”，我个人就遇到过好几次。很多开发者，包括我自己，在初次处理这类数据时，会直觉地把13位数字直接扔给
FROM_UNIXTIME()
。结果呢？不是得到一个看似“古老”的日期，就是直接返回
NULL
或者一个完全不符合预期的值。
根本原因在于
FROM_UNIXTIME()
函数的设计。它期望接收的是一个标准的Unix时间戳，这个时间戳是以秒为单位的，通常是10位数字（例如
1678886400
代表2023年3月15日）。而我们手上的13位数字，比如
1678886400000
，它比10位数字多了三位，这多出来的三位正是毫秒。当你直接把
1678886400000
传给
FROM_UNIXTIME()
时，MySQL会把它当作一个巨大的秒数来处理，这个秒数可能已经远远超出了MySQL能够表示的日期范围，或者指向一个非常遥远的未来（或过去），导致转换失败或结果异常。
所以，解决之道非常简单粗暴，但行之有效：在调用
FROM_UNIXTIME()
之前，先将你的13位时间戳除以1000。这样，
1678886400000
就会变成
1678886400
，这正是
FROM_UNIXTIME()
所需要的格式。
-- 错误示例：直接转换13位数字，结果可能不正确或为NULLSELECT FROM_UNIXTIME(1678886400000);-- 正确示例：先除以1000，再转换SELECT FROM_UNIXTIME(1678886400000 / 1000);
如何将转换后的日期格式化成我需要的样式？
FROM_UNIXTIME()
默认输出的日期格式通常是
YYYY-MM-DD HH:MM:SS
。但很多时候，我们可能需要更精细或者更简洁的格式，比如只显示日期，或者只显示小时分钟，甚至带上星期几。这时，
DATE_FORMAT()
函数就派上用场了。它允许你根据自己的需求，灵活地定制日期时间的显示格式。
DATE_FORMAT()
的用法是：
DATE_FORMAT(date, format)
。其中
date
就是你通过
FROM_UNIXTIME()
转换出来的日期时间值，而
format
是一个包含各种格式化占位符的字符串。
结合我们之前13位时间戳的转换，完整的格式化SQL语句会是这样：
SELECT DATE_FORMAT(FROM_UNIXTIME(your_13_digit_timestamp_column / 1000), '%Y-%m-%d %H:%i:%s') AS full_datetime, DATE_FORMAT(FROM_UNIXTIME(your_13_digit_timestamp_column / 1000), '%Y年%m月%d日') AS date_only_chinese, DATE_FORMAT(FROM_UNIXTIME(your_13_digit_timestamp_column / 1000), '%H:%i') AS time_onlyFROM your_table_name;
这里列举一些常用的格式化占位符，你可以根据需要组合它们：
%Y
: 四位年份 (e.g., 2023)
%m
: 两位月份 (01-12)
%d
: 两位日期 (01-31)
%H
: 两位小时 (00-23)
%i
: 两位分钟 (00-59)
%s
: 两位秒 (00-59)
%W
: 星期几的完整名称 (e.g., Monday)
%m
: 月份的完整名称 (e.g., March)
%p
: AM或PM
通过
DATE_FORMAT()
，你可以轻松地将日期时间显示成任何你想要的格式，这在报表生成或者数据展示时非常有用。
处理时间戳时可能遇到的数据类型问题及性能考量
在实际操作中，除了转换逻辑本身，我们还需要考虑一些潜在的数据类型问题和性能影响，特别是当数据量很大的时候。
首先是数据类型。如果你的13位时间戳列是
BIGINT
类型，那恭喜你，直接除以1000通常不会有任何问题。但如果它被存储成了
VARCHAR
或者
TEXT
类型，那在进行数学运算（除法）之前，MySQL需要先将其隐式转换为数字类型。虽然MySQL通常能处理这种隐式转换，但在某些极端情况下（比如字符串中包含非数字字符），这可能会导致转换错误或者
NULL
值。一个更健壮的做法是，如果确定是字符串，先用
CAST(your_column AS UNSIGNED BIGINT)
进行显式转换，然后再除以1000。
-- 如果你的时间戳是字符串类型，建议先CASTSELECT FROM_UNIXTIME(CAST(your_string_timestamp_column AS UNSIGNED BIGINT) / 1000) AS formatted_datetimeFROM your_table_name;
其次是性能考量。在一个非常大的表上，如果在
WHERE
子句中对时间戳列应用了
FROM_UNIXTIME()
或除法操作，比如
WHERE FROM_UNIXTIME(timestamp_col / 1000) > '2023-01-01'
，那么MySQL将无法使用该列上的任何索引。这意味着数据库必须对表进行全扫描（
Full Table Scan
），这会显著降低查询性能。
我个人的建议是，如果你的应用需要频繁地根据日期范围查询这些时间戳数据，最好的办法是在数据库层面就将时间戳转换为
DATETIME
或
TIMESTAMP
类型，并存储在一个独立的列中。你可以通过以下几种方式实现：
数据导入/ETL阶段转换： 在数据进入MySQL之前就完成转换，或者在导入后执行一次性的大批量更新。
添加计算列（MySQL 5.7+）： 创建一个
DATETIME
类型的生成列（Generated Column），它会自动根据原始的13位时间戳计算并存储日期值。这样，你就可以在这个计算列上创建索引。
ALTER TABLE your_table_nameADD COLUMN converted_datetime DATETIME AS (FROM_UNIXTIME(your_13_digit_timestamp_column / 1000));-- 然后你就可以在 converted_datetime 列上创建索引了CREATE INDEX idx_converted_datetime ON your_table_name (converted_datetime);
应用层处理： 如果查询频率不高，或者数据量不大，那么在应用层进行转换也是一个可行的方案，将转换逻辑放在代码中处理，减轻数据库的负担。
总的来说，理解13位时间戳的本质，并正确地进行单位转换是关键。在此基础上，根据实际的业务场景和数据量，选择合适的数据类型处理和性能优化策略，才能确保你的系统既准确又高效。
以上就是MySQL时间戳转换方法 13位数字转日期格式的SQL实现的详细内容，更多请关注创想鸟其它相关文章！
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 chuangxiangniao@163.com 举报，一经查实，本站将立刻删除。
发布者：程序猿，转转请注明出处：https://www.chuangxiangniao.com/p/13078.html
微信扫一扫 
支付宝扫一扫 
