做数据统计和业务报表的时候日期时间函数是绝对绕不开的一类SQL函数。业务表里十有八九都带着created_at、updated_at、birthday这类时间字段而按天、按周、按月做分组统计又是每张报表都躲不掉的需求。同一个需求不同人写出来的SQL性能有时候能差几十倍问题往往就出在日期函数的用法上。这篇文章我打算从实际干活的角度把MySQL里常用的日期时间函数按场景过一遍包括类型选型、格式化、日期计算、分组统计、时区和索引的坑中间会穿插我这些年踩过的一些实在问题。不管你是刚接触SQL的新人还是写了好几年业务的开发应该都能从这里拿点东西直接用。1. 日期类型怎么选用错类型后面全是坑1.1 三种日期时间的存储差异很多初学者上来就无脑用DATETIME这没问题但如果你不清楚DATE、DATETIME、TIMESTAMP三者的差别遇到时区问题和存储空间优化就会很被动。下面这张表是我在实际项目中总结的对比类型存储空间取值范围是否有时区概念DATE3字节1000-01-01 到 9999-12-31无只存日期DATETIME(fsp)5字节小数秒存储1000-01-01 00:00:00 到 9999-12-31 23:59:59.999999无按写入内容直接存储TIMESTAMP(fsp)4字节小数秒存储1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC有存储时转UTC查询时转会话时区重点说TIMESTAMP。它内部存的是UTC时间你插入一条记录MySQL会先把你给的时间按当前会话时区转成UTC然后存进去查询的时候再按会话时区转回来。这套逻辑在“全球部署用户分布在多个时区”的场景下非常方便但副作用就是范围有限2038年问题至今依然存在。DATETIME则完全没这个顾虑你存什么就是什么适合绝大多数业务系统作为“事件发生时间”来使用。fsp是可选的小数秒精度参数范围0到6。如果你的业务需要毫秒级精度比如订单取消时间精确到毫秒可以写成DATETIME(3)否则不要加省不了多少空间还容易让隐式转换问题更难排查。1.2 默认值设置与ON UPDATE陷阱建表时给时间字段设置默认值是一个极其常见但也有不少讲究的操作。从MySQL 5.6.5开始DATETIME也支持DEFAULT CURRENT_TIMESTAMP了早期版本里只有TIMESTAMP能这么干。一个我比较推荐的时间字段写法是CREATE TABLE user_order ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间 ) ENGINEInnoDB;这里ON UPDATE CURRENT_TIMESTAMP的含义是这一行的任何字段被UPDATE时updated_at自动刷新为当前时间。这很省心但有两点坑要提醒。第一如果某次业务更新确实不需要刷新更新时间你得手动在UPDATE语句里把updated_at赋值为自身比如SET updated_at updated_at否则它一定会变。第二DATETIME字段如果允许为NULL插入时你显式写了NULL它不会走默认值而是老老实实存NULL这容易让后续ORDER BY created_at DESC的排序结果和预期不一致建表时尽量把时间字段定义为NOT NULL。2. 取当前时间的函数别再只会NOW()2.1 NOW、CURRENT_TIMESTAMP、SYSDATE的差异日常写代码时获取当前时间最常用的是NOW()它可以不带括号写CURRENT_TIMESTAMP也可以写CURRENT_TIMESTAMP()这几个在MySQL里基本等价。但SYSDATE()是个例外它是函数真正执行时的时间NOW()是语句开始执行的时间。如果一条SQL里多条记录需要统一使用同一个“当前时间”NOW()是标准选择。我实际遇到过这样的问题一条长事务里执行UPDATE ... SET latest_login SYSDATE()因为每行执行时有细微时间差同一批更新的记录时间戳不一致后续排查数据时让人误以为有并发写入。更麻烦的是在基于二进制日志做数据同步的环境里SYSDATE()这种“不确定性函数”可能导致从库与主库时间基准不一致。所以我个人的经验是业务代码里统一用NOW()非特殊情况不碰SYSDATE()。2.2 只用日期和只用时间如果只需要“今天的日期”不需要时分秒可以用CURRENT_DATE()或CURDATE()返回类似2025-02-01。只要当前时间就用CURRENT_TIME()或CURTIME()返回14:30:25。UTC_TIMESTAMP()则可以拿到UTC标准时间适合需要和外部系统对时间的场景。这类函数在报表场景里经常作为“计算基准日”使用比如WHERE create_date CURDATE()表示查今天创建的记录。不过这里提前埋一个伏笔如果create_date字段是DATETIME类型那么 CURDATE()这种写法大概率会导致索引失效后面第6节我会专门讲怎么改成区间查询。2.3 从日期时间中抽取年月日时分秒从完整时间戳里取年、月、日、小时、分钟、秒最直观的就是YEAR()、MONTH()、DAY()、HOUR()、MINUTE()、SECOND()。SELECT YEAR(2025-02-01 14:30:25) AS year_part, MONTH(2025-02-01 14:30:25) AS month_part, DAY(2025-02-01 14:30:25) AS day_part, HOUR(2025-02-01 14:30:25) AS hour_part, MINUTE(2025-02-01 14:30:25) AS minute_part, SECOND(2025-02-01 14:30:25) AS second_part;结果分别是2025、2、1、14、30、25。这部分没什么花头但有两个函数容易搞混DAYOFWEEK()和WEEKDAY()。DAYOFWEEK(2025-02-01)返回2因为1代表周日WEEKDAY(2025-02-01)返回5因为0代表周一。如果你的业务是“每周一早上跑任务”判断条件应该写WEEKDAY(date_col) 0而不是DAYOFWEEK(date_col) 1这就是一个典型的“差一位”错误。EXTRACT(YEAR FROM date)和EXTRACT(YEAR_MONTH FROM date)也是抽取函数前者等同于YEAR()后者可以直接返回202502这种年月组合在某些老系统里常用来做分区字段我看过不少月分区的表就是用EXTRACT(YEAR_MONTH FROM create_time)来算分区键的。3. 格式化与解析DATE_FORMAT和STR_TO_DATE3.1 DATE_FORMAT报表展示的瑞士军刀业务侧通常需要把时间显示成“2025年02月01日 14:30”这种格式数据库里存的却是2025-02-01 14:30:25这时DATE_FORMAT()就是最常用的转换函数。它的核心是各种格式占位符我用顺手的基本是这几组占位符含义示例输出%Y四位年份2025%y两位年份25%m两位月份02%c月份不补零2%d两位日01%e日不补零1%H24小时制小时14%h12小时制小时02%i分钟30%s秒25%pAM/PMPM%W星期全名Saturday%a星期缩写Sat%M月份全名February%b月份缩写Feb%j一年中的第几天032日常统计里最常用的组合就是DATE_FORMAT(create_time, %Y-%m-%d)按天分组DATE_FORMAT(create_time, %Y-%m)按月分组DATE_FORMAT(create_time, %Y-%m-%d %H:00)按小时分组。我做流量报表时经常要输出“每分钟请求数”写的就是DATE_FORMAT(request_time, %Y-%m-%d %H:%i)直接在SQL里把时间点切到分钟粒度避免在应用层再循环处理SQL一次到位效率高很多。这里必须提醒一个初学高频坑%i是分钟%s是秒而%M是月份全名%m是两位月份。我见过同事把分钟写成%M结果时间显示成了“February”排查半天才反应过来。3.2 STR_TO_DATE反向解析字符串和格式化相反的场景是从外部导入数据比如CSV文件里的时间字段五花八门有的是2025/02/01有的是2025年2月1日还有的是02-01-2025。直接用字符串比较、排序都是错的必须先解析成真正的日期类型。STR_TO_DATE()就是干这个的。SELECT STR_TO_DATE(2025/02/01 14:30:25, %Y/%m/%d %H:%i:%s); -- 返回 2025-02-01 14:30:25解析规则和DATE_FORMAT的占位符一一对应但格式必须严格匹配。一个很实用的技巧是先用正则或REPLACE把各种杂格式统一再交给STR_TO_DATE。比如把2025年2月1日转成标准日期SELECT STR_TO_DATE(2025年2月1日, %Y年%c月%e日); -- 返回 2025-02-01注意这里%c和%e都是不补零的所以能正确匹配“2月1日”。如果源数据不合法STR_TO_DATE()不会抛异常而是返回NULL并产生一条警告。这在批量导入时特别隐蔽所以导入后一定要做校验比如WHERE parsed_date IS NULL过滤一遍。我习惯在加载完成的临时表上加一列parse_error专门记录转换失败的数据行方便反向修正源文件。4. 日期加减与差值计算4.1 DATE_ADD和DATE_SUB区间边界的生成需求里经常出现“最近7天”“最近30天”“上个月”这类描述。正确做法是先算出一个明确的起始时间点再传给SQL做区间过滤而不是在每一行上调用函数。SELECT NOW() AS current_time, DATE_ADD(NOW(), INTERVAL 1 DAY) AS tomorrow, DATE_SUB(NOW(), INTERVAL 30 DAY) AS days_ago_30, DATE_ADD(NOW(), INTERVAL -1 MONTH) AS month_ago_1;INTERVAL的单位非常多常用的有SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEAR还可以组合使用比如INTERVAL 1:30 HOUR_MINUTE表示1小时30分钟。如果我只关心日期不关心时分秒一般会用CURDATE()代替NOW()这样可以避免自带的时间部分污染区间判断。这个函数最大的坑是“月末溢出”。比如DATE_ADD(2025-01-31, INTERVAL 1 MONTH)你可能会想当然觉得结果是2025-02-31但2月根本没有31号MySQL返回的是2025-02-28。它不是加完月再往后顺延而是做一个“饱和”处理这在到期日计算里非常致命。我处理续费、账单周期这类需求时都不会直接用DATE_ADD加月而是自己写规则先取目标月份最后一天或者业务上统一约定“按日对齐不足则取末日”。要取某个日期所在月份的最后一天用LAST_DAY()是最省事的SELECT LAST_DAY(2025-02-10); -- 返回 2025-02-28这个函数在处理“月报时间窗口”时几乎必不可少。比如我要统计“本月截至今天”的数据月底的那天到底怎么取直接LAST_DAY(CURDATE())就能确定分界。4.2 DATEDIFF和TIMESTAMPDIFF日期间隔怎么算两个日期相减最常用的是DATEDIFF(end_date, start_date)它只比较日期部分忽略时间部分返回整数天数。SELECT DATEDIFF(2025-02-10, 2025-02-01); -- 返回 9需要注意顺序DATEDIFF的第一个参数是结束日期第二个是开始日期结果是“结束减开始”。如果你写反了会得到负数排序时很容易看错。DATEDIFF只能算天数但业务里经常需要“间隔几个小时”“间隔几个月”“间隔几年”这时用TIMESTAMPDIFF更合适SELECT TIMESTAMPDIFF(HOUR, 2025-02-01 08:00:00, 2025-02-01 18:30:00) AS hour_diff, TIMESTAMPDIFF(MONTH, 2024-02-01, 2025-02-01) AS month_diff, TIMESTAMPDIFF(YEAR, 2000-06-15, CURDATE()) AS age;第一个参数就是单位DAY、HOUR、MINUTE、SECOND、MONTH、YEAR。这里有个和直觉不同的地方TIMESTAMPDIFF(MONTH, 2025-01-31, 2025-02-28)返回1因为它按月边界算不看具体日期对齐。而DATEDIFF(2025-02-28, 2025-01-31)返回28因为按天算。如果你要计算一个用户的精确年龄用TIMESTAMPDIFF(YEAR, birth_date, CURDATE())是公认比较稳的办法。我还经常用TIMESTAMPDIFF(SECOND, created_at, NOW())来判断“距离上次操作过去了多少秒”比如超过900秒就判定会话超时。这类逻辑在SQL里直接算比把时间取回应用层再减更省事。4.3 获取周、季度、年度的边界“本周一是几号”“本季度第一天是哪天”“去年这个月从哪天开始”这类边界日期并不一定有现成函数但可以组合实现。比如本周一是SELECT DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY);季度第一天是SELECT DATE_FORMAT(CURDATE(), %Y-) AS year_prefix, QUARTER(CURDATE()) AS q; -- 再手动拼接 SELECT STR_TO_DATE(CONCAT(YEAR(CURDATE()), -, (QUARTER(CURDATE()) - 1) * 3 1, -01), %Y-%m-%d) AS quarter_first_day;我个人更推荐写一个可读性更高的版本先把季度起始月算出来再用DATE_FORMAT和STR_TO_DATE组合。这种边界日期在财务报表里非常重要但没必要硬背公式关键是理解组合方式先用YEAR()取年份、QUARTER()取季度再映射到月份最后拼成日期字符串。5. 统计报表中的日期分组与连续日期补齐5.1 按天、周、月、季度分组的小技巧报表SQL最核心的操作就是GROUP BY一个时间维度。最容易理解的写法是SELECT DATE_FORMAT(create_time, %Y-%m-%d) AS day, COUNT(*) AS cnt FROM orders WHERE create_time DATE_SUB(CURDATE(), INTERVAL 30 DAY) GROUP BY DATE_FORMAT(create_time, %Y-%m-%d) ORDER BY day;这里GROUP BY后面对应的是表达式也可以简写成GROUP BY day。按周分组要小心跨年问题比如2024-12-30和2025-01-02可能属于同一周如果只用WEEK()函数你得到的是两个年份的周号无法合并。我一般用YEARWEEK(create_time, 1)来处理第二个参数1表示周一作为一周的开始。所以“按周统计”最稳的写法是SELECT YEARWEEK(create_time, 1) AS week_key, COUNT(*) AS cnt FROM orders GROUP BY week_key;按季度则是QUARTER(create_time)但要同时带上YEAR()否则不同年份的同一季度会被合并到一起。按小时分组则用DATE_FORMAT(create_time, %Y-%m-%d %H:00)。设计分组表达式时有一条性能原则尽量在SELECT和GROUP BY里算不要在WHERE里用函数包裹字段。原因是WHERE里包裹字段会阻断索引而GROUP BY里的表达式虽然也不能用索引但至少它不会把全表扫描进一步放大到行级判断。5.2 没有数据的日期也要显示0怎么做统计最近7天订单量时如果某天没有订单普通GROUP BY会直接不返回这一行前端画折线图就会出现断点。常规解法是先生成一个连续的日期序列再左连接业务表。MySQL 8.0可以用递归CTE生成日期序列WITH RECURSIVE date_seq AS ( SELECT DATE_SUB(CURDATE(), INTERVAL 6 DAY) AS d UNION ALL SELECT DATE_ADD(d, INTERVAL 1 DAY) FROM date_seq WHERE d CURDATE() ) SELECT date_seq.d AS order_date, COUNT(o.id) AS order_cnt FROM date_seq LEFT JOIN orders o ON o.create_time date_seq.d AND o.create_time DATE_ADD(date_seq.d, INTERVAL 1 DAY) GROUP BY date_seq.d ORDER BY date_seq.d;这里我故意没写DATE(o.create_time) date_seq.d这种连接条件尽管它语义上更直观。原因就在于函数包裹create_time会让左连接变成对orders表的全表逐行计算数据量一大就卡死。改成“大于等于当日零点小于次日零点”的区间条件才能利用上create_time上的索引。这也是我在实际优化慢SQL时最常用的一招。MySQL 5.7没有递归CTE可以用一个自带的数字辅助表比如一张只有id从0到999的表配合DATE_SUB(CURDATE(), INTERVAL n DAY)同样能生成连续序列。我之前维护过一张dim_dates维表把日期、周几、是否工作日都提前算好之后所有缺数补零的场景都用它非常省心。6. 时区与索引两个“正确性”和“性能”的老大难6.1 时区设置影响的不只是显示数据库的时区由系统变量time_zone控制默认跟着服务器走。执行SET time_zone 08:00或者SET time_zone Asia/Shanghai会影响NOW()的返回值、TIMESTAMP的存取转换动作。这里最典型的坑是你在某个时区的服务器上写入了一批TIMESTAMP数据后来整个应用迁移到另一时区的服务器查询结果整体偏移了8小时。原因就是TIMESTAMP存储的是UTC时间显示时按会话时区转换。如果当时用的是DATETIME由于它不带时区概念存进去的字符串就是原样迁移后数值不会变但在多时区体系里它表示的“绝对时间”就没有任何时区信息了。我的实践原则很简单业务系统如果只在一个国家或东八区运营直接用DATETIME存本地时间应用层不做额外转换如果面向全球用户统一按UTC存DATETIME前端或接口层再按用户时区展示。尽量不要“混着来”比如有的字段存了本地时间有的字段用了TIMESTAMP出了问题时区排查难度是成倍上升的。NOW()和UTC_TIMESTAMP()在同一会话时区下会返回不同的时间差的就是时区偏移。我调试跨时区问题时习惯先执行SELECT NOW(), UTC_TIMESTAMP(), session.time_zone, system_time_zone;一眼就能看出当前会话时区到底是什么以及数据库UTC时间是多少比瞎猜快得多。6.2 WHERE条件里包函数索引会罢工这是日期函数应用里最值得强调的性能问题。假设orders表在create_time上有索引你想查某一天的订单-- 错误示范 SELECT * FROM orders WHERE DATE(create_time) 2025-02-01;这条SQL在MySQL里通常无法使用create_time索引因为DATE()函数把字段值先转换了一次索引树上存储的是原始时间值无法直接匹配函数计算结果。结果是全表扫描几百万行数据时响应时间会从毫秒级恶化到秒级。正确的写法是把它改造成一个左闭右开区间SELECT * FROM orders WHERE create_time 2025-02-01 00:00:00 AND create_time 2025-02-02 00:00:00;这样优化器可以直接对create_time做范围扫描命中索引。同理查询“最近30天”写成create_time DATE_SUB(NOW(), INTERVAL 30 DAY)是没问题的因为NOW()和DATE_SUB只作用于常数不涉及字段本身索引依然有效。判断一个查询是否能走索引我的习惯是看EXPLAIN输出的type列和key列。如果看到typeALL和keyNULL基本就是索引失效了再检查是不是在字段上套了函数。这条规则的重要性比多记几个函数用法高得多因为很多慢查询的根源都是这里。7. 常见问题排查速查表我把实际操作中遇到频率最高的问题整理成一个速查表方便直接对照现象可能原因解决方法按天查询很慢加索引也没用WHERE DATE(create_time) ...包了函数改成create_time ? AND create_time ?的区间条件DATE_ADD加一个月结果不是预期月末溢出被饱和到当月最后一天用LAST_DAY()配合业务规则处理按周统计出现跨年串周只用了WEEK()没考虑年份使用YEARWEEK(date, 1)NOW()和一些地方返回的时间不一样部分地方用了SYSDATE()统一使用NOW()导入的字符串日期变成NULLSTR_TO_DATE格式不匹配核对%i、%s、%c等占位符并检查错误行TIMESTAMP范围不够用2038年问题在极端业务中存在改用DATETIME按小时分组时少了一条边缘数据边界时间处理不当用DATE_FORMAT切分钟/小时或使用左闭右开区间同一批记录的时间戳不一致触发了ON UPDATE CURRENT_TIMESTAMPUPDATE时显式SET updated_at updated_at排查日期相关问题时我还有一个习惯先把结果集里最原始的时间字段SELECT出来看它到底是不是预期值再逐步看函数处理后的结果不要直接看最终报表。因为CONVERT、DATE_FORMAT、STR_TO_DATE每一步都可能引入肉眼难辨的数据偏差一步一步核对才能快速定位。在实际项目里我踩过最深的坑不是某个函数不会用而是“日期边界”定义不清楚。比如“最近30天”到底包不包含今天是算30条还是31条“本周”是从周一开始还是周日开始“上个月”是自然月还是滚动30天这些问题如果不先在业务层面明确写出来的SQL再漂亮也算错。所以我每次接报表需求第一件事就是和需求方敲定时间口径然后把口径以注释的形式写进SQL里避免几个月后自己都看不懂当初为什么这么算。日期时间函数本身不难难的是在真实业务场景里把边界、时区和索引这三件事都处理妥当。