悠悠楠杉
网站页面
MySQL时间戳处理指南：13位数字转日期格式的实用技巧
08/29
一、什么是13位时间戳？
在开发中，我们常遇到两种时间戳：
- 10位时间戳：表示从1970-01-01 00:00:00 UTC到现在的秒数（经典UNIX时间戳）
- 13位时间戳：精确到毫秒，前10位表示秒，后3位表示毫秒（常见于Java、JavaScript等语言生成的时间戳）
例如：1625097600000 表示 2021年6月30日 00:00:00 UTC（含毫秒部分）。
二、MySQL中的转换方法
1. 基础转换：截断毫秒部分
sql
SELECT FROM_UNIXTIME(1625097600000 / 1000) AS datetime;
输出：2021-06-30 00:00:00
通过除以1000将13位时间戳转为10位，再用FROM_UNIXTIME函数转换。
2. 保留毫秒精度（MySQL 5.6+）
sql
SELECT
FROM_UNIXTIME(FLOOR(1625097600123 / 1000)) AS base_time,
CONCAT(
FROM_UNIXTIME(FLOOR(1625097600123 / 1000)),
'.',
LPAD(MOD(1625097600123, 1000), 3, '0')
) AS full_datetime;
输出：base_time | full_datetime
-----------------|----------------------
2021-06-30 00:00:00 | 2021-06-30 00:00:00.123
3. 处理时区问题
若需显示特定时区时间，可结合CONVERT_TZ函数：sql
SET time_zone = '+00:00';
SELECT CONVERT_TZ(
FROM_UNIXTIME(1625097600000 / 1000),
'+00:00',
'+08:00'
) AS beijing_time;
输出：2021-06-30 08:00:00 # UTC+8时间
三、实战应用场景
1. 批量转换数据表字段
假设表logs包含13位时间戳字段event_time：sql
ALTER TABLE logs ADD COLUMN event_datetime DATETIME;
UPDATE logs SET event_datetime = FROM_UNIXTIME(event_time / 1000);
2. 高效查询优化
对时间戳字段建立函数索引（MySQL 8.0+）：sql
ALTER TABLE logs ADD INDEX idx_event_time ((FROM_UNIXTIME(event_time / 1000)));
四、常见问题解答
Q：为什么直接使用FROM_UNIXTIME(13位时间戳)报错？
A：MySQL的FROM_UNIXTIME仅支持10位时间戳输入，需先除以1000处理。
Q：如何生成13位时间戳？
A：在MySQL中可用：sql
SELECT UNIX_TIMESTAMP(NOW()) * 1000;
五、总结
通过FROM_UNIXTIME结合数学运算，MySQL能轻松处理13位时间戳转换。关键步骤包括：
1. 截取前10位（除以1000）
2. 按需处理毫秒和时区
3. 对高频查询字段建立索引
掌握这些技巧后，无论是数据分析还是日志处理，时间戳转换将不再成为瓶颈。
