1. 被日期时间坑过的那些场景先搞清楚痛点在哪1.1 一次典型的“数据少了一条”排查先讲一个我实际遇到过的例子。某天运营同事跑过来问为什么某个活动的领券统计比后台实际发放少了十几条。我当时第一反应是代码逻辑问题查了半天接口调用记录对账文件也翻了最后发现问题出在SQL的时间范围上。当时的统计SQL大概是这样的SELECT COUNT(*) FROM coupon_record WHERE create_time 2024-06-18;看起来没毛病查6月18号当天的记录。但create_time是DATETIME类型存储的是2024-06-18 08:30:11这样的完整时间。用等号去匹配2024-06-18MySQL会隐式把字符串转成2024-06-18 00:00:00所以只能命中那个时刻恰好为0点0分0秒的数据其余当天记录全部漏掉。这就是日期时间处理里最常见的坑把日期date和日期时间datetime混为一谈。类似的问题我后来在不同项目里见过很多次包括按天分组统计时用了做日期过滤、用BETWEEN查当月数据时把边界算错、DATEDIFF和TIMESTAMPDIFF搞混参数顺序等等。1.2 日期处理出问题的常见原因分类如果把这些年踩过的日期时间相关的坑做个归类大概可以分成以下几类第一类是类型混淆。DATE、DATETIME、TIMESTAMP之间的区别没搞清楚导致存储、比较、展示出现偏差。第二类是格式转换问题。字符串和日期类型互转时格式符写错或者隐式转换的规则没掌握导致结果不符合预期甚至报错。第三类是边界与区间问题。月份第一天、最后一天、周一起始、季度切换这些“临界点”在日期计算中非常容易出错。第四类是时区与夏令时问题。生产环境数据库时区设置不对或者TIMESTAMP类型受会话时区影响导致同一时间在不同环境里显示不一致。第五类是性能问题。在查询中对索引字段使用了日期函数导致索引失效数据量一大就慢得离谱。这篇文章我就按这几类痛点来展开把MySQL里日期时间处理的完整武器库和背后原理一次讲清楚。内容偏实战适合刚入门的开发同学也适合写了好几年SQL但没系统整理过日期函数的中级开发者。2. 类型选不对后面全白费DATE、TIME、DATETIME、TIMESTAMP怎么选2.1 四种日期时间类型的存储规格与适用场景MySQL里和日期时间直接相关的类型主要有DATE、TIME、DATETIME、TIMESTAMP另外还有YEAR这种相对简单的类型。很多人建表时随手就写DATETIME这倒不算错但理解每种类型的特性能帮你避免很多后续的麻烦。先看一张我整理的对比表类型存储空间显示格式取值范围时区相关DATE3字节YYYY-MM-DD1000-01-01 至 9999-12-31否TIME3字节不含小数秒HH:MM:SS-838:59:59 至 838:59:59否DATETIME8字节不含小数秒YYYY-MM-DD HH:MM:SS1000-01-01 00:00:00 至 9999-12-31 23:59:59否TIMESTAMP4字节不含小数秒与DATETIME类似1970-01-01 00:00:01 UTC 至 2038-01-19 03:14:07 UTC是YEAR1字节YYYY1901 至 2155否这里最需要记住的是TIMESTAMP的取值上限——2038年。这就是传说中的2038年问题类似的故障在32位系统的时间处理上也出现过。虽然看起来离我们很远但如果你建的表要存“出生日期”“合同到期日”这类可能超过2038年的时间用TIMESTAMP就会存不进去。还有一个容易被忽略的点TIMESTAMP内部是以UTC存储的写入时会从当前会话的时区转换成UTC读取时再转回会话时区。也就是说同一个TIMESTAMP值在不同时区的客户端连接下查出来可能不一样。而DATETIME就是个“死心眼”你存什么它就显示什么完全不理会时区设置。2.2 具体场景下我建议怎么选基于上面的差异我自己的选型原则是这样的记录业务发生时间订单创建时间、日志时间、操作时间优先用DATETIME。它的取值范围广不受时区影响对绝大多数国内业务来说足够用。如果明确需要“跟随会话时区自动转换”而且业务时间范围在1970到2038年之间可以考虑TIMESTAMP。典型场景是面向全球用户的应用希望用户看到的是本地时间。只需要“哪一天”这种粒度比如生日、节假日配置用DATE。只需要时分秒比如每天固定的活动开始时间、定时任务的执行时刻用TIME。在MySQL 5.6.4之后DATETIME和TIME还支持小数秒精度可以到微秒6位需要高精度计时的场景比如记录接口耗时可以指定DATETIME(3)。有一点要提醒尽量不要用字符串类型VARCHAR来存日期。虽然有时候从接口拿到的就是字符串直接塞进VARCHAR列也能查但排序会变成字典序2024-02-01和2024-1-01这种不规整的格式会出现逻辑错乱而且完全没法利用日期函数做运算。我见过不止一个老系统因为这种设计吃过大亏。2.3 关于默认值的一个细节建表时给日期字段设置默认值也是个容易出错的地方。在MySQL 5.6之前DATETIME不能直接设置默认值为CURRENT_TIMESTAMP5.6.5版本之后才支持。如果你的项目还在用老版本比如5.5配置默认当前时间就得靠触发器绕过去。当前版本下推荐的写法CREATE TABLE demo_table ( id INT PRIMARY KEY AUTO_INCREMENT, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间 ) ENGINEInnoDB;ON UPDATE CURRENT_TIMESTAMP这个特性很实用每次更新行记录时MySQL会自动刷新该字段的值为当前时间。不过要小心只要这条记录的任意字段被UPDATE这个时间就会被改动如果业务上需要保留“首次更新时间”这种语义这个默认值就不合适。3. 取时间和格式化从NOW()到DATE_FORMAT()的完整武器库3.1 获取当前时间NOW()、SYSDATE()、CURDATE()、CURTIME()的区别先聊最基础的需求——拿到“当前时间”。MySQL提供了好几个函数很多人以为它们差不多其实执行语义有细微差别。NOW()返回的是语句开始执行时的时刻一个SQL里多次调用NOW()结果相同。SYSDATE()返回的是函数实际执行那一刻的时刻如果在一条复杂的SQL里SYSDATE()在多个位置调用结果可能因为执行先后而不同。举个实际影响假设一条长SQL里先UPDATE再SELECT SYSDATE()拿到的可能不是同一个时间点。这会造成主从复制或日志回放时SYSDATE()在不同机器上产生的值不一致。所以在生产环境我基本只用NOW()不用SYSDATE()。CURDATE()返回当前的DATE只有年月日CURTIME()返回当前的TIME只有时分秒。它们内部也会受会话时区影响本质上是NOW()的“日期部分”和“时间部分”的简化版本。如果你想知道当前的UTC时间MySQL提供了UTC_TIMESTAMP()、UTC_DATE()、UTC_TIME()在多时区系统中做时间标准统一时很好用。3.2 格式化输出DATE_FORMAT()的格式符详解日期格式化和解析是使用频率最高的操作。把DATETIME转成想要的字符串核心函数是DATE_FORMAT()用法很简单SELECT DATE_FORMAT(NOW(), %Y-%m-%d %H:%i:%s);结果类似2024-06-18 14:30:05。这里%Y是四位数年份%m是两位数月份%d是两位数日期%H是24小时制小时%i是分钟%s是秒。我把常用格式符列一下方便查表格式符含义示例%Y四位年份2024%y两位年份24%m两位月份06%c月份无前导零6%d两位日期18%e日期无前导零18%H24小时制小时14%h12小时制小时02%i分钟30%s秒05%W星期名英文Tuesday%w星期几0周日2%a星期名缩写Tue%M月份名英文June%b月份名缩写Jun%j年内第几天170%U年内第几周周日为一周起点25%u年内第几周周一为一周起点25%T24小时制完整时间14:30:05%r12小时制完整时间02:30:05 PM我特别提醒%U和%u的区别——一个以周日为一周起点一个以周一为一周起点。国内业务通常习惯周一作为一周起点用%u更符合直觉但也要看具体业务的统计口径。同样影响周统计的还有WEEK()函数的mode参数后面分组统计部分我再细说。3.3 字符串转日期STR_TO_DATE()与隐式转换的坑反向操作把字符串转成日期类型用STR_TO_DATE()SELECT STR_TO_DATE(2024-06-18 14:30:05, %Y-%m-%d %H:%i:%s);注意两个参数必须匹配特别是格式符和字符串里的分隔符要严格一致。比如2024/06/18就不能用%Y-%m-%d去解析得用%Y/%m/%d。这里有个常见的崩溃现场接口传了一个2024-6-3 8:5:0这种不带前导零的时间字符串程序里直接用STR_TO_DATE按%Y-%m-%d %H:%i:%s解析。MySQL实际会返回NULL因为这些格式符默认要求两位数字。解决方法是改用非前导零的格式符组合比如月用%c、日用%e、小时用%k、分钟用%i%i本身不要求前导零具体组合根据输入格式调整。还要注意隐式转换的问题。MySQL在字符串和日期类型比较时会尝试把字符串转成日期类型但转换规则不一定符合预期。比如WHERE create_time 2024-06-18MySQL会把2024-06-18转成2024-06-18 00:00:00再比较这是合理的。但如果字符串格式不规整比如2024-6-18不同版本的MySQL解析行为可能不一致干脆用STR_TO_DATE()显式转换最稳妥。另外有一个使用频率挺高的函数DATE()它可以从日期时间中提取出日期部分SELECT DATE(NOW()); -- 返回 2024-06-18类似功能的还有YEAR()、MONTH()、DAY()、HOUR()、MINUTE()、SECOND()都是提取指定部分。要注意它的性能问题这个我放到第6章专门讲。4. 日期计算与区间判断DATE_ADD、DATEDIFF、TIMESTAMPDIFF的正确打开方式4.1 日期加减运算DATE_ADD与DATE_SUB的用法业务里经常要算“三天后”“下个月1号”“去年的今天”这类日期加减用DATE_ADD()和DATE_SUB()。SELECT DATE_ADD(2024-06-18, INTERVAL 3 DAY); -- 2024-06-21 SELECT DATE_SUB(2024-06-18, INTERVAL 1 MONTH); -- 2024-05-18 SELECT DATE_ADD(2024-06-18, INTERVAL 1 YEAR); -- 2025-06-18INTERVAL后跟的表达式和单位是关键DAY、MONTH、YEAR、HOUR、MINUTE、SECOND、WEEK、QUARTER都可以用。还可以组合SELECT DATE_ADD(2024-06-18, INTERVAL 1 2:30 DAY_MICROSECOND);这种组合写法实际中用得不多容易读晕我更推荐用两级操作代替比如“1天2小时30分”先加一天再加两小时三十分钟。SQL可读性在生产环境太重要了后面维护的人看到一串DAY_MICROSECOND多半要骂人。MySQL还有一个简洁的语法糖直接用INTERVAL表达式参与运算。比如SELECT 2024-06-18 INTERVAL 7 DAY;效果和DATE_ADD一样。如果你用的是TIMESTAMP类型或字符串日期也可以直接往DATE_ADD里传。注意结果类型有时候不直观DATE_ADD传DATE参数且单位是DAY这种粒度时返回DATE单位是HOUR这种粒度时会自动提升为DATETIME。搞不清时用DATE_FORMAT包一层格式化最保险。4.2 计算两个日期差几天DATEDIFF与TIMESTAMPDIFF两个日期之间差多少天最常用的函数是DATEDIFF()SELECT DATEDIFF(2024-06-18, 2024-06-01); -- 17它只比较日期部分不看时间。注意是第一个参数减第二个参数前面是大的日期后面是小的日期才能得到正数。但DATEDIFF只能算到“天”这个粒度。如果想知道两个时间差多少小时、多少分钟、多少秒就得用TIMESTAMPDIFF()SELECT TIMESTAMPDIFF(HOUR, 2024-06-18 08:00:00, 2024-06-18 14:30:00); -- 6 SELECT TIMESTAMPDIFF(MINUTE, 2024-06-18 08:00:00, 2024-06-18 08:30:00); -- 30TIMESTAMPDIFF是第二个参数减第一个参数和DATEDIFF相反。这个方向很多人会搞混我的记忆方法是TIMESTAMPDIFF(unit, start, end)从start到end经过了多少unit。单位可以是SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEAR。还有一个函数SEC_TO_TIME()把秒数转换成HH:MM:SS格式做耗时统计时会用到反向的是TIME_TO_SEC()。配合TIMESTAMPDIFF(SECOND, start, end)可以很方便地把时间差转成可读格式。实际应用中的典型场景是计算用户年龄SELECT TIMESTAMPDIFF(YEAR, birthday, CURDATE()) AS age FROM user_profile;这个写法比用YEAR(CURDATE()) - YEAR(birthday)更准确因为后者忽略生日是否已过会在某些日期上年龄多算一岁。TIMESTAMPDIFF是真正按“满了多少整年”来算的。4.3 日期区间查询的正确姿势半开区间与索引优化查询某一天的数据最稳妥的写法是半开区间-- 推荐查6月18日全天 SELECT * FROM coupon_record WHERE create_time 2024-06-18 00:00:00 AND create_time 2024-06-19 00:00:00;等价地利用DATE_ADD自动算一天SELECT * FROM coupon_record WHERE create_time 2024-06-18 AND create_time DATE_ADD(2024-06-18, INTERVAL 1 DAY);不用等号不用BETWEEN上限用开区间这样能把边界包含进来同时也能让MySQL在create_time索引上做范围扫描而不是对每一行做函数运算。关于索引这里先不展开第6章会专门讲。如果你确实习惯用BETWEEN要非常小心-- 不推荐漏掉 23:59:59 到次日 00:00:00 之间的事务 SELECT * FROM coupon_record WHERE create_time BETWEEN 2024-06-18 00:00:00 AND 2024-06-18 23:59:59;这个写法漏掉微秒级的高精度时间2024-06-18 23:59:59.5虽然大多数业务不在意但严谨一点的话直接用半开区间最安心。5. 按时间维度做统计报表从分组到补零5.1 按天、周、月分组的核心函数组合日报、周报、月报是后端统计业务的常客。按天分组直接DATE_FORMAT(create_time, %Y-%m-%d)或者更快一点用DATE(create_time)SELECT DATE(create_time) AS day, COUNT(*) FROM orders WHERE create_time 2024-06-01 AND create_time 2024-07-01 GROUP BY DATE(create_time);按月分组类似可以用DATE_FORMAT(create_time, %Y-%m)也可以提取年份和月份分别分组SELECT YEAR(create_time) AS year, MONTH(create_time) AS month, COUNT(*) FROM orders WHERE create_time 2024-01-01 GROUP BY YEAR(create_time), MONTH(create_time);按周分组就要注意了。前面提到的WEEK(date, mode)mode决定一周从星期几开始、第一周怎么算。国内习惯周一作为一周起点用WEEK(date, 1)。如果你需要带有年份的周编号用YEARWEEK(date, 1)SELECT YEARWEEK(create_time, 1) AS week_no, COUNT(*) FROM orders WHERE create_time 2024-01-01 GROUP BY YEARWEEK(create_time, 1);YEARWEEK返回类似202425的值表示2024年的第25周。这里有个跨年陷阱如果以周一为一周起点2024年1月1日是周一那YEARWEEK(2024-01-01, 1)结果是202401但如果某年的1月1日是周日且前面的几周被归入上一年YEARWEEK很可能返回202453这种上年末尾的编号分组出来会“凭空多出”一个带去年年份的组。做报表前最好先跑几个边界日期看看。5.2 统计结果缺零日期如何补齐空档分组统计最常见的痛点不是分组本身而是“某天没有数据那这一天的行压根不存在”。比如6月18日、6月19日有订单6月20日没有那按天统计的结果里就没有6月20日这行。做趋势图时会出现断档。补齐空档的做法通常是在SQL之外应用层生成完整的日期序列再左连接统计结果。比如在Java代码或Python脚本里生成6月1日到6月30日的日期集合逐日去匹配查询结果。也可以在MySQL里用递归CTE生成日期序列但要注意版本限制5.7及之前不支持递归CTE。在应用层补齐的参考思路以Python为例import datetime start datetime.date(2024, 6, 1) end datetime.date(2024, 6, 30) step datetime.timedelta(days1) date_list [] while start end: date_list.append(start.strftime(%Y-%m-%d)) start step然后拿这个列表和SQL统计结果做一次字典匹配缺失的日期补零。这种方式灵活直观也方便后续接其他数据源。如果你的MySQL是8.0也可以用递归CTE直接在SQL里生成连续日期WITH RECURSIVE date_series AS ( SELECT 2024-06-01 AS d UNION ALL SELECT DATE_ADD(d, INTERVAL 1 DAY) FROM date_series WHERE d 2024-06-30 ) SELECT d, COALESCE(cnt, 0) FROM date_series LEFT JOIN ( SELECT DATE(create_time) AS day, COUNT(*) AS cnt FROM orders WHERE create_time 2024-06-01 AND create_time 2024-07-01 GROUP BY DATE(create_time) ) t ON date_series.d t.day;这个写法8.0可用但数据量大时递归性能要看情况通常只适合补少量日期比如一个月。如果补一整年的日粒度建议还是应用层处理。5.3 季度、月初月末与工作日判断季度统计用QUARTER(date)函数返回1到4SELECT YEAR(create_time) AS year, QUARTER(create_time) AS quarter, COUNT(*) FROM orders GROUP BY YEAR(create_time), QUARTER(create_time);月初、月末是另一种高频需求。当月第一天可以这样算SELECT DATE_FORMAT(CURDATE(), %Y-%m-01);当月最后一天直接用LAST_DAY()函数SELECT LAST_DAY(2024-02-15); -- 2024-02-29闰年LAST_DAY会自动处理大小月和闰年比手动拼更可靠。上个月最后一天可以写成SELECT LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 1 MONTH));这里有个细节容易出错DATE_SUB(CURDATE(), INTERVAL 1 MONTH)在3月31日运行时结果是2月28日还是2月29日MySQL会截断到当天存在的最大日期所以2024-03-31减一个月得到2024-02-29。如果你的业务逻辑要求“无论几号上个月最后一天都返回月末”应该先取当月1号再减一天SELECT DATE_SUB(DATE_FORMAT(CURDATE(), %Y-%m-01), INTERVAL 1 DAY);这个写法在任何日期上都稳定返回上个月的最后一天。6. 时区、夏令时和索引失效生产环境里最隐蔽的坑6.1 时区参数对日期函数结果的影响很多团队开发时都遇到过“本地查出来的时间和服务器不一致”的问题。这背后的核心是MySQL的time_zone参数。看当前时区设置SHOW VARIABLES LIKE %time_zone%;输出里有两项需要注意system_time_zone表示操作系统时区time_zone表示MySQL会话的当前时区。如果time_zone是SYSTEM那MySQL会跟随操作系统时区线上连接串里也可以单独指定session time_zone。前面提过TIMESTAMP类型写入时按会话时区转成UTC存储读取时再按会话时区转回。所以同一个TIMESTAMP值在不同时区的会话里查询结果会不同。DATETIME则完全忽略时区。我见过一个事故某团队把订单表的create_time字段建成了TIMESTAMP开发库和测试库所在的服务器时区不同导致同一笔订单在两个环境里显示的时间差了几个小时。排查了很久才发现不是代码问题而是建表时类型选错了。如果要统一时间比较稳妥的做法是数据库连接串上显式指定connectionTimeZone或用SET time_zone 08:00把会话时区钉死而不是依赖服务器的物理时区。存业务时间尽量用DATETIME因为DATETIME完全不跟随时区变化行为最简单可控。面向全球用户的应用可以统一用UTC存储展示层再按用户地域转换。这时候TIMESTAMP反而更合适因为它天然就是UTC存储。夏令时的问题主要出现在使用TIMESTAMP且服务器所在地区实行夏令时的时候。时区在夏令时切换的瞬间会有“时间跳变”或“凌晨两点到三点之间不存在/出现两次”的情况如果业务对这类敏感比如定时任务建议直接避开TIMESTAMP改用DATETIME加应用层时间转换。6.2 函数包裹字段导致的索引失效这是日期处理里最影响性能的坑。先看两条SQL-- 写法A在字段上套函数 SELECT * FROM orders WHERE DATE(create_time) 2024-06-18; -- 写法B范围查询 SELECT * FROM orders WHERE create_time 2024-06-18 AND create_time 2024-06-19;写法A看着简洁但它对create_time列应用了DATE()函数MySQL在绝大多数情况下无法直接使用create_time上的索引只能对整张表逐行计算后过滤全表扫描的代价在百万级数据下非常明显。写法B是纯粹的索引范围扫描性能差距可以是几十倍甚至上百倍。类似的还有对字段做YEAR()、MONTH()、DATE_FORMAT()、UNIX_TIMESTAMP()等操作只要索引列被函数包裹优化器通常就“放弃了”索引。这不是索引本身的问题而是B树索引只能支持等值和范围查找无法预计算函数值。我之前的项目里有一个统计接口线上数据量大概2000万行原来用YEAR(create_time) 2024 AND MONTH(create_time) 6做查询结果走了全表扫描平均响应时间到了4秒多。改成半开区间范围查询后响应时间降到几十毫秒。这个优化没有改任何代码逻辑只改了查询条件。遇到必须按“某个时间点所在的周”来查的情况如果数据量大建议单独设计一个冗余字段比如order_week周编号列写入时就算好查询时直接等值匹配。新增列不影响现有逻辑代价是多存储一点空间。6.3 进阶优化思路生成列与索引如果实在没法改成范围查询还有一种手段生成列Generated Column。MySQL 5.7支持在表上定义一个虚拟列或存储列它的值由其他列计算而来然后可以对这个生成列建索引。ALTER TABLE orders ADD COLUMN create_day DATE GENERATED ALWAYS AS (DATE(create_time)) STORED, ADD INDEX idx_create_day (create_day);之后查询可以直接用create_day来等值匹配索引就派上用场了SELECT * FROM orders WHERE create_day 2024-06-18;这一步的本质是用空间换时间把函数计算的结果物化。不过要提醒几点生成列不能被直接更新写入时要额外承担计算和存储成本如果表已经很大加列加索引属于大操作建议在业务低峰期执行或者通过新建表加数据迁移的方式推进。还有一个类似的思路是按时间段建分区表比如按月分区CREATE TABLE orders_partition ( id INT NOT NULL, create_time DATETIME NOT NULL ) PARTITION BY RANGE (YEAR(create_time) * 100 MONTH(create_time)) ( PARTITION p202401 VALUES LESS THAN (202402), PARTITION p202402 VALUES LESS THAN (202403), PARTITION p202403 VALUES LESS THAN (202404) );分区表配合日期条件查询可以只扫描目标分区性能依然很可观。但分区方案也会引入维护成本新增分区、清理旧分区我个人建议在有明确月度归档需求时才考虑。6.4 最后分享一个我自己常用的排查习惯如果某天日期相关的SQL结果不对我现在的排查顺序是先确认字段类型。DESC看表结构搞清楚是DATETIME还是TIMESTAMP是否带小数秒。看会话时区。SHOW VARIABLES LIKE %time_zone%排除时区干扰。把SQL里的字符串日期全部用STR_TO_DATE()显式转换避免隐式转换出幺蛾子。对单条数据用手工SELECT验证边界。比如查6月数据就分别查2024-06-01 00:00:00和2024-07-01 00:00:00各有一条什么样记录看是否符合预期。最后看执行计划确认日期列上没有函数包裹索引能正常使用。这套流程帮我解决过不少看起来“玄学”的问题。实际上MySQL的日期时间处理规则是固定的出问题几乎都是某个环节的细节没对上。把类型、时区、格式、边界这四个维度都检查一遍绝大多数坑都能定位出来。