1. 为什么 DATE_FORMAT 值得专门写一篇我做后端这些年最烦的不是复杂的 join反而是一些看起来很小的日期格式化需求。刚用 MySQL 那会儿统计报表总习惯在 Java 或 Python 代码里把日期拼成字符串后来发现两个问题一是每个业务方要的格式都不一样二是不管怎么拼SQL 里拿到的始终是一长串 datetime还得二次加工。直到我认真把 DATE_FORMAT() 用起来才意识到很多“重复造轮子”其实都可以省掉。DATE_FORMAT() 是 MySQL 官方提供的日期时间格式化函数作用是把 DATE、DATETIME、TIMESTAMP 类型的值转换成指定格式的字符串。比如SELECT DATE_FORMAT(NOW(), %Y-%m-%d)返回的是类似2024-05-26的字符串。这类需求在报表、日志分析、数据导出、接口开发里几乎每天都在出现。不需要死记硬背所有格式符但得知道它能干哪些事以及用的时候有哪些坑。这篇文章适合刚学 SQL 的新手、被各种日期格式折腾的后端同学还有写复杂统计报表的分析师。我会把函数语法、常用格式符、实战场景、性能坑和排查技巧串起来尽量做到读完能直接上手。1.1 我最早踩过的坑我第一次用 DATE_FORMAT 是在一个订单统计需求里当时要按天统计订单数。我傻乎乎地在 WHERE 条件里写了DATE_FORMAT(create_time, %Y-%m-%d) 2024-05-01结果数据对是没错但一查慢日志那条 SQL 扫了全表几十万订单量直接把数据库 CPU 打到了 70%。后来才明白DATE_FORMAT 用在 WHERE 字段上会让索引失效。这个坑我后面会专门展开说。1.2 格式化到底是在“翻译”什么数据库里的 DATETIME 类型本质上是一个按“年-月-日 时:分:秒”排列的数值结构不是直观给人看的字符串。DATE_FORMAT 做的就是“翻译”把内部的时间结构按照你指定的格式符翻译成一段字符串。拿%Y-%m-%d举例%Y表示四位年份%m表示两位月份%d表示两位日期中间的-是分隔符你可以换成/、.、甚至中文的年月日完全由你来定。理解这一点之后你会发现DATE_FORMAT 就是一个“格式翻译器”它本身不改写原始字段的值只是输出一个格式化后的结果。所以它也经常出现在 SELECT 列表、GROUP BY、ORDER BY 里用来让查询结果更符合业务方对日期展示的习惯。2. DATE_FORMAT 语法与格式化符号完全解读2.1 语法拆解基本语法就一行DATE_FORMAT(date, format)date要格式化的日期可以是 DATE、DATETIME、TIMESTAMP 类型的字段也可以是一个表达式比如NOW()、SYSDATE()、CURDATE()。format格式字符串由%加字母组成也可以包含普通字符作为分隔符。返回结果是字符串类型。如果第一个参数是 NULL那么结果就是 NULL如果格式符写错MySQL 不会直接报错而是会返回包含原格式符的字符串或者返回 NULL这个细节很容易让人懵。比如SELECT DATE_FORMAT(NOW(), %Y-%m-%d); -- 2024-05-26 SELECT DATE_FORMAT(NOW(), %Y年%m月%d日); -- 2024年05月26日 SELECT DATE_FORMAT(NOW(), %H:%i:%s); -- 15:30:45注意第二条中文可以直接写在格式字符串里这一点在生成中文报表标题时特别好用。2.2 常用格式符对照表这里给出一张我平时用得最多的格式符表建议直接收藏格式符含义示例假设 2024-05-26 15:30:45%Y四位年份2024%y两位年份24%m两位月份01-1205%c月份不带前导零5%d两位日期01-3126%e日期不带前导零26%H24小时制00-2315%h12小时制01-1203%i分钟00-5930%s秒00-5945%pAM 或 PMPM%W星期几完整英文名Sunday%a星期几缩写英文名Sun%M月份完整英文名May%b月份缩写英文名May%j一年中的第几天001-366147%w星期几0星期日6星期六0%u一年中的第几周以周一为第一天21%T完整时间 24小时制 HH:MM:SS15:30:45%r完整时间 12小时制带 AM/PM03:30:45 PM%f微秒六位000000其中%i是分钟不是月份。很多新手把%m当分钟结果把月份和分钟搞混这个必须小心。我在团队内部培训时总会反复强调这一点。2.3 格式串的拼接技巧格式串不一定只有一个格式符。你可以把它当成一个普通字符串自由插入连字符、斜杠、空格、冒号、中文等字符。比如SELECT DATE_FORMAT(NOW(), %Y/%m/%d %H时%i分%s秒); -- 2024/05/26 15时30分45秒在写日志表分区、报表文件名时这种自定义能力很方便。比如我要导出某个日期的数据文件名想带时间戳就可以直接在查询里把日期格式化好再拼上业务后缀省掉一层代码处理。3. 实战场景从报表到日志分析的典型用法3.1 按天、周、月做分组统计最常见的需求是按某个时间粒度做统计。以前你可能习惯把时间字段截成字符串再分组其实直接 GROUP BY DATE_FORMAT 就行SELECT DATE_FORMAT(create_time, %Y-%m-%d) AS day, COUNT(*) AS order_cnt, SUM(amount) AS total_amount FROM orders WHERE create_time 2024-05-01 AND create_time 2024-06-01 GROUP BY DATE_FORMAT(create_time, %Y-%m-%d) ORDER BY day;按周统计时用%x-%v这类格式符可以输出“ISO 周”对应的年份和序号避免跨年时出现 1 月 1 日被归到上一年的问题。按月统计则直接%Y-%m。这个分组粒度完全由格式串决定甚至可以用%Y-%m-%d %H做小时级统计。有个细节GROUP BY 后面可以直接写别名吗不同版本行为不太一样。MySQL 允许在 GROUP BY 中使用 SELECT 列的别名但为了避免歧义我习惯把完整的DATE_FORMAT(create_time, %Y-%m-%d)写在 GROUP BY 里这样也能兼容更多数据库。3.2 报表标题和固定格式输出业务方经常要导出的 Excel 文件名带日期比如“订单明细_20240526”。有人第一时间想到在应用层拼字符串但其实 SQL 也能直接做SELECT CONCAT(订单明细_, DATE_FORMAT(NOW(), %Y%m%d)) AS file_name;这种写法特别适合定时任务生成报表SQL 把结果查出来文件命名就顺带完成了。再比如银行对账单常用的格式20240526153045也能用DATE_FORMAT(NOW(), %Y%m%d%H%i%s)一键拼出来。3.3 结合 CONCAT 和条件判断有时候一个字段是日期一个字段是金额要拼成一行展示。DATE_FORMAT 可以先格式化再和字符串拼接SELECT CONCAT(DATE_FORMAT(pay_time, %Y-%m-%d), 支付 , amount, 元) FROM payments;还可以配合 IF 或 CASE 做条件展示例如判断是否工作日SELECT DATE_FORMAT(dt, %Y-%m-%d) AS date_str, IF(DATE_FORMAT(dt, %w) IN (0, 6), 周末, 工作日) AS day_type FROM calendar;3.4 处理字符串日期DATE_FORMAT 和 STR_TO_DATE 搭配很多业务表里日期被存成了 VARCHAR比如2024/05/26或2024-05-26 15:30:45。此时不能直接用 DATE_FORMAT因为 DATE_FORMAT 要求的入参是日期类型。需要先用 STR_TO_DATE 把字符串转成日期再格式化SELECT DATE_FORMAT(STR_TO_DATE(2024/05/26, %Y/%m/%d), %Y-%m-%d);这里STR_TO_DATE是 DATE_FORMAT 的反向操作它把字符串按指定格式解析成日期。这个组合在清洗脏数据时非常有用常见的场景包括从日志文件导入的时间需要统一格式、不同上游系统推送的日期字段格式不一致、接口文档要求输出 JSON 格式而数据库存的是 datetime。3.5 联表查询中的日期格式化联表查询里如果两边各有一个时间字段要比较是否在同一天直接用DATE(a.pay_time) DATE(b.create_time)会损失索引优势。但如果只是用来输出展示可以先格式化再输出SELECT DATE_FORMAT(a.pay_time, %Y-%m-%d) AS pay_day, DATE_FORMAT(b.create_time, %Y-%m-%d) AS create_day, ... FROM orders a JOIN payments b ON a.id b.order_id;这种用法只有在结果集已经确定比较小的时候推荐否则还是要先通过条件把数据范围缩小再格式化展示。4. 性能与索引不能只看表面4.1 WHERE 条件里的 DATE_FORMAT 会让索引失效这是 DATE_FORMAT 最容易被滥用的一点。很多人习惯在查询条件里写SELECT * FROM orders WHERE DATE_FORMAT(create_time, %Y-%m-%d) 2024-05-26;问题在于数据库为了判断每一行的create_time格式化后是否符合条件必须对create_time字段做函数运算导致这一列上的普通索引无法被使用。原因是索引存储的是原始值而这里需要的是函数加工后的值B 树里的顺序帮不上忙。数据量小还好数据量一大全表扫描的代价会非常明显。正确的做法是把查询条件改成范围区间SELECT * FROM orders WHERE create_time 2024-05-26 00:00:00 AND create_time 2024-05-27 00:00:00;这样既能命中索引又能覆盖当天的所有数据。如果你一定需要按小时或分钟做区间同样思路把起点和终点算好用原始字段做比较不要把函数包在字段外面。4.2 GROUP BY 格式化后的性能思考GROUP BY DATE_FORMAT(create_time, %Y-%m-%d) 同样是在字段上做函数计算无法利用索引直接进行分组。但这并不意味着不能用而是要分场景看数据量几千到几万统计需求跑得不频繁直接格式化分组方便第一问题不大。数据量百万级且需要频繁跑就要考虑物化冗余字段比如新增一个day_str字段在写入时提前算好或者在查询层面用日期范围先过滤再对少量数据格式化分组。如果需要按小时统计也可以用DATE_FORMAT(create_time, %Y-%m-%d %H)这种粗粒度格式化配合存储引擎压缩效果还行。我的原则是能先在条件里缩小范围就不要在全表上格式化和分组能用冗余字段就不要每次查询都做实时计算。4.3 MySQL 版本差异与替代方案DATE_FORMAT 在 MySQL 5.7 和 8.0 里语法完全相同行为也没有本质差异所以迁移基本不用改。但在需要性能或做大量日期转换时可以结合下面几个函数一起用DATE()从 DATETIME 里截取日期部分。TIME()从 DATETIME 里截取时间部分。YEAR()、MONTH()、DAY()提取单个部分性能上比格式化再比较更直接。EXTRACT(YEAR FROM dt)从日期中提取指定部分。STR_TO_DATE(date_str, format)把字符串按格式转成日期。UNIX_TIMESTAMP(dt)转为 Unix 时间戳。FROM_UNIXTIME(ts, format)把 Unix 时间戳格式化为字符串。如果你只需要年份用YEAR(create_time)比DATE_FORMAT(create_time, %Y)更直观也更容易被优化器识别。如果是做时间范围比较最好还是用UNIX_TIMESTAMP或者直接拿 datetime 字段和常量区间比避免函数出现在字段侧。5. 常见问题排查实录5.1 返回 NULL 的常见原因DATE_FORMAT 返回 NULL 一般有两个原因。一是入参本身就是 NULL。比如某张表的create_time允许为空格式化结果自然是 NULL。处理办法是用IFNULL或COALESCE给默认值SELECT IFNULL(DATE_FORMAT(create_time, %Y-%m-%d), 未知日期) FROM orders;二是入参是一个非法日期字符串。比如2024-02-30这样的值存在于 VARCHAR 字段中如果直接对它调用 STR_TO_DATE结果是 NULL。我遇到过不少上游系统写入脏数据的情况这时需要用DATE_FORMAT(STR_TO_DATE(...), ...)并配合 WHERE 条件先把非法值筛掉或者用NULLIF做兜底。5.2 12小时制和24小时制的坑%H是 24 小时制%h是 12 小时制。如果你用%h而没加%p晚上八点会显示成08早上八点也是08业务方很容易晕。完整的 12 小时制写法是DATE_FORMAT(NOW(), %Y-%m-%d %h:%i:%s %p) -- 2024-05-26 03:30:45 PM如果拿不准到底用哪种我建议默认用%H跨系统展示时不容易产生歧义。另外%T其实是%H:%i:%s的快捷写法%r是带%p的完整 12 小时写法知道这两个也能少写几个字符。5.3 格式化后排序不对有同学用ORDER BY DATE_FORMAT(create_time, %m-%d)希望看到一月到十二月的数据结果排序出来是01-10、01-11、01-02这种字典序而不是按日期自然顺序。因为 DATE_FORMAT 返回的是字符串字符串排序是按字符逐位比较的不是日期比较。解决方法有两种-- 方法一按原始字段排序 SELECT DATE_FORMAT(create_time, %m-%d) AS md FROM events ORDER BY create_time; -- 方法二在 ORDER BY 里使用能反映顺序的格式 SELECT DATE_FORMAT(create_time, %m-%d) AS md FROM events ORDER BY DATE_FORMAT(create_time, %Y-%m-%d);如果只要月份顺序还可以用MONTH(create_time)排序比格式化后再排序更稳。5.4 时区不一致DATE_FORMAT 按照数据库会话的时区来转换时间。如果应用服务器和数据库服务器时区不同同一个NOW()可能返回不同的小时。最稳妥的做法是统一数据库会话时区例如在连接串里指定time_zone08:00或者让业务层先把时间转成 UTC 再入库展示时再按业务时区格式化。排查时可以先执行SELECT global.time_zone, session.time_zone;如果发现会话时区没有设置而业务方需要的又是一个固定时区可以显式用CONVERT_TZ()转换后再 DATE_FORMAT。5.5 周边函数对照速查表有时不是 DATE_FORMAT 本身的问题而是选错了函数。这里列一张速查表目标推荐函数把日期格式化成字符串DATE_FORMAT把字符串解析成日期STR_TO_DATE获取日期部分DATE()获取时间部分TIME()提取年份YEAR()提取月份MONTH()在时间戳和日期之间转换FROM_UNIXTIME / UNIX_TIMESTAMPURL/JSON 里拼接展示DATE_FORMAT CONCAT6. 我的实操建议与最后提醒6.1 选择格式化粒度DATE_FORMAT 的好处是灵活但也因为灵活容易让人把格式串写得过于随意。我的建议是团队内部先定一套规范比如默认展示格式统一用%Y-%m-%d %H:%i:%s日期统一用%Y-%m-%d上传文件名统一用%Y%m%d%H%i%s。这样排查问题的时候大家看到 SQL 就知道输出长什么样不用每个查询都去猜。6.2 代码 vs SQL 处理日期的边界不要把所有日期格式化都塞进 SQL。某些场景在应用层处理更合适比如多语言环境的月份名、周几展示业务规则复杂的日期计算。SQL 负责数据筛选和聚合应用层负责展示这是我的一个主要原则。DATE_FORMAT 是给 SQL 查询结果生成字符串用的不要为了省事而让数据库承担过多计算压力。6.3 一些踏过坑之后才明白的事我后来把常用格式符做了一张速查表贴在工位旁边谁写 SQL 不确定了就直接对照。DATE_FORMAT 不是一个复杂函数踩坑大多来自对格式符含义的误解以及对索引条件的误用。真正熟练之后你会发现它就像一把瑞士军刀能在报表、日志、导出、接口各种地方快速派上用场。但也要记住能用在 SELECT 输出层的不要随便挪到 WHERE 条件里能用原始字段范围比较的就不要包一层函数强行格式化。这样数据库会感谢你业务方也会觉得你靠谱。