做数据统计和业务报表的时候，日期时间函数是绝对绕不开的一类SQL函数。业务表里十有八九都带着created_at、updated_at、birthday这类时间字段，而按天、按周、按月做分组统计，又是每张报表都躲不掉的需求。同一个需求，不同人写出来的SQL性能有时候能差几十倍，问题往往就出在日期函数的用法上。这篇文章我打算从实际干活的角度，把MySQL里常用的日期时间函数按场景过一遍，包括类型选型、格式化、日期计算、分组统计、时区和索引的坑，中间会穿插我这些年踩过的一些实在问题。不管你是刚接触SQL的新人，还是写了好几年业务的开发，应该都能从这里拿点东西直接用。
1. 日期类型怎么选：用错类型，后面全是坑
1.1 三种日期时间的存储差异
很多初学者上来就无脑用DATETIME，这没问题，但如果你不清楚DATE、DATETIME、TIMESTAMP三者的差别，遇到时区问题和存储空间优化就会很被动。下面这张表是我在实际项目中总结的对比：
| 类型 | 存储空间 | 取值范围 | 是否有时区概念 |
|---|---|---|---|
| DATE | 3字节 | '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 '更新时间' ) ENGINE=InnoDB;这里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_DATE
3.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 |
| %H | 24小时制小时 | 14 |
| %h | 12小时制小时 | 02 |
| %i | 分钟 | 30 |
| %s | 秒 | 25 |
| %p | AM/PM | PM |
| %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列。如果看到type=ALL和key=NULL，基本就是索引失效了，再检查是不是在字段上套了函数。这条规则的重要性，比多记几个函数用法高得多，因为很多慢查询的根源都是这里。
7. 常见问题排查速查表
我把实际操作中遇到频率最高的问题整理成一个速查表，方便直接对照：
| 现象 | 可能原因 | 解决方法 |
|---|---|---|
| 按天查询很慢，加索引也没用 | WHERE DATE(create_time) = ...包了函数 | 改成create_time >= ? AND create_time < ?的区间条件 |
DATE_ADD加一个月结果不是预期 | 月末溢出被饱和到当月最后一天 | 用LAST_DAY()配合业务规则处理 |
| 按周统计出现跨年串周 | 只用了WEEK()没考虑年份 | 使用YEARWEEK(date, 1) |
NOW()和一些地方返回的时间不一样 | 部分地方用了SYSDATE() | 统一使用NOW() |
导入的字符串日期变成NULL | STR_TO_DATE格式不匹配 | 核对%i、%s、%c等占位符，并检查错误行 |
TIMESTAMP范围不够用 | 2038年问题在极端业务中存在 | 改用DATETIME |
| 按小时分组时少了一条边缘数据 | 边界时间处理不当 | 用DATE_FORMAT切分钟/小时，或使用左闭右开区间 |
| 同一批记录的时间戳不一致 | 触发了ON UPDATE CURRENT_TIMESTAMP | UPDATE时显式SET updated_at = updated_at |
排查日期相关问题时，我还有一个习惯：先把结果集里最原始的时间字段SELECT出来，看它到底是不是预期值，再逐步看函数处理后的结果，不要直接看最终报表。因为CONVERT、DATE_FORMAT、STR_TO_DATE每一步都可能引入肉眼难辨的数据偏差，一步一步核对才能快速定位。
在实际项目里，我踩过最深的坑不是某个函数不会用，而是“日期边界”定义不清楚。比如“最近30天”到底包不包含今天，是算30条还是31条，“本周”是从周一开始还是周日开始，“上个月”是自然月还是滚动30天，这些问题如果不先在业务层面明确，写出来的SQL再漂亮也算错。所以我每次接报表需求，第一件事就是和需求方敲定时间口径，然后把口径以注释的形式写进SQL里，避免几个月后自己都看不懂当初为什么这么算。日期时间函数本身不难，难的是在真实业务场景里把边界、时区和索引这三件事都处理妥当。
