悠悠楠杉
网站页面
MySQL时间戳转日期教程：13位时间戳格式转换的完整步骤
09/08
一、什么是13位时间戳？
在数据处理中，我们常会遇到两种时间戳：
- 10位时间戳：表示从1970年1月1日（UTC）开始的秒数
- 13位时间戳：精确到毫秒级，前10位代表秒，后3位为毫秒（如JavaScript默认生成的时间戳）
二、MySQL转换核心函数
1. FROM_UNIXTIME() 函数
MySQL原生支持通过FROM_UNIXTIME()将Unix时间戳转为日期，但该函数默认仅支持10位时间戳。对于13位时间戳需先做除法处理：
sql
SELECT FROM_UNIXTIME(1633046400123 / 1000) AS datetime;
结果：2021-10-01 00:00:00
2. 保留毫秒精度（MySQL 5.6+）
若需要保留毫秒，可使用DATE_FORMAT组合：
sql
SELECT
CONCAT(
FROM_UNIXTIME(1633046400123 / 1000),
'.',
LPAD(MOD(1633046400123, 1000), 3, '0')
) AS full_datetime;
结果：2021-10-01 00:00:00.123
三、完整转换步骤
步骤1：确认时间戳位数
sql
-- 判断是否为13位时间戳
SELECT LENGTH(CAST(1633046400123 AS CHAR)) AS timestamp_length;
步骤2：基础转换（不带格式）
sql
SELECT
1633046400123 AS origin_timestamp,
FROM_UNIXTIME(1633046400123 / 1000) AS standard_datetime;
步骤3：自定义日期格式
使用DATE_FORMAT指定输出格式：
sql
SELECT
DATE_FORMAT(
FROM_UNIXTIME(1633046400123 / 1000),
'%Y-%m-%d %H:%i:%s'
) AS formatted_date;
步骤4：时区处理（关键！）
MySQL默认使用系统时区，可通过CONVERT_TZ函数调整：
sql
SET time_zone = '+00:00'; -- 设置为UTC时区
SELECT
FROM_UNIXTIME(1633046400123 / 1000) AS utc_time,
CONVERT_TZ(
FROM_UNIXTIME(1633046400123 / 1000),
'+00:00',
'+08:00'
) AS beijing_time;
四、实际应用场景
场景1：批量转换数据表
sql
UPDATE user_logs
SET create_date = FROM_UNIXTIME(timestamp_col / 1000)
WHERE timestamp_col > 1000000000000;
场景2：查询时间范围
sql
-- 查询2023年的数据（13位时间戳范围）
SELECT * FROM orders
WHERE timestamp_col BETWEEN 1672531200000 AND 1704067199000;
场景3：与程序语言协作
php
// PHP生成13位时间戳存入MySQL
$timestamp = round(microtime(true) * 1000);
$sql = "INSERT INTO events (ts, content) VALUES ($timestamp, 'test')";
五、注意事项
- 性能影响：对大数据列做实时转换会降低查询速度，建议存储时直接分列存储
- 年份限制：MySQL的
FROM_UNIXTIME()有效范围为1970-2038年 - 溢出处理：超过
BIGINT最大值时需用字符串存储时间戳
通过以上方法，你可以轻松处理各种时间戳转换需求。建议在数据库设计阶段就明确时间存储格式，避免后期复杂的转换操作。
