做后端开发、数据分析、运维的同学脑子里肯定都存过一张 MySQL 日期格式化格式符对照表。DATE_FORMAT() 是 MySQL 里最常用的日期时间格式化函数没有之一。今天我不给你列一堆官方文档截图而是把 DATE_FORMAT() 从函数签名、格式符、业务场景到性能坑一次性讲完顺便把所有我踩过的坑、排查过的慢查询一并整理出来。这篇文章适合刚接触 MySQL 的初学者也适合写过几年 SQL 却在某个时间字段上翻过车的老手。我见过太多人为了格式化一个日期反复查文档也见过因为DATE_FORMAT写进 WHERE 条件导致线上慢查询半小时的情况。真正把这个函数用好不是背几个格式符就完事还要知道它返回什么、什么时候能用、什么时候千万别用。1. DATE_FORMAT() 函数到底返回了什么1.1 函数签名与基本参数DATE_FORMAT() 的语法非常固定DATE_FORMAT(date, format)第一个参数是日期时间可以是 DATE、DATETIME、TIMESTAMP 类型也可以是能被 MySQL 正确转换的字符串比如2025-04-13、2025-04-13 14:30:00。第二个参数是一个字符串模板里面用%加字母的方式来表示年、月、日、时、分、秒等部分模板里的普通字符会原样输出。举个例子这是每个项目里几乎都会出现的写法SELECT DATE_FORMAT(NOW(), %Y-%m-%d %H:%i:%s) AS current_time;返回结果类似2025-04-13 14:30:00。注意这里%H是 24 小时制的小时如果你写成%h那下午两点会变成02:30:00。单独看好像没错但放到日志、订单详情里就会产生歧义所以我个人做正式报表时默认只用%H。另外参数顺序千万不能写反。我见过有人把DATE_FORMAT(%Y-%m-%d, NOW())当成合法写法结果直接报错。这个函数在设计上把“日期”放前面“格式”放后面记不住就多写两遍别靠感觉。如果第一个参数传了 NULL函数会安静地返回 NULL不会报错这一点下面讲空值处理时还会再提。1.2 返回值是字符串不是日期DATE_FORMAT() 的返回值是 VARCHAR 字符串这是理解这个函数最关键的一点。它做的事本质上就是把内部存储的日期时间值“翻译”成人类更容易读的文本。你可以把它类比成 Excel 里的单元格格式设置数据本身没变只是在显示层面换了个样子。但正因为返回值是字符串很多人在后续操作里会踩坑。比如把格式化后的字符串拿去做日期比较SELECT * FROM t WHERE DATE_FORMAT(create_time, %Y-%m-%d) 2025-04-13;这句话语法没错结果也可能对但它在查询条件里对索引列套了函数性能代价很高。我后面会专门讲这个坑。所以我在实际工作中给 DATE_FORMAT() 的定位非常明确它是“展示层工具”不是“过滤层工具”。要过滤时间直接用原始 datetime 字段做范围比较要展示再用 DATE_FORMAT 加工字符串。这样职责清晰也避免了一堆莫名其妙的性能问题。2. 格式符全解析一张表记住所有占位符2.1 日期部分年份、月份、日DATE_FORMAT() 看起来难记是因为格式符数量多。其实拆开就三块日期、时间、星期/周数。先看日期部分我用2025-04-13作为示例日期格式符说明示例输出%Y四位年份2025%y两位年份25%m月份两位补零04%c月份不补零4%M月份英文全称April%b月份英文缩写Apr%d日两位补零13%e日不补零13%D日英文序数后缀13th%j一年中的第几天001-366103%d和%e的区别就是补不补零。中文报表里我基本只用%m和%d因为补零之后字符串排序不会乱%c和%e不补零输出2025-4-1这种格式肉眼看着简洁但排序和拼接都会埋雷。%D适合做英文界面能拼出April 13th, 2025这种效果中文环境基本用不到。%j是“一年中的第几天”千万不要把它理解成“第几周”。比如2025-04-13是第 103 天和“第 15 周”完全不是一回事。这里还要提一个最容易混淆的点%M是月份英文全称%m才是两位数字月份。很多人写年份月日时手一抖写成%Y-%M-%d结果出来2025-April-13在报表里非常突兀。如果要做成2025-04-13必须用%Y-%m-%d。2.2 时间部分时、分、秒时间部分的格式符同样有容易踩坑的位置。先看表格示例时间是14:30:45格式符说明示例输出%H24 小时制两位补零14%k24 小时制不补零14%h12 小时制两位补零02%I12 小时制两位补零02%l12 小时制不补零2%i分钟两位补零30%s / %S秒两位补零45%pAM / PM 标记PM%r12 小时制完整时间02:30:45 PM%T24 小时制完整时间14:30:45%f微秒6 位数字123456这里最迷的就是%i。MySQL 里分钟是%i不是%M也不是%m。记忆方法我一般推荐分钟英文 minute 里有个 i所以%i是分钟秒英文 second 首字母是 s所以%s是秒。只要把%H:%i:%s当成一个固定组合来记基本不会错。%H和%h也很容易出错。%H是 24 小时制%h是 12 小时制。如果你把凌晨零点格式化%h会显示 12既有可能是夜里 12 点也有可能是中午 12 点。%r是 12 小时制并带 AM/PM%T等价于%H:%i:%s。需要微秒时比如查接口耗时日志就可以用%fSELECT DATE_FORMAT(2025-04-13 14:30:45.123456, %H:%i:%s.%f); -- 结果14:30:45.1234562.3 星期、周数与自然语言输出做统计报表的人经常会按“自然周”汇总数据这时候会用到星期和周数相关的格式符。示例日期用2025-01-05星期日格式符说明示例输出%W星期英文全称Sunday%a星期英文缩写Sun%w数字星期0周日6周六0%U一年中的第几周00-53周日作为一周开始01%u一年中的第几周00-53周一作为一周开始01%V一年中的第几周01-53周日开始配合 %X01%v一年中的第几周01-53周一开始配合 %x01%X周数对应的年份四位周日开始2025%x周数对应的年份四位周一开始2025%W和%a返回的都是英文星期做国际化报表有用中文环境里直接用%w拿数字星期反而更方便。%U和%u的区别在于一周从哪天开始算%U从周日开始%u从周一开始。如果你做打卡、排班系统通常用周一开始的%u更符合国内业务习惯。%V、%v、%X、%x是一组主要用于需要“该周属于哪一年”的场景。比如 1 月 1 日是周三那么它所在的 ISO 周的一部分其实跨越到前一年直接用%Y会“跨年”跨错这时就要用%x-%v或%X-%V配合。虽然平时用得不多但做周报统计时能救命。3. 从简单查询到真实业务场景3.1 SELECT 展示和数据导出DATE_FORMAT() 最常见的用途就是把数据库里的 datetime 字段格式化成业务需要的字符串。比如我曾经做过一个订单导出功能财务要求订单创建时间必须精确到秒、不能有毫秒、格式必须是yyyy-MM-dd HH:mm:ss。数据库里存的是 DATETIME如果直接查出来给 Java 处理每个字段都要经过 SimpleDateFormat 转一遍代码冗长还容易错。直接写 SQL 更稳SELECT order_no, DATE_FORMAT(create_time, %Y-%m-%d %H:%i:%s) AS create_time_text FROM order_info WHERE create_time 2025-04-01 00:00:00 AND create_time 2025-05-01 00:00:00;这种写法的优点是应用层拿到的直接就是标准字符串导出 CSV、Excel 非常省事。但要注意如果你在业务代码里还希望把它当成 Date 类型做加减运算那就别用 DATE_FORMAT 去替换原始字段否则拿到字符串后还得再解析一次反而多此一举。我个人的习惯是数据库字段保持原始 DATETIME 返回给后端只在 SQL 里用 DATE_FORMAT 生成一个xxx_text的别名供展示。这样既不影响程序的时间逻辑又满足了前端和导出需求两边都不耽误。3.2 按日、月、年维度做统计报表系统里“按天统计”是最刚需的场景。比如统计每天的订单数和销售额SELECT DATE_FORMAT(create_time, %Y-%m-%d) AS day, COUNT(*) AS order_cnt, SUM(amount) AS total_amount FROM order_info WHERE create_time 2025-01-01 00:00:00 AND create_time 2026-01-01 00:00:00 GROUP BY DATE_FORMAT(create_time, %Y-%m-%d) ORDER BY day;这个 SQL 看起来简单但有三个细节值得注意。第一GROUP BY后面既可以写完整的DATE_FORMAT(...)表达式也可以直接写别名day。MySQL 对 GROUP BY 别名的支持很宽松但为了兼容性和可读性我通常写完整表达式ORDER BY day用别名没关系因为排序是在分组完成后执行的。第二格式化月份和日期一定要用补零的%m、%d不要用%c、%e。比如统计月报时如果用%Y-%c输出的字符串会是2025-1、2025-10字符串排序时2025-10会排在2025-1前面报表顺序全乱。用%Y-%m则能稳定排成2025-01、2025-10。第三如果按天统计的数据量大比如千万级订单表GROUP BY DATE_FORMAT(...)会对每一行做一次字符串格式化CPU 开销非常明显。更合理的方案是先按时间范围把数据缩到最小再在需要展示的层面对结果集做格式化。尤其不要在大范围、全表上去做按天汇总否则很容易变成慢查询。3.3 在查询条件中使用 DATE_FORMAT典型案例后台管理系统里最常见的筛选条件是“查询某一天的数据”。很多新手会非常自然地写出SELECT * FROM order_info WHERE DATE_FORMAT(create_time, %Y-%m-%d) 2025-04-13;如果order_info表只有几千行这个 SQL 一点问题都没有。但一旦到百万级、千万级它就会让 create_time 字段上的索引形同虚设。因为 MySQL 必须先对每一行的 create_time 调用 DATE_FORMAT把结果转成字符串再去和常量比较这等于把有序的索引彻底废掉。正确且高效的写法是范围查询SELECT * FROM order_info WHERE create_time 2025-04-13 00:00:00 AND create_time 2025-04-14 00:00:00;这个写法能直接命中索引也能正确处理时间字段里可能存在的毫秒值。看到这里你应该明白了DATE_FORMAT 在 SELECT 里用是展示在 WHERE 里用是“自残”。这个案例也自然带出了下一个必须重点讲的话题性能。4. 性能与索引格式化时最容易被忽略的坑4.1 为什么在 WHERE 中使用 DATE_FORMAT 会导致索引失效MySQL 的 B 树索引之所以快是因为索引叶子节点按原始字段值有序排列。如果查询条件是create_time 2025-04-13 00:00:00 AND create_time 2025-04-14 00:00:00优化器能直接定位到符合范围的索引区间然后回表读取数据。但一旦改成DATE_FORMAT(create_time, %Y-%m-%d) 2025-04-13索引里的顺序就不再是优化器可以利用的顺序了因为每个 create_time 都要先经过函数转换转换后的字符串和索引里的原始 datetime 不是同一个顺序体系。最后优化器只能选择全表扫描或者扫描二级索引的所有叶子节点性能完全不可控。我调过的一个实际案例某后台查询页面筛选某天订单SQL 就是WHERE DATE_FORMAT(create_time, %Y-%m-%d) ...订单表有 800 万行这个查询跑了 11 秒。改成范围查询后执行时间降到了 0.02 秒。差别就是这么夸张。遇到这类慢 SQL第一步就是打开 EXPLAIN 看一眼EXPLAIN SELECT * FROM order_info WHERE DATE_FORMAT(create_time, %Y-%m-%d) 2025-04-13;通常type列是ALLkey列是 NULL这就是全表扫描的典型特征。而范围查询的type是rangekey是 create_time 索引名。记住这个特征以后排查慢 SQL 会快很多。4.2 替代方案范围查询、生成列索引、函数索引解决这类问题有三个方案。第一个方案是范围查询这也是我最推荐、最不需要动表结构的方案WHERE create_time 2025-04-13 00:00:00 AND create_time 2025-04-14 00:00:00注意这里不要写成create_time 2025-04-13 23:59:59。如果字段是 DATETIME(6) 或 TIMESTAMP(6)数据里可能存在23:59:59.999999用会漏掉这一行用 2025-04-14 00:00:00是严谨的半开区间写法。第二个方案是生成列索引。如果业务里“按天查询”非常高频又不想每次手写范围条件可以在表上加一个生成列把日期部分单独抽出来ALTER TABLE order_info ADD COLUMN create_date DATE GENERATED ALWAYS AS (DATE(create_time)) STORED, ADD INDEX idx_create_date (create_date);这样后续查询直接写SELECT * FROM order_info WHERE create_date 2025-04-13;生成列是 MySQL 5.7 引入的能力MySQL 8.0 也支持。它会把计算好的 create_date 物理存储下来并且可以建索引查询性能接近原始字段范围扫描。第三个方案是 MySQL 8.0 的函数索引也就是直接对表达式建索引ALTER TABLE order_info ADD INDEX idx_date ((DATE(create_time)));函数索引在 MySQL 8.0.13 之后可用但也不是所有表达式都支持。我实际项目中更倾向于生成列因为生成列语义更直观运维同学也能看得懂函数索引对版本和写法要求更高容易在升级、迁移时踩到兼容性坑。4.3 大数据量分组统计的取舍按天统计时如果数据量大到几千万行哪怕只做一次格式化也会消耗大量 CPU。我通常会把“缩小数据范围”前置到子查询中让 MySQL 先走索引把时间范围压缩成一个小集合再在结果集上做格式化。SELECT DATE_FORMAT(create_time, %Y-%m-%d) AS day, COUNT(*) AS cnt FROM ( SELECT create_time FROM order_info WHERE create_time 2025-04-01 00:00:00 AND create_time 2025-05-01 00:00:00 ) t GROUP BY day;这个写法看起来多包了一层子查询但实际执行计划里内层会优先利用索引范围扫描外层只需要处理一个月的数据格式化成本大幅下降。如果查询范围覆盖整年我会再考虑预聚合表比如每天凌晨跑一个任务把前一天的数据按天汇总到统计表业务查询直接查统计表连 GROUP BY 都不需要。这个思路其实和很多数据仓库层做数仓分层是一个逻辑核心原则是先缩小数据量再讨论格式化。5. 常见问题与排查技巧实录5.1 格式化后排序不对用%Y-%c-%e这种不补零的格式符做字符串排序会出现2025-10-13排在2025-9-3前面的问题。因为字符串比较是按字符顺序逐位比较2025-10小于2025-9。解决思路有两种第一种展示层用补零格式符也就是%Y-%m-%d、%H:%i:%s。补零后2025-09-03会正常排在2025-10-13前面。第二种真正的排序需求不要排格式化后的字符串直接排原始 datetime 列。分组统计场景可以写ORDER BY MIN(create_time)或者ORDER BY create_time放在 GROUP BY 外。简单来说DATE_FORMAT 负责“看”ORDER BY 负责“排序”两者尽量各干各的不要把格式化结果当成排序依据。5.2 格式符写反、脑子一团浆糊这是我见得太多的经典错误%M写成月份数字%i当成秒%s当成毫秒。给你一个固定的“完整体”模板直接抄作业DATE_FORMAT(now, %Y-%m-%d %H:%i:%s)中文习惯记法就是“年Y 月m 日d 时H 分i 秒s”。一旦你发现输出是2025-April-13不要怀疑函数坏了肯定是把%m写成了%M。一旦你发现分钟永远显示00大概率是%i写成了别的。这些低级错误用上面这个固定模板就能避免。5.3 格式化后少了 8 小时如果你用的是 TIMESTAMP 类型格式化后少 8 小时通常不是 DATE_FORMAT 的问题而是时区转换问题。TIMESTAMP 底层以 UTC 存储MySQL 返回时会根据会话变量time_zone转换成对应时区的时间。如果time_zone是00:00北京时间从凌晨 8 点就会变成 0 点DATE_FORMAT 只是把换算后的结果重新渲染了一遍。排查时先看SHOW VARIABLES LIKE time_zone;如果是SYSTEM再看操作系统时区。如果通过 JDBC 连接还要检查连接参数里的serverTimezone是否设置成了Asia/Shanghai。如果数据库存的是 DATETIME它本身不带时区信息通常不会出现少 8 小时的问题。所以遇到少 8 小时先不要急着在 SQL 里加上几个小时否则时区修好之后数据会变成多加 8 小时的错误结果。DATE_FORMAT 只负责“格式化”不负责“换时区”真正要做时区转换应该用CONVERT_TZ()。5.4 空值和非法日期返回 NULLDATE_FORMAT() 对空值和非法日期的处理很“安静”不报错但会返回 NULLSELECT DATE_FORMAT(NULL, %Y-%m-%d); -- NULL SELECT DATE_FORMAT(2025-02-30, %Y-%m-%d); -- NULL并产生警告在报表和服务端接口里NULL 可能导致前端显示null、导出 Excel 出现空行或者 Java 程序拿到 null 后出现 NPE。建议在展示层用 IFNULL 兜底SELECT IFNULL(DATE_FORMAT(create_time, %Y-%m-%d %H:%i:%s), ) AS create_time_text FROM order_info;如果业务上要求非法日期也能展示原始值可以用COALESCE或CASE WHEN处理。总之DATE_FORMAT 不是一个“宽容”的函数日期字符串如果本身不合法它会返回 NULL这一点在数据清洗时尤其要注意。5.5 常见问题速查表问题现象可能原因解决办法月份显示为 April 而不是 04用了 %M 或 %b用 %m分钟显示为 00 或秒显示为 0把 %i 和 %s 写反用 %H:%i:%s下午 14:30 显示为 02:30用了 %h 或 %I用 %H排序出现 9、10、11 混乱用了 %c、%e、%k、%l用 %m、%d或直接按原始日期排序WHERE 按日期筛选特别慢索引列套了 DATE_FORMAT改用范围查询、生成列或函数索引格式化结果少 8 小时TIMESTAMP 时区转换设置 time_zone / serverTimezone结果全是 NULL字段为空或日期非法IFNULL / COALESCE 兜底6. 与 DATE_FORMAT 配套使用的时间函数6.1 STR_TO_DATE格式化逆操作DATE_FORMAT 是把日期变成字符串STR_TO_DATE 则是把字符串变成日期。两者使用同一套格式符体系所以学会 DATE_FORMAT 之后STR_TO_DATE 基本等于白送。SELECT STR_TO_DATE(2025-04-13 14:30:00, %Y-%m-%d %H:%i:%s);我在导入 CSV 数据时经常用到它。CSV 里的日期字段往往是2025/04/13 14:30插入数据库之前需要统一成标准 DATETIMEUPDATE import_table SET create_time STR_TO_DATE(raw_time, %Y/%m/%d %H:%i) WHERE raw_time IS NOT NULL;需要注意的是 STR_TO_DATE 对格式匹配要求很严格如果字符串和格式不一致会返回 NULL。比如%H期望 24 小时制你给一个02:30 PM就解析不对得用%h:%i %p这种带 AM/PM 的格式。6.2 FROM_UNIXTIME时间戳格式化有些老表为了省空间直接存 INT 类型的秒级时间戳。这种字段不能用 DATE_FORMAT 直接格式化需要用 FROM_UNIXTIME 先把时间戳转成 datetime再进行格式化。FROM_UNIXTIME 的第二个参数和 DATE_FORMAT 完全一致SELECT id, FROM_UNIXTIME(create_ts, %Y-%m-%d %H:%i:%s) AS create_time FROM operation_log;反向操作是 UNIX_TIMESTAMP它可以把日期字符串或 datetime 转成秒级时间戳。这三者的关系是日期字符串 - UNIX_TIMESTAMP - 时间戳时间戳 - FROM_UNIXTIME - 日期字符串。DATE_FORMAT 可以作用在 FROM_UNIXTIME 的结果上也可以直接用 FROM_UNIXTIME 的格式化参数二选一就行。我一般直接用 FROM_UNIXTIME 的第二参数少包一层函数可读性更好。6.3 CONVERT_TZ 与 GET_FORMAT跨时区业务里DATE_FORMAT 需要配合 CONVERT_TZ 使用。比如把北京时间的订单时间转成 UTC 再格式化SELECT order_no, DATE_FORMAT(CONVERT_TZ(create_time, 08:00, 00:00), %Y-%m-%d %H:%i:%s) AS utc_time FROM order_info;CONVERT_TZ 依赖系统时区表如果返回 NULL通常是 MySQL 的时区数据没有加载需要用mysql_tzinfo_to_sql命令导入系统时区表或者直接指定08:00这种偏移形式。GET_FORMAT 则是一个返回固定格式模板的函数比如GET_FORMAT(DATE,ISO)返回%Y-%m-%dGET_FORMAT(DATETIME,EUR)返回%Y-%m-%d %H.%i.%s。你可以把 GET_FORMAT 的输出直接塞给 DATE_FORMATSELECT DATE_FORMAT(NOW(), GET_FORMAT(DATE, ISO));不过我的实际使用频率很低因为不同地区和场景的“标准格式”并不一样与其去记 GET_FORMAT 的枚举值不如直接写%Y-%m-%d %H:%i:%s更直白。GET_FORMAT 适合那些要严格符合某个区域标准的国际化项目日常开发不推荐绕一层。最后说点我自己的习惯。我在实际工作中不到万不得已不会在 WHERE 或 GROUP BY 里直接使用 DATE_FORMAT尤其不会拿它去做条件过滤我把它当成一个只负责“出场展示”的工具。真正存数据库的永远是原始 DATETIME要按天统计就在 SQL 里用 DATE_FORMAT 生成维度但必须先缩小时间范围。如果你要在一个月、一年的尺度上用日期字符串做排序格式符一定要用补零的那组也就是%Y%m%d、%H%i%s。格式化字符串看起来简单真正影响的是数据准确性、排序规则和查询性能这三样都比“少写一行代码”重要得多。