做 MySQL 开发的这几年十张业务表里头总有七八张存的是 DATETIME 或 TIMESTAMP。订单创建时间、用户注册时间、支付完成时间、登录时间……数据落库的时候全是“2024-11-06 14:32:08”这种带时分秒的原生格式。程序读出来没问题可一旦要给人看、给报表用这串东西就不够用了。这时候 MySQL 中的DATE_FORMAT()函数就是最顺手的工具。DATE_FORMAT()干的事就一句话把日期时间类型按你指定的格式转成字符串。它完全不接受默认输出而是按照 format 参数里的格式串来呈现比如你只想看“2024年11月06日”或者只看“14:32”这个时刻它都能给到。用得最多的场景是统计数据时按时间段分组——按天汇总订单量按小时看流量高峰按月对比销售额基本都靠它把时间字段“裁”到想要的粒度上。所以这篇文章适合谁适合刚接触 MySQL 的初级开发也适合那些写 SQL 总要现场翻文档的中级开发。我会把格式串挨个讲清楚把最容易记混的符号单拎出来对比再配合几个真实的统计场景。看完之后你不需要死记硬背可以把这篇当手卡用随时照着写。为什么很多人怕DATE_FORMAT因为格式串里全是%开头的小写字母长得又像容易晕。别担心这篇文章的核心目的就是把这些符号一个个拆开揉碎。1. 为什么 DATE_FORMAT 是报表统计里绕不开的工具1.1 原始时间戳在业务展示里的痛点举个具体的例子。订单表order_info有一个字段pay_time是 DATETIME 类型存的是支付时间。你想在后台管理页面展示“2024年11月06日 14:32”如果直接SELECT pay_time出来的结果是“2024-11-06 14:32:00”月份没有“月”字时分秒也去不掉。前端去处理当然也行但更省事的做法是 SQL 里直接格式化好接口层返回的就是现成字符串。再比如财务对账需要把每天的交易明细按“YYYYMMDD”这种紧凑格式导出给下游系统。原生输出是“2024-11-06”下游要求“20241106”中间差一个横杠。你可以在 SQL 里直接写DATE_FORMAT(pay_time, %Y%m%d)一步到位。这些场景都属于“输出的形状不对”本质是日期时间展示层的问题DATE_FORMAT解决的正是这一层。1.2 统计分组时 DATE_FORMAT 的真正价值比展示更刚需的场景是分组统计。业务方要“每个自然月的销售额”订单表里有几十上百万条记录每条pay_time都是一个精确到秒的时间点。不格式化怎么分组按pay_time直接GROUP BY会把同一秒的数据归一组几乎每条一组毫无意义。这时候你会写SELECT DATE_FORMAT(pay_time, %Y-%m) AS month, SUM(amount) AS total_amount FROM order_info WHERE pay_status 1 GROUP BY month;这里把pay_time先格式化成“2024-11”这种月粒度字符串再GROUP BY每个自然月一行。这个写法简单得让人以为 MySQL 天生就该这么写但真正把它用好后面还有一堆细节要注意——比如GROUP BY后面能不能直接用别名在 MySQL 里可以因为 MySQL 对GROUP BY别名的支持比标准 SQL 宽松。这个在后面讲坑的时候会细说。2. 格式串速查手卡最容易记混的几个符号刚用DATE_FORMAT的人最头疼的是记格式串。一堆%加字母有的大小写不同意思完全不同。这里我先把完整的对照表放出来方便你复制粘贴用然后再挑几个最容易出错的单独讲。2.1 完整格式符对照表格式符说明示例%Y四位年份2024%y两位年份24%m两位月份01-1211%c月份数字1-12无前导零11%M英文月份全称November%b英文月份缩写Nov%d两位日期01-3106%e日期1-31无前导零6%D英文序数日期6th%H24小时制00-2314%h12小时制01-1202%i分钟00-5932%s秒00-5908%S秒00-5908%f微秒000000-999999000000%pAM 或 PMPM%T等同%H:%i:%s14:32:08%r12小时制时间含AM/PM02:32:08 PM%W星期英文全称Wednesday%a星期英文缩写Wed%w星期几数字0为周日3%j一年中的第几天001-366310%U一年中的第几周周日为每周第一天45%u一年中的第几周周一开始45%V同上与%X搭配45%v同上与%x搭配45%X周所在的四位年份周日开始2024%x周所在的四位年份周一开始2024%%字面量%符号%这个表建议直接收藏。我自己在编辑器里做成了代码片段敲两个字符直接带出常用组省得每次手敲。2.2 易混淆项%m 和 %i%Y 和 %y初学者最典型的报错是把分钟写成%m。注意%m是“月份”%i才是“分钟”这个设计确实有点反直觉但 MySQL 就这么定的。我见过有同事写DATE_FORMAT(now(), %Y-%m-%d %H-%m)出来的结果是“2024-11-06 14-11”——分钟位置变成了月份整个时间看着就像是时区和月份混在一起。排查这种问题其实很快但如果你不知道%i的存在可能会反复确认是不是数据源出了问题。另一个容易混的是%Y和%y。%Y输出四位年份 2024%y输出两位年份 24。对应到日期解析函数STR_TO_DATE时如果用了%y来解析“24”按 MySQL 的规则69 年以前会被解析成 20xx70 年以后解析成 19xx也就是 24 会被解析成 2024。这个规则容易踩坑所以日常写 SQL 尽量统一用%Y做四位年份别省那两个字符。2.3 分隔符和字面量你真的需要转义 % 吗很多人在拼接格式串时不注意分隔符的统一。DATE_FORMAT的逻辑很直接除了%开头的格式符其他字符原样输出。所以你写%Y-%m-%d会得到“2024-11-06”写%Y/%m/%d会得到“2024/11/06”写%Y年%m月%d日会得到“2024年11月06日”。中文直接写进去没问题MySQL 不会拦你。如果业务数据里本身就有百分号比如你想输出“成功率: 99%”那格式串里要写%%来表示一个普通的%字符。不然 MySQL 会认为%后面跟的字符是一个格式符如果这个格式符不存在它可能会原样输出但行为在不同版本里有差异。规范起见遇到百分号就用%%转义。3. 实战按天、按月、按小时统计的正确写法前面铺垫了那么多终于到动手的环节。这一节我会拿一个模拟的订单表和一个模拟的访问日志表从头写几个查询把常见的统计需求都过一遍。3.1 订单表按天汇总别在 GROUP BY 里重复写表达式假设有一张订单表order_info主要字段包括id、pay_timeDATETIME、amountDECIMAL、pay_statusTINYINT。业务方要一份从 2024-10-01 到 2024-10-31 每天的交易总额和订单笔数。最直观的写法SELECT DATE_FORMAT(pay_time, %Y-%m-%d) AS pay_date, COUNT(*) AS order_cnt, SUM(amount) AS total_amount FROM order_info WHERE pay_time 2024-10-01 00:00:00 AND pay_time 2024-11-01 00:00:00 AND pay_status 1 GROUP BY pay_date ORDER BY pay_date;注意这里有两个细节。第一WHERE 条件里没有用DATE_FORMAT(pay_time)而是用了一个范围查询。这样做的好处是pay_time上的索引还能生效后面讲到性能取舍的时候再展开。第二GROUP BY用的是 SELECT 里定义的别名pay_dateMySQL 是允许的。如果你用的数据库是标准 SQL 严格模式别名的使用限制会多一些但在 MySQL 里这样写很常见。如果不想用别名就得把DATE_FORMAT表达式再写一遍GROUP BY DATE_FORMAT(pay_time, %Y-%m-%d)两种写法结果一样。我个人的偏好是优先用别名因为改格式串的时候只改一个地方不用同步改GROUP BY。3.2 按小时统计观察一天的流量高峰再来看访问日志。表access_log有字段access_timeDATETIME和user_id。想找出一天中哪个小时访问量最大传统 SQL 写法SELECT DATE_FORMAT(access_time, %H) AS hour_no, COUNT(*) AS cnt FROM access_log WHERE access_time 2024-11-11 00:00:00 AND access_time 2024-11-12 00:00:00 GROUP BY hour_no ORDER BY cnt DESC;这里返回的hour_no是“00”到“23”这样的两位字符串。因为是从 0 点开始编号排序时字符串排序和数值排序结果一致所以可以直接按cnt排次数。如果你想输出“10:00 - 11:00”这种人类友好的区间标签可以配合CONCATSELECT CONCAT(DATE_FORMAT(access_time, %H), :00 - , DATE_FORMAT(DATE_ADD(access_time, INTERVAL 1 HOUR), %H), :00) AS hour_range, COUNT(*) AS cnt FROM access_log WHERE access_time 2024-11-11 00:00:00 AND access_time 2024-11-12 00:00:00 GROUP BY DATE_FORMAT(access_time, %H) ORDER BY hour_range;这个写法里嵌套了DATE_ADD后文会专门讲它和DATE_FORMAT的配合。3.3 没有数据的日期也要补 0先造一个日期序列按天统计最常踩的坑不是 SQL 写错而是“某一天没有订单那行直接消失了”。业务方看报表时10 月 3 日没有订单他期望看到一行“10月3日0 笔0 元”而不是表格直接少一行。解决方案是准备一张日期维度表或者现场用递归 CTE 生成日期序列然后左连接统计结果。MySQL 8.0 里可以这样生成一个月的日期序列WITH RECURSIVE date_range AS ( SELECT 2024-10-01 AS d UNION ALL SELECT DATE_ADD(d, INTERVAL 1 DAY) FROM date_range WHERE d 2024-10-31 ) SELECT d AS pay_date, COALESCE(t.order_cnt, 0) AS order_cnt, COALESCE(t.total_amount, 0) AS total_amount FROM date_range LEFT JOIN ( SELECT DATE_FORMAT(pay_time, %Y-%m-%d) AS pay_date, COUNT(*) AS order_cnt, SUM(amount) AS total_amount FROM order_info WHERE pay_time 2024-10-01 AND pay_time 2024-11-01 AND pay_status 1 GROUP BY pay_date ) t ON t.pay_date d ORDER BY d;这里有两个关键点。一是递归 CTE 生成日期序列二是子查询里的DATE_FORMAT结果和日期维度串d直接等值匹配。因为两边都是YYYY-MM-DD格式字符串比较没问题。COALESCE把没匹配到的统计结果补成 0。这个技巧在实际报表里非常常用值得单独记一下。4. 不只是格式串DATE_FORMAT 的几个隐性坑这一节要讲的是我在实际开发中反复踩过的坑。DATE_FORMAT本身很简单但一旦落到生产环境里各种边界情况就来了。4.1 NULL 与非法日期的处理函数会静默返回 NULLDATE_FORMAT的第一个参数如果是 NULL结果一定是 NULL。这看起来像废话但很多新手写CASE WHEN DATE_FORMAT(created_at, %Y-%m) 2024-11 THEN 1 ELSE 0 END时会想当然地认为 NULL 日期会落入 ELSE 分支结果整行都变成 NULL。要拿到“日期为空”的记录应该显式写created_at IS NULL或者用COALESCE给个默认值SELECT COALESCE(DATE_FORMAT(created_at, %Y-%m), 未知) AS create_month FROM user_info;还有一种情况是非法日期。比如你往DATE_FORMAT里传了一个值为2024-02-30的字符串MySQL 在严格模式下会直接报错非严格模式下会得到 NULL。这提醒我们在做数据清洗时对日期合法性要有预判不能想当然地认为数据库里的日期永远合法。4.2 传入字符串的隐式转换能用原生日期就别传字符串DATE_FORMAT的第一个参数支持字符串。这意味着你传2024-11-06 14:32:08也能正常格式化。但问题来了如果这个字符串的格式不标准MySQL 会尝试把它解析成日期解析失败时结果不可控。比如2024/11/6这种格式不同版本表现可能不同。更麻烦的是性能。如果对一个 VARCHAR 字段直接调用DATE_FORMATMySQL 需要先把每行的字符串转成日期再格式化这个转换发生在每一行上代价不小。如果字段本身就是 DATETIME直接格式化就行。所以设计表结构时能用 DATE、DATETIME 就别用 VARCHAR 存时间这是我排查慢查询时最常见的根因之一。4.3 WHERE 里用 DATE_FORMAT 导致索引失效替代写法很重要这是DATE_FORMAT在查询中最常见的性能陷阱。-- 低效写法 SELECT COUNT(*) FROM order_info WHERE DATE_FORMAT(pay_time, %Y-%m-%d) 2024-10-31;这条 SQL 的问题在于WHERE 条件把pay_time包在函数里MySQL 的优化器无法对这个字段做索引范围扫描只能全表扫描把所有行都格式化一遍再比较。数据量到百万级别时这条查询会慢得让人怀疑人生。替代写法是改成范围条件等价且能走索引SELECT COUNT(*) FROM order_info WHERE pay_time 2024-10-31 00:00:00 AND pay_time 2024-11-01 00:00:00;这个写法把“等于某一天”转换成“从当天零点到下一天零点之前”的半开区间。这是我在团队里反复强调的一个原则函数能不在 WHERE 的字段上套就不要套。DATE_FORMAT是用来做展示和分组的不是用来做过滤条件的。过滤条件用原生字段范围展示和分组再用DATE_FORMAT各司其职。4.4 时区与服务器时间报表差一天的最隐蔽原因DATE_FORMAT直接使用 MySQL 会话的时区设置。如果你的业务库时区是 UTC但订单时间是按北京时间存的那DATE_FORMAT(pay_time, %Y-%m-%d)得到的是 UTC 日期和用户看到的北京时间可能差一天尤其是晚上 8 点以后的订单UTC 日期已经是第二天了。排查这种问题时先执行SELECT NOW(), session.time_zone, global.time_zone;如果发现time_zone是00:00而业务需要08:00可以在连接初始化时执行SET time_zone 08:00;或者在 JDBC 连接串里加serverTimezoneAsia/Shanghai。这个坑跟DATE_FORMAT本身无关但凡是做日报表的人迟早会撞上建议提前检查环境配置。5. 性能取舍DATE_FORMAT 什么时候该让位的替代方案DATE_FORMAT看着轻巧其实它并不是一个廉价操作。这一节聊一下什么时候该用它、什么时候该考虑换方案。5.1 为什么 DATE_FORMAT 不便宜逐行函数运算的本质MySQL 里DATE_FORMAT属于逐行运算函数它对结果集中的每一行都要执行一次字符串格式化。这跟 SUM、COUNT 这种聚合函数不同聚合可以在扫描的同时累加而DATE_FORMAT要为每一行生成一个新的字符串对象。行数上万时感觉不出来百万、千万级时只为了把时间格式化成YYYY-MM-DD就要白白多花不少 CPU。如果你只是用DATE_FORMAT来对日期字符串做展示可以换个思路先在 MySQL 里把原生 DATETIME 查出来格式化的活儿交给程序语言。Java 的SimpleDateFormat、Python 的strftime做同样的事情用的资源是应用服务器上的DB 的压力能小一大截。当然如果查询结果集本身要 JOIN、要 GROUP BY那DATE_FORMAT反而是无法省掉的。5.2 高频报表场景用日期维表 汇总表代替实时格式化业务方每天要跑一份“近 30 天每日订单量”的报表如果每次跑都是全表DATE_FORMATGROUP BY数据库会很难受。更合理的架构是在业务低峰期比如每天凌晨跑一次定时任务把前一天的订单按天聚合后写入一张汇总表order_daily_summary。报表查询直接 SELECT 汇总表连DATE_FORMAT都不用写。这属于数据仓库里的“预聚合”思想对 MySQL 这种 OLTP 数据库尤其适用。DATE_FORMAT不是不能用于生产而是用在“低频、小结果集”的查询里没问题别让它成为高并发接口的瓶颈。5.3 如果实在想用加生成列是折中方案MySQL 5.7 及以上支持生成列。如果一张订单表经常要按天查可以加一个生成列来存格式化后的日期字符串ALTER TABLE order_info ADD COLUMN pay_date VARCHAR(10) GENERATED ALWAYS AS (DATE_FORMAT(pay_time, %Y-%m-%d)) STORED;生成列在插入数据时自动计算之后查询pay_date就是普通列可以加索引。这个方案适合“DATE_FORMAT的结果经常被用于 WHERE 或 JOIN”的场景既保留了格式化的便利又避开了逐行计算的开销。缺点是占一点存储空间数据一致性完全由 MySQL 保证不用应用层操心。6. 周边函数配合DATE_FORMAT 不是孤立存在的DATE_FORMAT经常和其他日期函数一起出现。这一节挑几个最常用的组合给大家一组现成模板。6.1 STR_TO_DATE把字符串解析回日期有格式化输出就有反向解析。STR_TO_DATE(str, format)是DATE_FORMAT的逆操作把符合特定格式的字符串变成 DATE 或 DATETIME。比如导入外部系统的 CSV 时日期字段可能是2024/11/06 14:32直接用STR_TO_DATE解析再入库SELECT STR_TO_DATE(2024/11/06 14:32, %Y/%m/%d %H:%i);注意这里分钟必须用%i跟DATE_FORMAT的规则一致。很多从业务系统导出的时间字符串还带毫秒比如2024-11-06 14:32:08.123解析时格式串要写成%Y-%m-%d %H:%i:%s.%f。这个函数在“mysql将字符串转为日期”这类需求里是主力。6.2 DATE_ADD 与 DATE_FORMAT时间段统计的配合DATE_FORMAT经常配合DATE_ADD实现多维度统计。比如按“每 15 分钟一个时段”统计订单量先把时间戳向下取整到 15 分钟网格SELECT DATE_FORMAT( DATE_ADD( 2000-01-01, INTERVAL FLOOR(TIMESTAMPDIFF(MINUTE, 2000-01-01, pay_time) / 15) * 15 MINUTE ), %Y-%m-%d %H:%i ) AS time_slot FROM order_info;思路是选一个远早于业务数据的基准时间计算pay_time与基准时间差了多少分钟除以 15 向下取整再乘回 15得到该时段起点。这种方式写起来略绕但性能比用SUBSTRING去截取分钟要可靠因为整个运算只涉及数值计算没有字符串匹配。6.3 DATE_FORMAT 与 DATE_SUB、LAST_DAY月初、月末的统计统计每月最后一天的订单可以先用LAST_DAY得到月末日期再配合DATE_FORMAT输出SELECT LAST_DAY(2024-11-06) AS last_day_of_month; -- 返回 2024-11-30再来一个常用组合查“上个月同一天的订单”可以用DATE_SUB配合INTERVAL 1 MONTH输出时再用DATE_FORMAT转换格式SELECT DATE_FORMAT(DATE_SUB(2024-11-06, INTERVAL 1 MONTH), %Y-%m-%d);这种组合适合固定报表里的相对日期计算。写的时候要注意INTERVAL的单位MONTH、DAY、HOUR 都属于关键字拼写不能错写错了 MySQL 会直接报语法错误提示倒是挺明确。6.4 存储过程里使用 DATE_FORMAT 的场景顺带提一句如果你在存储过程中需要构造游标查询或者要把时间字段拼进动态 SQL 里DATE_FORMAT也是很好的工具。比如拼接一个按天分表的表名SET table_name CONCAT(order_, DATE_FORMAT(NOW(), %Y%m%d)); SET sql CONCAT(SELECT * FROM , table_name); PREPARE stmt FROM sql; EXECUTE stmt;这种用法在分表分库的旧系统里很常见配合预处理语句可以避免反复解析 SQL。不过动态 SQL 的拼串场景要严格验证表名来源别把用户可控的参数直接拼进去这个安全意识一定要有。7. 最后分享几个我自己的使用习惯看了这么多规则其实DATE_FORMAT用久了会形成肌肉记忆。我最后把平时积累的几个小经验写在这里给各位一个参考。一是格式串写好后先在测试环境跑一遍确认输出结果和预期一致再上线。别小看这一步%m和%i写错这种问题在测试环境一眼就能发现省得上了生产再排查。二是字符串比较的隐式规则。DATE_FORMAT的结果是字符串如果你拿它和日期类型做比较MySQL 会做隐式转换。比如DATE_FORMAT(now(), %Y-%m-%d) 2024-11-06能正常比较但如果你写成DATE_FORMAT(now(), %Y-%m-%d) DATE(2024-11-06)两边类型不同引擎会尝试把字符串转日期一旦格式不对就可能出幺蛾子。稳妥的做法是保持两边都是字符串或者都转成日期比较。三是如果你恰好也在维护老版本 MySQL注意 8.0 之后DATE_FORMAT的格式符行为基本没变但函数性能在不同版本有细微差别升级后建议对核心 SQL 做一轮压测。我当年从 5.6 升 8.0 时就遇到一个历史报表查询计划变化的问题最后是靠加生成列解决的。四是DATE_FORMAT不能直接作用于时间戳的微秒部分。如果要保留微秒需要用到%f并且输入值必须是 DATETIME(6) 或 TIMESTAMP(6) 这类带精度的类型普通 DATETIME 取出来的微秒恒为000000。这个细节在日志系统对接时容易忽略。这一节不算什么系统知识更多是攒下来的习惯。做数据库相关的工作很多技能就是在这种小细节里慢慢积累的。希望这篇对你有用下次写DATE_FORMAT的时候就不用临时翻文档了。