MySQL时间戳转日期教程：13位时间戳格式转换的完整步骤

---

一、什么是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')";

五、注意事项

1. 性能影响：对大数据列做实时转换会降低查询速度，建议存储时直接分列存储
2. 年份限制：MySQL的FROM_UNIXTIME()有效范围为1970-2038年
3. 溢出处理：超过BIGINT最大值时需用字符串存储时间戳

---

通过以上方法，你可以轻松处理各种时间戳转换需求。建议在数据库设计阶段就明确时间存储格式，避免后期复杂的转换操作。