MySQL日期格式化实战：DATE_FORMAT、STR_TO_DATE与时间戳互转全指南

做 MySQL 开发的人，早晚都要跟日期格式化打交道。今天查订单要按天分组，明天统计报表要按月汇总，后天同步数据又要把字符串翻回时间类型。这些场景绕来绕去，核心就是对DATE_FORMAT、STR_TO_DATE、UNIX_TIMESTAMP这几个函数要玩得转。这篇就把我在实际项目里常用到的日期格式化方案整理成一套，从最简单的格式串到分组统计、从时间戳互转到时区坑点，一篇讲透，方便大家直接抄作业。

1. 日期格式化的先想清楚再动手

很多人拿到需求上来就写DATE_FORMAT(create_time, '%Y-%m-%d')，这没问题，但只解决了“显式格式化”这一半需求。完整的日期处理链条其实包括三段：解析、计算、显示。想清楚当前卡在哪一段，再去挑函数，往往能少走弯路。

1.1 三个函数各管一段，别混着用

• STR_TO_DATE负责解析：把外部传进来的字符串（比如'2024-01-15 08:30:00'）转成 DATETIME 类型，存入数据库或参与后续比较。
• DATE_FORMAT负责显示：把 DATETIME 或 DATE 类型的值，按指定格式串转成字符串，用于接口返回或报表展示。
• DATE_ADD/DATE_SUB/DATEDIFF负责计算：在日期类型上做加减或求差值，生成统计区间。

我见过不少同事把字符串截断、拼接当日期处理，比如LEFT(create_time, 10)取日期然后拿去分组。数据量小看不出问题，一旦上百万行，这种写法既吃 CPU 又容易踩隐式转换的坑。日期就是日期，该用日期函数就用日期函数，这个是原则问题。

1.2 格式化串必须前后端约定一致

项目里最容易出现的一类 bug 是：前端期望'YYYY-MM-DD HH:mm:ss'，后端用了'%Y-%m-%d %H:%i:%s'；或者报表系统期望'yyyy-MM-dd'，SQL 里写成了'%y-%m-%d'。同样是四位年份，Java 写YYYY，MySQL 写%Y，两边的语义可能并不一致，这点微妙的差异往往只在年底跨年时才会被引爆——YYYY在某些实现里会按“周所在年”解释，导致 12 月 31 日被格式化成下一年的日期。

我的习惯是：在项目初始化阶段，就把日期格式字符串统一成一张映射表，写进团队规范文档。后端 SQL 里全用%Y-%m-%d %H:%i:%s，接口层如果需要别的样式，再在代码里转换，绝不在 SQL 层各写各的。这样排查问题时，至少能先排除“格式串不统一”这类低级干扰项。

1.3 分清存储类型再做格式化

还有一个经常被忽视的细节：字段本身是什么类型，决定了你能不能用某个函数。DATE_FORMAT接收 DATE、DATETIME、TIMESTAMP 都行，但如果字段是 VARCHAR 且里面存的是'2024/01/15'这种非标准写法，直接格式化会出现意外结果，得先用STR_TO_DATE转一次。反过来，如果字段是 DATE 类型，你偏要显示时分秒，那格式化结果就是00:00:00，也符合预期。

我建议在表设计阶段就想清楚：纯日期用DATE，带时间的业务流水用DATETIME，需要自动更新或用 UTC 存储的场景用TIMESTAMP。类型选对了，格式化只是顺手的事。

2. DATE_FORMAT 格式串参数逐项拆解

DATE_FORMAT的格式串由一个或多个格式符组成，每个格式符前都要带百分号。我把实际开发里真正用得上的格式符整理成了一张表，并标注了容易出错的点。

2.1 格式符速查表

2.2 最常用的三种组合

• 纯日期：DATE_FORMAT(create_time, '%Y-%m-%d')，输出2024-06-15。
• 日期加时间：DATE_FORMAT(create_time, '%Y-%m-%d %H:%i:%s')，输出2024-06-15 14:30:25。
• 紧凑型（文件名或日志前缀用）：DATE_FORMAT(create_time, '%Y%m%d%H%i%s')，输出20240615143025。

我建议把这三个组合直接定为团队内通用标准，其他样式按需现拼，不必全记，但至少要知道分用的是%i，不是%m。这个错踩的人最多，%m取的是月份，放分钟位置上数据全乱。

2.3 可读性优化：别返回纯数字星期

接口返回的数据如果要给前端直接展示，我一般会再做一层可读化处理。比如星期几：

SELECT DATE_FORMAT(create_time, '%Y-%m-%d') AS day, CASE DATE_FORMAT(create_time, '%w') WHEN '0' THEN '周日' WHEN '1' THEN '周一' WHEN '2' THEN '周二' WHEN '3' THEN '周三' WHEN '4' THEN '周四' WHEN '5' THEN '周五' WHEN '6' THEN '周六' END AS week_name FROM orders;

这种 CASE 写法虽然啰嗦，但胜在逻辑直白。如果项目里有多个地方要用，建议直接封装成存储函数或者干脆在 Java/Go 的服务层映射，SQL 层保持最小逻辑，方便索引下推。

3. STR_TO_DATE 字符串解析的正确玩法

STR_TO_DATE是把字符串变成日期类型的核心函数。数据导入、接口入参、Excel 批量上传，都离不开它。它能自动识别不少常见格式，但这个“自动”是有边界的，不讲清楚会容易掉进坑里。

3.1 标准式解析

SELECT STR_TO_DATE('2024-06-15 14:30:25', '%Y-%m-%d %H:%i:%s'); -- 输出：2024-06-15 14:30:25（DATETIME 类型）

格式串必须与字符串的结构相匹配。'2024/06/15'要解析，格式串写'%Y/%m/%d'。但 MySQL 对分隔符的容忍度其实不低，STR_TO_DATE('2024-06-15', '%Y/%m/%d')也能成功，因为对于日期部分（年月日）的解析，非数字字符会被视为可用分隔符。这里我不建议依赖这种“宽容”，数据来源不可控时，宁可先把分隔符统一替换。

3.2 解析失败会返回 NULL，极其隐蔽

这是STR_TO_DATE最重要也最容易被忽略的脾气：一旦格式对不上，它不报错，直接返回NULL。比如：

SELECT STR_TO_DATE('2024-02-30', '%Y-%m-%d'); -- 输出：NULL（2月没有30日）

2024 年 2 月只有 29 天，所以这里返回 NULL，并不抛异常。如果这条 SQL 用在INSERT ... SELECT里，就会出现目标字段为空但源数据非空的情况。而且NULL值会被继续带进计算或分组里，导致统计数据凭空少一块，排查时非常隐蔽。

我的习惯是：在数据导入的校验环节加一层过滤，先把解析结果为 NULL 的行捞出来：

SELECT raw_value FROM temp_import WHERE STR_TO_DATE(raw_value, '%Y-%m-%d') IS NULL;

先把脏数据暴露出来，再决定是丢掉还是人工处理，而不是任由 NULL 流进正式表。

3.3 乱入的时分秒秒杀你的分组

还有一种情况：字符串明明只给了日期，但解析出来带了 00:00:00，这在按小时分组的统计里会造成意外。比如统计某天各时段订单量，原始字段是'2024-06-15'，你直接GROUP BY结果就是一个时间段，和前端的时段筛选完全对不上。这时候要么用STR_TO_DATE时明确只解析日期部分，要么对时间部分做 CASE 归一处理：

SELECT HOUR(STR_TO_DATE(create_time, '%Y-%m-%d %H:%i:%s')) AS hour_bucket, COUNT(*) FROM orders GROUP BY hour_bucket;

4. 分组统计场景的实战写法

日期格式化用得最多的场景就是分组统计。按月、按周、按天、按小时，需求千篇一律，但写法上有很多细节能省下不少性能。

4.1 按天统计，直接对日期格式化

SELECT DATE_FORMAT(create_time, '%Y-%m-%d') AS day, COUNT(*), ROUND(SUM(amount), 2) AS total_amount FROM orders WHERE create_time >= '2024-06-01 00:00:00' AND create_time < '2024-07-01 00:00:00' GROUP BY DATE_FORMAT(create_time, '%Y-%m-%d') ORDER BY day;

这里特别说一下 WHERE 的写法：用>= 起始日和< 结束日，而不是BETWEEN '2024-06-01' AND '2024-06-30'。前一种写法天然覆盖 6 月 30 日 23:59:59 之后的数据，不会漏掉最后一秒的订单，而且这个区间条件是可以走索引的。BETWEEN边界值如果只写日期，隐含时间就是 00:00:00，一整天后面 23 小时 59 分 59 秒的订单全会被丢在统计窗口之外。

4.2 按周统计，有必要用 YEARWEEK

按自然周聚合，简单的做法是DATE_FORMAT(create_time, '%x-%v')，拿到的就是2024-25表示 2024 年第 25 周。其中%x是周所属的年份，%v是周序号，这组格式符搭配使用能避免元旦前后“周跨年”的问题。比如 2024 年 12 月 30 日是周一，它属于 2025 年第 1 周，用%Y-%u就会错误地统计到 2024 年。

SELECT DATE_FORMAT(create_time, '%x-%v') AS week_no, COUNT(*) FROM orders GROUP BY week_no ORDER BY week_no;

如果你要的是自然周（周一到周日），%u和%v都满足；如果要周日到周六，得用%U和%V。订单统计一般按周一作为起点更符合业务习惯，所以默认用%u/%v是合理选择。

4.3 按小时统计，HOUR 函数比 DATE_FORMAT 快

按小时分组，我一般不用DATE_FORMAT(create_time, '%Y-%m-%d %H:00:00')，而是拆成两步：日期用DATE(create_time)，小时用HOUR(create_time)，或者直接用DATE_FORMAT(create_time, '%Y-%m-%d')加HOUR(create_time)两个维度。

SELECT DATE_FORMAT(create_time, '%Y-%m-%d') AS day, HOUR(create_time) AS hour_no, COUNT(*) FROM orders WHERE create_time >= '2024-06-01 00:00:00' AND create_time < '2024-06-02 00:00:00' GROUP BY day, hour_no ORDER BY hour_no;

这里还有个细节：HOUR(create_time)返回的是数字 0-23，排序列不会出现'2024-06-01 24:00:00'这种脏值。如果用DATE_FORMAT拼%H:00:00再分组，字符串排序会更别扭，'10:00:00'会排在'9:00:00'前面，必须依赖具体数据库的排序规则，容易翻车。

4.4 月份统计的完整边界示例

按月统计，我推荐这种写法：起始时间是当月第一天零点，结束时间取下个月第一天零点。

SELECT DATE_FORMAT(create_time, '%Y-%m') AS month, COUNT(*) AS order_cnt, ROUND(SUM(amount), 2) AS revenue FROM orders WHERE create_time >= '2024-01-01 00:00:00' AND create_time < '2024-02-01 00:00:00' GROUP BY month;

如果你希望 SQL 自动跟上“当前月”，可以结合DATE_SUB动态算：

WHERE create_time >= DATE_SUB(DATE_FORMAT(NOW(), '%Y-%m-01'), INTERVAL 0 MONTH) AND create_time < DATE_ADD(DATE_FORMAT(NOW(), '%Y-%m-01'), INTERVAL 1 MONTH)

这段计算逻辑虽然写起来长了点，但语义直白：左闭右开区间，恰好覆盖一整个自然月。而且DATE_FORMAT(NOW(), '%Y-%m-01')自带日期拼接效果，比CONCAT(YEAR(NOW()), '-', MONTH(NOW()), '-01')要干净得多。

5. 时间戳与日期互转，附坑点总结

接口联调阶段，前后端经常会传递时间戳（Unix 秒级）。MySQL 里互转的活主要落在UNIX_TIMESTAMP和FROM_UNIXTIME两个函数上，但有几个细节非常容易踩坑。

5.1 日期转时间戳

SELECT UNIX_TIMESTAMP('2024-06-15 14:30:25'); -- 输出：1718447425

需要注意的是：这里存的是 UTC 秒还是本地时区的秒？UNIX_TIMESTAMP是把传入的时间当作服务器所在时区来解释，然后转成 UTC 秒数。如果数据库服务器的时区不是东八区，同一时间算出来的秒数就会不一样。这个在容器化部署或跨区域协同的项目里经常被忽视，一旦对不上，排错极耗时间。

5.2 时间戳转日期

SELECT FROM_UNIXTIME(1718447425); -- 输出：2024-06-15 14:30:25（按会话时区）

FROM_UNIXTIME转换结果受time_zone系统变量影响。如果应用连接的会话把time_zone设成了'+00:00'，那同一时间戳格式化出来会差 8 小时。排查这种问题时第一件事就是查会话时区：

SELECT @@global.time_zone, @@session.time_zone, NOW();

如果连NOW()都跟你本地时间对不上，那问题的根源基本就是时区配置，而不是 SQL 写法。

5.3 演示一个完整的统计同步场景

假设有个订单流水表order_log，ETL 同步过来的时间字段是字符串'2024-06-15 14:30:25'，而业务侧要按天统计且输出时间戳字段给下游。

SELECT DATE_FORMAT(STR_TO_DATE('2024-06-15 14:30:25', '%Y-%m-%d %H:%i:%s'), '%Y-%m-%d') AS day, UNIX_TIMESTAMP(STR_TO_DATE('2024-06-15 14:30:25', '%Y-%m-%d %H:%i:%s')) AS ts, COUNT(*) FROM order_log GROUP BY day, ts;

这种每个会话里反复出现两次STR_TO_DATE的写法性能略差，但胜在一目了然。数据量不大时无所谓，如果上了千万级，建议先预处理一次把字符串正式转成 DATETIME 列再加索引，查询侧只做格式化。

5.4 默认值别用0000-00-00 00:00:00

最后提醒一个老项目里常见的残局：建表时日期字段默认值写了'0000-00-00 00:00:00'，后续查询如果分组或格式化可能遇到无法预料的返回值。新版 MySQL 默认开启NO_ZERO_DATE模式，这种值基本写不进去了。如果你接手的是老库，先检查这批零值数据到底是什么状态，再决定是清洗掉还是置为NULL。带着零值日期做统计，十有八九会多出一行莫名其妙的分组结果。

6. 常见问题高频排查实录

这部分把我自己和团队在真实运维中踩过的几个典型坑整理成速查表，每条都是能用一条 SQL 直接验证的。

6.1 常见问题速查表

6.2 一个真实的“时间少 8 小时”排查过程

有次线上订单报表的对账接口突然和外部系统对不上，两边统计同一个时间区间，结果差了几百条数据。查日志发现外部系统传的时间戳按天做分组，而我们服务里存的时间却是 GMT 会话解析出来的。最终定位到是数据库连接串没加serverTimezone=GMT%2B8，导致 JDBC 连上后会话时区是 UTC，查出来和展示差了整 8 小时。

修复方式是显式设置：

SET time_zone = '+08:00';

或者在连接参数里直接指定。这类问题单靠 SQL 层很难一眼看出来，但如果你养成连接后先查一次NOW()的习惯，就能提前暴露。

6.3 性能排查：格式化会不会让索引失效

WHERE条件里如果写成DATE_FORMAT(create_time, '%Y-%m-%d') = '2024-06-15'，这个索引就废了——因为 MySQL 要先对每一行做格式化计算，才能拿结果去跟字符串比较。正确写法是范围比较：

WHERE create_time >= '2024-06-15 00:00:00' AND create_time < '2024-06-16 00:00:00';

这个理念在前面提到过很多遍，但值得再强调一次：能够用范围过滤的，就不要用格式化函数。函数作用在字段上，等于屏蔽了索引的有序性。

6.4 STR_TO_DATE 慢查询优化思路

如果非要在大表上写类似WHERE STR_TO_DATE(create_time_str, '%Y-%m-%d') > '2024-01-01'的查询，这个查询基本无法走索引。更好的方案是：

1. 在表上加一列 DATETIME 类型create_time_dt，导入时用触发器或在插入程序里同步解析完成。
2. 对这一列建索引，查询时用日期范围过滤。
3. 原有字符串列可以退役，或仅保留为原始凭证。

这种改动虽然要动表结构，但对于需要频繁按时间过滤的大表来说，收益远大于改造成本。纯靠 SQL 调优很难救回一个设计不合理的字段类型。

7. 几个组合级实战建议

上面把函数用法和避坑点都过了一遍，接下来分享几个我平时设计日期处理方案时的个人习惯。

7.1 统一出口：写一个函数包一层

团队内部如果 MySQL 用了公共库，可以做一个统一的格式化函数，把常用格式码映射成标准操作：

DELIMITER $$ CREATE FUNCTION FMT_DATETIME(dt DATETIME, fmt VARCHAR(20)) RETURNS VARCHAR(32) DETERMINISTIC BEGIN IF fmt = 'day' THEN RETURN DATE_FORMAT(dt, '%Y-%m-%d'); ELSEIF fmt = 'second' THEN RETURN DATE_FORMAT(dt, '%Y-%m-%d %H:%i:%s'); ELSE RETURN DATE_FORMAT(dt, '%Y-%m-%d %H:%i:%s'); END IF; END$$ DELIMITER ;

这样业务 SQL 就统一写FMT_DATETIME(create_time, 'day')，格式需求变更时改函数内部一处即可。比起到处散落'%Y-%m-%d'这种方式，后续维护会轻松很多。

7.2 日志表分区按天，格式化只是默认值

如果表数据量持续增长，日期字段通常就是分区键。建议使用 RANGE 分区配合TO_DAYS或YEAR+MONTH表达式，把每天的数据落到独立分区里。这种情况下查询时如果按create_time范围过滤，能直接做分区裁剪，格式化函数只用在 SELECT 展示层，不参与分区判断。

PARTITION BY RANGE (TO_DAYS(create_time)) ( PARTITION p20240601 VALUES LESS THAN (TO_DAYS('2024-06-02')), PARTITION p20240602 VALUES LESS THAN (TO_DAYS('2024-06-03')), ... );

分区逻辑和日期格式化是两件事，但放一起设计能显著提升查询速度。

7.3 关于闰秒、夏令时，业务上别过度设计

MySQL 存储的 DATETIME 本身不带时区，存进去是什么就是什么。TIMESTAMP 才会做时区转换。很多并发系统会为了“严谨”引入各种时区换算，但实际效果往往只是让排查更复杂。我的原则是：

• 纯内部系统：统一用服务器时区，不额外转换。
• 面向多地域用户：存储一律用 UTC 的 DATETIME，展示时按用户时区在服务层换算。
• 必须混用 TIMESTAMP 时，明确写入和读取之间的时区换算规则。

日期格式化本身不应承载业务时区逻辑，它只负责“显示层”的字符串样式。让格式化回归格式化，让时区逻辑回到时区模块，代码才不容易纠缠成一团。

7.4 日期格式化与缓存策略配合

报表查询如果频繁用到同一天的格式化字符串，可以考虑在服务层做结果缓存。数据库侧只负责提供原始数据和时间戳，格式化这种纯计算放在内存里，能显著降低重复 SQL 的压力。比如每天更新一次的日报接口，可以把 SQL 查询结果按日期键缓存，失效时间设为次日零点。这种方案对数据库的负担更小，也能避免深夜大量并发查库做同样格式化。

8. 最后补充一个实际体会

做日期处理这块，最容易被忽视的部分往往不是函数用法，而是对“时区”和“边界”的敏感性。我在实际操盘过的几个项目里，线上事故磨掉最多时间的，几乎都是边界问题——月底最后一天的数据漏了、跨年那一周统计重复、GMT 时间戳对不上东八区。每次排查到最后，都是底层日期表示和会话时区的设置问题。

所以我的建议是：开工前先花十分钟把库表的日期字段类型、连接时区、业务展示时区这三件事梳理清楚，在文档里写死一套规则。之后再遇到日期格式化的需求，就只是查格式串、拼 SQL 的体力活，不会再被隐藏的时区坑绊倒。

最后再分享一个小技巧：如果你要连续做多个日期判断，试着把格式串写进一个公共常量里集中管理，同时养成用EXPLAIN检查 WHERE 条件的习惯。一旦看到DATE_FORMAT或STR_TO_DATE出现在索引字段上，基本就可以确定要优化 SQL 了。日期函数本身没有错，错的是把它放在不该放的位置上。