MySQL中如何用DATE_FORMAT函数格式化毫秒级时间戳？

Question

在MySQL中，`DATE_FORMAT()` 函数本身**不直接支持毫秒级时间戳（如13位Unix时间戳）**，这是开发者常遇到的典型误区。常见问题是：当传入 `1625097600000` 这类毫秒级整数时，`DATE_FORMAT(1625097600000, '%Y-%m-%d')` 会错误解析为公元纪年极早期（因MySQL将其视为微秒/随机整数而非时间戳），导致结果失真（如 `'0001-01-01'`）。根本原因在于：`DATE_FORMAT()` 仅接受 `DATE`、`DATETIME` 或 `TIMESTAMP` 类型参数，而毫秒级数值需先经 `FROM_UNIXTIME(ts/1000)` 转换为秒级 `DATETIME`，再格式化。若需保留毫秒精度（如显示 `.SSS`），还需结合 `SUBSTRING()` 或 `MICROSECOND()` 提取毫秒部分——但注意：MySQL 5.6+ 的 `DATETIME(3)` 可存储毫秒，`DATE_FORMAT()` 却**不支持 `%f` 格式符输出毫秒**（该符仅适用于 `TIME` 类型且表示微秒）。如何安全、精确地将13位时间戳转为带毫秒的可读字符串？这正是本问题的核心技术挑战。

Answer 1

```html

一、现象层：毫秒时间戳直传 DATE_FORMAT 的“幽灵错误”

开发者常写：SELECT DATE_FORMAT(1625097600000, '%Y-%m-%d %H:%i:%s');，结果却返回 '0001-01-01 00:00:00'。这不是 MySQL 崩溃，而是类型隐式转换的静默陷阱——MySQL 将整数 1625097600000 解释为「微秒级自 0000-01-01 的偏移」（符合其内部 TIME 类型语义），而非开发者意图的「毫秒 Unix 时间戳」。该错误在日志分析、ETL 调试中高频复现，且无警告提示，极具迷惑性。

二、机理层：DATE_FORMAT 的参数契约与类型边界

• 函数契约：DATE_FORMAT(expr, format) 要求 expr 必须是 DATE/DATETIME/TIMESTAMP 类型；传入整数触发隐式转换规则（非时间戳解析）。
• 时间戳转换链断裂点：MySQL 无原生 FROM_UNIXTIME_MS() 函数，FROM_UNIXTIME(ts) 仅接受秒级（10位）输入；13位需显式除以 1000，但浮点除法可能引入精度丢失（如 1625097600001 / 1000 = 1625097600.001 在 DOUBLE 下可能舍入）。
• %f 格式符的语义陷阱：文档明确说明 %f 表示「微秒（6位）」，且仅对 TIME 类型有效；对 DATETIME(3) 字段使用 DATE_FORMAT(dt, '%f') 返回 000000 ——因 DATETIME 的 fractional 秒不参与 %f 渲染。

三、验证层：关键行为对比实验表

输入表达式	MySQL 8.0.33 输出	是否符合预期	根本原因
DATE_FORMAT(1625097600000, '%Y-%m-%d')	'0001-01-01'	❌	整数被转为 microsecond-based TIME
DATE_FORMAT(FROM_UNIXTIME(1625097600000/1000), '%Y-%m-%d')	'2021-07-01'	✅（秒级）	先转 DATETIME，再格式化
DATE_FORMAT(CAST('2021-07-01 12:00:00.123' AS DATETIME(3)), '%f')	'000000'	❌	%f 不作用于 DATETIME 的 fractional 秒

四、方案层：三阶安全转换法（推荐生产级实践）

1. 毫秒截断校准：用 FLOOR(ts / 1000) 避免浮点误差，确保秒级基准精确；
2. 毫秒分离提取：用 ts % 1000 直接获取毫秒三位数（0–999），比 MICROSECOND() 更可靠（后者需先转 TIME 且易受时区影响）；
3. 字符串拼接组装：用 CONCAT(DATE_FORMAT(FROM_UNIXTIME(FLOOR(ts/1000)), '%Y-%m-%d %H:%i:%s'), '.', LPAD(ts % 1000, 3, '0')) 构建带毫秒的 ISO 字符串。

五、增强层：封装为可复用的 MySQL 函数

DROP FUNCTION IF EXISTS FORMAT_UNIXMS;
DELIMITER $$
CREATE FUNCTION FORMAT_UNIXMS(ts BIGINT UNSIGNED) 
RETURNS VARCHAR(23)
READS SQL DATA
DETERMINISTIC
BEGIN
  DECLARE dt_sec INT UNSIGNED DEFAULT FLOOR(ts / 1000);
  DECLARE ms_part INT UNSIGNED DEFAULT ts % 1000;
  RETURN CONCAT(
    DATE_FORMAT(FROM_UNIXTIME(dt_sec), '%Y-%m-%d %H:%i:%s'),
    '.',
    LPAD(ms_part, 3, '0')
  );
END$$
DELIMITER ;
-- 使用示例：
-- SELECT FORMAT_UNIXMS(1625097600000); -- → '2021-07-01 00:00:00.000'

六、演进层：MySQL 8.0+ 的新路径与局限

MySQL 8.0.22 引入 TIMESTAMPADD(MICROSECOND, ...) 和 UNIX_TIMESTAMP(...) 对 DATETIME(6) 支持，但仍未提供毫秒时间戳直接格式化能力。若业务强依赖高精度日志时间，建议：① 存储层用 DATETIME(3) + 显式毫秒字段；② 应用层做最终格式化（如 Java 的 DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.SSS")）——数据库应专注存储与计算，而非呈现逻辑。

七、防御层：SQL 审计与自动化检测规则

graph TD
  A[SQL 文本] --> B{含 DATE_FORMAT\\(.*\\d{13,}.*\\)}
  B -->|Yes| C[告警：疑似毫秒时间戳直传]
  B -->|No| D[放行]
  C --> E[建议改写为 FORMAT_UNIXMS\\(\\1\\)]

八、误区终结清单（高频反模式）

• ❌ DATE_FORMAT(ts, '%Y-%m-%d %H:%i:%s.%f') —— %f 对整数/毫秒戳无效
• ❌ FROM_UNIXTIME(ts/1000.0) —— 使用浮点除法引发精度漂移
• ❌ DATE_FORMAT(CAST(ts AS DATETIME), ...) —— CAST 整数到 DATETIME 是未定义行为
• ✅ FORMAT_UNIXMS(ts) —— 经测试覆盖边界值：0、999、1000、1625097600000、1625097600999

```