做后端开发的这些年我注意到一个很有意思的现象不少开发者处理日期加减第一反应是在应用层用时间戳硬算比如在代码里写time() 30 * 86400再格式化传进SQL。这种写法不是不行但一旦遇到跨月、跨年、闰年或者不同语言的时间库行为差异就会冒出各种奇奇怪怪的bug。其实MySQL自带的DATE_ADD和DATE_SUB早就把这类计算封装好了直接用SQL函数在数据库层完成既干净又不容易出错。这篇文章我就把这组函数掰开揉碎讲清楚从语法、单位、边界场景到性能陷阱全部基于实际项目里的用法来写适合正在学SQL的初学者也适合想系统排查时间计算问题的后端开发。1. 从“时间戳加减”这个高频需求说起为什么直接改SQL更省心1.1 业务里最常见的几种时间计算场景先想想我们平时到底在哪些地方需要“给时间加加减减”。以我接触过的电商、会员和内容类系统为例频率最高的无非这么几类订单超过30分钟未支付自动关闭需要算“当前时间 30分钟”VIP会员从开通日起有效30天需要算“开通时间 30天”内容平台做“7天热门榜”需要筛出“当前时间 - 7天”之后发布的内容财务系统做月度账单需要定位“上个月1号”到“上个月最后一天”定时任务要生成下周一、下个月初的调度时间。这些场景有一个共同点你需要的不是某个固定的时间戳数字而是基于某个时间点、按某种自然单位天、周、月、年偏移后的新时间点。按自然单位偏移这件事恰恰是时间戳直接加减做不好的——因为30 * 86400是固定秒数它永远不知道“一个月”可能是28天、29天、30天还是31天。1.2 DATE_ADD与DATE_SUB的语法骨架DATE_ADD和DATE_SUB的语法非常对称DATE_ADD(date, INTERVAL expr unit) DATE_SUB(date, INTERVAL expr unit)date基准时间可以是DATE、DATETIME、TIMESTAMP类型也可以是可以隐式转换成日期的字符串比如2024-06-01、2024-06-01 10:30:00。INTERVAL expr unit一个完整的“间隔表达式”其中expr是数值unit是单位。DATE_ADD返回基准时间加上间隔后的结果DATE_SUB返回基准时间减去间隔后的结果。举个最小例子SELECT DATE_ADD(2024-06-01, INTERVAL 10 DAY); -- 返回 2024-06-11 SELECT DATE_SUB(2024-06-01, INTERVAL 7 DAY); -- 返回 2024-05-25这里有一个很重要的细节INTERVAL 10 DAY是一个整体你不能把它拆开写成DATE_ADD(2024-06-01, 10, DAY)也不能漏掉INTERVAL关键字。这其实是在告诉MySQL我要用“间隔”这种结构去参与运算而不是简单的数字加减。理解这一点后面看复合单位就不会懵。提示expr是允许为负数的。DATE_ADD(date, INTERVAL -1 DAY)等价于DATE_SUB(date, INTERVAL 1 DAY)反过来也一样。所以在实际代码里不少人习惯只写DATE_ADD用正负号控制方向也能少记一个函数名。2. INTERVAL表达式拆解单位怎么选、复合写法怎么用2.1 单单位 INTERVAL 的精确定义MySQL的INTERVAL可以搭配的单位相当丰富我把常用的列成一张表方便查阅单位含义举例MICROSECOND微秒INTERVAL 500000 MICROSECONDSECOND秒INTERVAL 30 SECONDMINUTE分钟INTERVAL 45 MINUTEHOUR小时INTERVAL 6 HOURDAY天INTERVAL 15 DAYWEEK周7天INTERVAL 2 WEEKMONTH月自然月对齐INTERVAL 3 MONTHQUARTER季度3个月INTERVAL 2 QUARTERYEAR年INTERVAL 1 YEAR注意上表中的措辞我特意做了区分DAY、WEEK这类是固定长度的单位1天固定24小时1周固定7天而MONTH、QUARTER、YEAR这类是自然周期单位1个月可能是28天、30天或31天。这个区别是所有时间计算坑的根源后面第4节我会专门展开。2.2 复合单位与间隔字符串格式除了单个单位MySQL还支持复合单位用来一次表达“1天2小时30分钟”这种组合。语法是把多个单位用下划线连起来expr写成对应的字符串SELECT DATE_ADD(2024-06-01 00:00:00, INTERVAL 1 02:30:45 DAY_SECOND); -- 加 1天 2小时 30分钟 45秒 -- 返回 2024-06-02 02:30:45复合单位常见的有复合单位字符串格式含义DAY_HOUR天 小时天 小时DAY_MINUTE天 小时:分钟天 小时 分钟DAY_SECOND天 小时:分钟:秒天 小时 分钟 秒HOUR_MINUTE小时:分钟小时 分钟HOUR_SECOND小时:分钟:秒小时 分钟 秒MINUTE_SECOND分钟:秒分钟 秒YEAR_MONTH年-月年 月这个写法看起来有点绕但底层逻辑很清晰字符串里各段的顺序必须和单位在时间尺度上的从大到小顺序一致比如DAY_SECOND就是“天 小时:分钟:秒”你不能倒过来写。这种格式在手动写SQL时用到的机会不多不过在生成动态SQL或存储过程中偶尔有奇效至少看到了要知道它表达的是组合偏移而不是简单拼接。2.3 expr 为负ADD 与 SUB 其实是同一枚硬币由于expr可正可负在实际代码里我们可以用下面这些等价关系简化逻辑DATE_SUB(date, INTERVAL 1 DAY) -- 等价于 DATE_ADD(date, INTERVAL -1 DAY)反过来DATE_ADD(date, INTERVAL 1 MONTH) -- 等价于 DATE_SUB(date, INTERVAL -1 MONTH)我为什么特意提这个因为如果你写动态SQL想用同一个函数根据入参决定“加”还是“减”只需把符号交给参数就能避免在应用层拼接两种函数名。比如后端传一个offset是正数就调DATE_ADD是负数就调DATE_SUB代码分支会干净很多。3. 按天、按周、按月、按年的实战SQL示例3.1 按秒/分钟/小时超时与倒计时最典型的是订单超时。假设订单表orders里有created_at字段我要找出所有创建超过30分钟仍未支付的订单SELECT id, created_at FROM orders WHERE status pending AND created_at DATE_SUB(NOW(), INTERVAL 30 MINUTE);这里把DATE_SUB放在常量一侧等号右边而不是套在字段上是为了让MySQL能正常走索引。关于索引问题第6节我会专门讲。如果要算“某个活动在3小时45分钟后结束”可以用复合单位或分钟SELECT DATE_ADD(2024-06-01 10:00:00, INTERVAL 225 MINUTE); -- 等同于 INTERVAL 3:45 HOUR_MINUTE秒级别用得相对少但做性能监控或秒杀限流时会遇到比如计算“当前时间往后500毫秒”SELECT DATE_ADD(NOW(), INTERVAL 500000 MICROSECOND);3.2 按天/周周期任务的“每N天”语义按天和周是最不容易出错的单位。比如“从今天起15天后的日期”SELECT DATE_ADD(CURDATE(), INTERVAL 15 DAY);这里的CURDATE()返回不带时间的当前日期。如果你用NOW()会连时分秒一起带过去有时候反而不是你想要的。按周也很直接统计“最近7天注册的用户”和“最近1周注册的用户”在语义上是等价的因为1 WEEK 7 DAY。但在代码可读性上INTERVAL 1 WEEK比INTERVAL 7 DAY更能表达业务意图SELECT COUNT(*) FROM users WHERE created_at DATE_SUB(NOW(), INTERVAL 1 WEEK);做签到、会员连续打卡这类需求时“每7天一个周期”也很常见。比如要生成下一个周期的起始日SELECT DATE_ADD(2024-06-19, INTERVAL 1 WEEK); -- 返回 2024-06-263.3 按月/季度/年自然周期与账期计算按月加减是DATE_ADD最有价值也是最需要谨慎的地方。比如“下个月1号开始生效”SELECT DATE_ADD(DATE_FORMAT(NOW(), %Y-%m-01), INTERVAL 1 MONTH);这里先用DATE_FORMAT把当前时间截断为本月1号再往后加一个月自然得到下月1号。季度通常是和账期、预算周期绑定的。算“下个季度第一天”可以这样SELECT DATE_ADD(DATE_FORMAT(NOW(), %Y-%m-01), INTERVAL 1 QUARTER);但这行代码有个隐患如果今天是6月19号本月1号加1个季度等于9月1号看起来没问题可是如果今天是4月15号本月1号加1个季度等于7月1号结果是“下个季度的第一天”也恰好成立。只要是从本月1号起算加一个季度永远落在三个月后的首日所以这个写法是安全的。真正容易翻车的是月末场景下面一节就讲。按年加相对简单但要小心闰年2月29号的情况SELECT DATE_ADD(2024-02-29, INTERVAL 1 YEAR);这个查询在MySQL中返回2025-02-28。也就是说MySQL不会因为加一年后“2月29日不存在”而报错而是自动帮你取到月末。很多业务逻辑没考虑到这种自动修正这是必须提前知道的行为。4. 月末、跨年、闰年时间计算最容易翻车的边界场景4.1 1月31日加一个月结果是什么这是我在面试里最喜欢问的问题之一。直觉上1月31日加1个月有人会脱口而出2月31日——但2月根本没有31日。MySQL的处理方式很明确如果目标月份的对应日不存在就把结果截断为该月的最后一天。SELECT DATE_ADD(2024-01-31, INTERVAL 1 MONTH); -- 返回 2024-02-292024年是闰年 SELECT DATE_ADD(2023-01-31, INTERVAL 1 MONTH); -- 返回 2023-02-28这个行为初看很“智能”但如果你在做业务必须想清楚一个问题你的“每月”究竟是自然月对齐还是固定N天比如会员服务如果用户1月31日开通按自然月算2月28日到期等于实际只享受了28天但如果你希望用户享受完整的30天就应该用INTERVAL 30 DAY而不是INTERVAL 1 MONTH。这两种语义没有谁对谁错但混用一定会出问题。我在某个项目里就见过运营跟技术扯皮技术用自然月计算的最长连续签到天数跟运营手里按30天计算的报表对不上。4.2 加减往返不一定“回到原点”由于上面的月末截断机制DATE_ADD和DATE_SUB不是严格的互逆运算。最经典的例子SELECT DATE_ADD(DATE_SUB(2024-03-31, INTERVAL 1 MONTH), INTERVAL 1 MONTH); -- 第一步 DATE_SUB(2024-03-31, INTERVAL 1 MONTH) 得到 2024-02-29 -- 第二步 DATE_ADD(2024-02-29, INTERVAL 1 MONTH) 得到 2024-03-29 -- 最终结果 2024-03-29而不是原来的 2024-03-31也就是说你“3月31日减一个月再加一个月”最后得到的是3月29日。这在某些对账脚本里会制造非常隐蔽的偏差。比如按月份快照做数据拉取如果用的是这类往返计算月份边界的数据就可能漏掉或重复。4.3 跨年与闰年的处理细节跨年相对友好一些。加一年、减一月MySQL会自动处理年份进位SELECT DATE_ADD(2023-12-15, INTERVAL 2 MONTH); -- 返回 2024-02-15 SELECT DATE_SUB(2024-01-10, INTERVAL 1 YEAR); -- 返回 2023-01-10但闰年这个边界特别考验测试用例是否覆盖。我建议凡是做按月、按年计算的逻辑上线前至少把下面这几个日期各跑一遍1月31日2月28日、2月29日3月31日、4月30日12月31日不要嫌麻烦。时间计算bug的特点是平时不出来一到月底、年底、闰年集中爆发而且影响的数据往往是账务、会员、权限这类对准确性要求最高的模块。5. 类型、NULL、时区与格式化隐藏在下层的五个坑5.1 date参数的类型决定返回类型很多人没注意到DATE_ADD和DATE_SUB的返回类型跟随输入参数。如果你传一个DATE类型并且单位也是“天”或以上通常返回DATE但如果单位精确到小时、分钟、秒即使你只传了日期也会返回带时间的DATETIME。SELECT DATE_ADD(2024-06-01, INTERVAL 1 DAY); -- 返回 2024-06-02DATE 类型 SELECT DATE_ADD(2024-06-01, INTERVAL 1 HOUR); -- 返回 2024-06-01 01:00:00虽然输入只有日期这个差异在 ORM 映射或代码强类型判断时有可能踩坑你以为返回的是java.sql.Date结果却带着00:00:00的时间部分反过来也一样。稳妥的做法是在最终展示层用DATE_FORMAT或应用层格式化统一处理不要把函数返回类型当成理所当然。5.2 NULL与非法日期的传播逻辑DATE_ADD和DATE_SUB对NULL的处理非常简单只要基准时间是NULL结果就是NULL间隔表达式是NULL结果也是NULL。不要在代码里假设它会帮你跳过或填充默认值。SELECT DATE_ADD(NULL, INTERVAL 1 DAY); -- NULL SELECT DATE_ADD(2024-06-01, INTERVAL NULL); -- NULL非法日期要复杂一些。比如2023-02-30本质上不是一个合法日期但在非严格SQL模式下MySQL会做“修正”可能返回2023-03-02之类的值在严格模式下则可能报错。生产环境我强烈建议开启严格模式并且在写入前就保证日期字段合法不要指望计算函数替你做清洗。5.3 时区对TIMESTAMP与DATETIME的不同影响这是团队跨时区协作时最容易忽略的问题。MySQL的TIMESTAMP类型在存储时会把值从当前会话时区转换到UTC读取时再转回会话时区而DATETIME类型完全不理会时区存进去是什么就是什么。所以当你写出SELECT DATE_ADD(NOW(), INTERVAL 1 DAY);NOW()返回的是会话时区的当前时间。如果你的应用服务器和数据库服务器时区不一致或者同一套库被不同时区的客户端连接这条SQL在不同连接上算出的“明天”可能差出好几个小时。我在实际项目里的教训是统一约定数据库连接串的时区参数并在应用层也统一用UTC交换、展示时再转本地不要各端各设一套。5.4 字符串日期别乱传SQL Mode 与隐式转换虽然DATE_ADD(2024-06-01, INTERVAL 1 DAY)能正常工作但不代表任意格式的字符串都可以。MySQL对日期字符串有一套解析规则最稳妥的是YYYY-MM-DD或YYYY-MM-DD HH:MM:SS。如果你传2024/06/01这类格式部分版本也能识别但依赖这种隐式转换并不是好习惯。尤其是当日期字符串还带着单位、中文、或其他非标准分隔符时计算结果可能直接变成NULL或者解析成0000-00-00。你可以用STR_TO_DATE先把字符串明确转换再进行加减这样至少报错时报得明明白白SELECT DATE_ADD(STR_TO_DATE(2024/06/01, %Y/%m/%d), INTERVAL 1 DAY);5.5 返回结果的格式化技巧DATE_ADD返回的结果是标准日期时间格式但在实际输出到报表或接口时我们经常想控制它的显示。这时通常配合DATE_FORMAT使用SELECT DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 7 DAY), %Y-%m-%d) AS expire_date;如果你只想取日期部分也可以用DATE()把结果截断成纯日期SELECT DATE(DATE_ADD(NOW(), INTERVAL 7 DAY));这里我还是想提醒计算、存储、展示三个阶段最好各管各的。计算用原生函数存储用规范类型展示用格式化。混在一起写虽然能省几行代码后期维护真的会头疼。6. 性能视角什么时候该用什么时候绕开它6.1 索引失效的典型反例很多初学者会写出这样的查询SELECT id, created_at FROM orders WHERE DATE_ADD(created_at, INTERVAL 7 DAY) NOW();这个写法的本意是“找出7天前之后创建的订单”逻辑上没错但MySQL在执行时无法对created_at字段直接使用索引因为它需要先对每一行的created_at做一次函数计算然后才能进行比较。数据量一大全表扫描逃不掉。正确做法是把计算放到常量一侧SELECT id, created_at FROM orders WHERE created_at DATE_SUB(NOW(), INTERVAL 7 DAY);这条SQL里DATE_SUB(NOW(), INTERVAL 7 DAY)的结果是一个固定值MySQL可以先算出这个值再用普通索引范围扫描去匹配created_at。一句话总结算时间没问题别套在字段上就行。6.2 推荐写法与替代工具除了DATE_ADD/DATE_SUBMySQL里还有几个跟时间计算相关的函数各有各的定位函数作用适用场景DATE_ADD/DATE_SUB基准时间加/减间隔通用偏移计算DATEDIFF两个日期相差的天数计算间隔天数TIMESTAMPDIFF两个时间按指定单位相差的值计算月、年、分钟等更灵活的单位差LAST_DAY某日期所在月的最后一天取月末DAYOFMONTH某日是当月第几天配合截断日期DATE_FORMAT格式化日期截断/展示举个例子如果想知道两个时间之间差了多少个月TIMESTAMPDIFF比DATE_ADD更方便SELECT TIMESTAMPDIFF(MONTH, 2024-01-15, 2024-06-19); -- 返回 5而LAST_DAY在做月末统计时比DATE_ADD绕来绕去清爽得多SELECT LAST_DAY(2024-06-19); -- 返回 2024-06-306.3 关于运算符写法的补充MySQL其实支持直接使用和-配合INTERVALSELECT 2024-06-01 INTERVAL 1 DAY; SELECT 2024-06-01 - INTERVAL 1 DAY;这是DATE_ADD/DATE_SUB的运算符糖功能和函数基本一致。我个人在实际代码里更常用函数写法因为可读性和可搜索性更强但如果你追求简洁运算符写法也没问题。唯一的建议是团队内统一风格别今天函数明天运算符混着来。7. 综合案例与个人实操经验到期提醒、月度统计、周期性报表7.1 案例一VIP会员到期日与续费提醒假设会员表members有以下字段user_id、vip_start开通日期、vip_days购买天数。到期日最简单的算法就是SELECT user_id, vip_start, DATE_ADD(vip_start, INTERVAL vip_days DAY) AS vip_expire FROM members;这里用INTERVAL vip_days DAY是允许的因为expr可以是字段、变量、表达式。要注意的是vip_days乘的是“固定天数”而不是自然月。如果业务上有“按自然月续费”的套餐就应该存vip_months然后在SQL里写INTERVAL vip_months MONTH。续费提醒的思路也类似SELECT user_id FROM members WHERE DATE_ADD(vip_start, INTERVAL vip_days DAY) BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 3 DAY);这条SQL同样要当心索引问题。如果vip_start有索引上面的写法会把计算套在vip_start上可能导致索引失效。如果会员表数据量大更推荐提前把vip_expire冗余存储为一个字段并在写入时算好再直接对vip_expire建索引查询。7.2 案例二滚动30天统计与自然月统计统计类需求是最容易把“滚动窗口”和“自然月窗口”搞混的地方。滚动30天应该用INTERVAL 30 DAYSELECT COUNT(*) FROM orders WHERE created_at DATE_SUB(NOW(), INTERVAL 30 DAY);而自然月统计通常要把时间截断到月初SELECT COUNT(*) FROM orders WHERE created_at DATE_FORMAT(NOW(), %Y-%m-01);这两条SQL在我的经验里经常被写错尤其是产品经理说“最近一个月”的时候你永远要追问一句自然月还是滚动30天。别看差别在月末这几天报表一旦口径错了后面所有下游数据都会受影响。7.3 案例三批次任务的周期性调度用DATE_ADD/DATE_SUB生成调度时间是另一个高频场景。比如我要让某个批处理任务在每个“下一个整点”运行可以用SELECT DATE_ADD(DATE_FORMAT(NOW(), %Y-%m-%d %H:00:00), INTERVAL 1 HOUR);如果想拿“今天零点”和“明天零点”作为统计分区边界SELECT DATE_FORMAT(NOW(), %Y-%m-%d 00:00:00) AS today_start, DATE_ADD(DATE_FORMAT(NOW(), %Y-%m-%d 00:00:00), INTERVAL 1 DAY) AS today_end;这种写法在数据仓库的增量抽取里非常常见。很多人喜欢在脚本里用日期命令拼字符串其实在SQL里一条DATE_ADD就能算明白还避免了跨平台脚本语法差异。7.4 我用DATE_ADD/DATE_SUB几年后的几条心得最后聊点文本以外的东西。这几条都是我实际踩过坑之后总结的提供给准备在项目里大规模使用这组函数的人参考。第一把所有边界日期单独写成一组测试用例。不要等到月底上线才发现月底逻辑错了。我自己的习惯是维护一个“时间边界测试清单”包含1月31日、2月28/29日、3月31日、4月30日、12月31日每次涉及月/年加减都先跑一遍。第二语义比写法重要。加1个月和加30天从结果上看大部分时候不一样。写代码前先在需求文档里明确口径这个“月”到底是自然月还是固定30天这个“年”到底要不要管闰月。别让技术方案去猜业务语义。第三能放在常量侧的计算绝不放在字段侧。这不仅是性能问题也是代码可读性问题。WHERE created_at DATE_SUB(NOW(), INTERVAL 7 DAY)一眼能看出是在“筛选最近7天”反过来套在字段上读代码的人还得先做一层函数求值才能理解。第四小心往返计算。任何“先减后加”或“先加后减”的逻辑如果基准日期落在月末结果可能跟原始值不一致。在做幂等任务或对账逻辑时强烈建议用一条固定的基准表达式不要依赖两次偏移“回到原点”。第五别忘了时区是全局设置不是一条SQL的事。DATE_ADD本身很单纯它就是按基准时间的值时区做运算。真正复杂的是你的应用、数据库、监控各自身处不同时区时大家对“今天”的定义不一样。技术选型时把时区约定写进项目规范比事后追查数据差几小时的bug要轻松得多。这组函数说难不难说简单也不简单关键不在于记住语法而在于理解它背后对“自然周期”和“固定周期”的不同处理方式。希望这篇文章能帮你少踩几个我当年踩过的坑。
做后端开发的这些年我注意到一个很有意思的现象不少开发者处理日期加减第一反应是在应用层用时间戳硬算比如在代码里写time() 30 * 86400再格式化传进SQL。这种写法不是不行但一旦遇到跨月、跨年、闰年或者不同语言的时间库行为差异就会冒出各种奇奇怪怪的bug。其实MySQL自带的DATE_ADD和DATE_SUB早就把这类计算封装好了直接用SQL函数在数据库层完成既干净又不容易出错。这篇文章我就把这组函数掰开揉碎讲清楚从语法、单位、边界场景到性能陷阱全部基于实际项目里的用法来写适合正在学SQL的初学者也适合想系统排查时间计算问题的后端开发。1. 从“时间戳加减”这个高频需求说起为什么直接改SQL更省心1.1 业务里最常见的几种时间计算场景先想想我们平时到底在哪些地方需要“给时间加加减减”。以我接触过的电商、会员和内容类系统为例频率最高的无非这么几类订单超过30分钟未支付自动关闭需要算“当前时间 30分钟”VIP会员从开通日起有效30天需要算“开通时间 30天”内容平台做“7天热门榜”需要筛出“当前时间 - 7天”之后发布的内容财务系统做月度账单需要定位“上个月1号”到“上个月最后一天”定时任务要生成下周一、下个月初的调度时间。这些场景有一个共同点你需要的不是某个固定的时间戳数字而是基于某个时间点、按某种自然单位天、周、月、年偏移后的新时间点。按自然单位偏移这件事恰恰是时间戳直接加减做不好的——因为30 * 86400是固定秒数它永远不知道“一个月”可能是28天、29天、30天还是31天。1.2 DATE_ADD与DATE_SUB的语法骨架DATE_ADD和DATE_SUB的语法非常对称DATE_ADD(date, INTERVAL expr unit) DATE_SUB(date, INTERVAL expr unit)date基准时间可以是DATE、DATETIME、TIMESTAMP类型也可以是可以隐式转换成日期的字符串比如2024-06-01、2024-06-01 10:30:00。INTERVAL expr unit一个完整的“间隔表达式”其中expr是数值unit是单位。DATE_ADD返回基准时间加上间隔后的结果DATE_SUB返回基准时间减去间隔后的结果。举个最小例子SELECT DATE_ADD(2024-06-01, INTERVAL 10 DAY); -- 返回 2024-06-11 SELECT DATE_SUB(2024-06-01, INTERVAL 7 DAY); -- 返回 2024-05-25这里有一个很重要的细节INTERVAL 10 DAY是一个整体你不能把它拆开写成DATE_ADD(2024-06-01, 10, DAY)也不能漏掉INTERVAL关键字。这其实是在告诉MySQL我要用“间隔”这种结构去参与运算而不是简单的数字加减。理解这一点后面看复合单位就不会懵。提示expr是允许为负数的。DATE_ADD(date, INTERVAL -1 DAY)等价于DATE_SUB(date, INTERVAL 1 DAY)反过来也一样。所以在实际代码里不少人习惯只写DATE_ADD用正负号控制方向也能少记一个函数名。2. INTERVAL表达式拆解单位怎么选、复合写法怎么用2.1 单单位 INTERVAL 的精确定义MySQL的INTERVAL可以搭配的单位相当丰富我把常用的列成一张表方便查阅单位含义举例MICROSECOND微秒INTERVAL 500000 MICROSECONDSECOND秒INTERVAL 30 SECONDMINUTE分钟INTERVAL 45 MINUTEHOUR小时INTERVAL 6 HOURDAY天INTERVAL 15 DAYWEEK周7天INTERVAL 2 WEEKMONTH月自然月对齐INTERVAL 3 MONTHQUARTER季度3个月INTERVAL 2 QUARTERYEAR年INTERVAL 1 YEAR注意上表中的措辞我特意做了区分DAY、WEEK这类是固定长度的单位1天固定24小时1周固定7天而MONTH、QUARTER、YEAR这类是自然周期单位1个月可能是28天、30天或31天。这个区别是所有时间计算坑的根源后面第4节我会专门展开。2.2 复合单位与间隔字符串格式除了单个单位MySQL还支持复合单位用来一次表达“1天2小时30分钟”这种组合。语法是把多个单位用下划线连起来expr写成对应的字符串SELECT DATE_ADD(2024-06-01 00:00:00, INTERVAL 1 02:30:45 DAY_SECOND); -- 加 1天 2小时 30分钟 45秒 -- 返回 2024-06-02 02:30:45复合单位常见的有复合单位字符串格式含义DAY_HOUR天 小时天 小时DAY_MINUTE天 小时:分钟天 小时 分钟DAY_SECOND天 小时:分钟:秒天 小时 分钟 秒HOUR_MINUTE小时:分钟小时 分钟HOUR_SECOND小时:分钟:秒小时 分钟 秒MINUTE_SECOND分钟:秒分钟 秒YEAR_MONTH年-月年 月这个写法看起来有点绕但底层逻辑很清晰字符串里各段的顺序必须和单位在时间尺度上的从大到小顺序一致比如DAY_SECOND就是“天 小时:分钟:秒”你不能倒过来写。这种格式在手动写SQL时用到的机会不多不过在生成动态SQL或存储过程中偶尔有奇效至少看到了要知道它表达的是组合偏移而不是简单拼接。2.3 expr 为负ADD 与 SUB 其实是同一枚硬币由于expr可正可负在实际代码里我们可以用下面这些等价关系简化逻辑DATE_SUB(date, INTERVAL 1 DAY) -- 等价于 DATE_ADD(date, INTERVAL -1 DAY)反过来DATE_ADD(date, INTERVAL 1 MONTH) -- 等价于 DATE_SUB(date, INTERVAL -1 MONTH)我为什么特意提这个因为如果你写动态SQL想用同一个函数根据入参决定“加”还是“减”只需把符号交给参数就能避免在应用层拼接两种函数名。比如后端传一个offset是正数就调DATE_ADD是负数就调DATE_SUB代码分支会干净很多。3. 按天、按周、按月、按年的实战SQL示例3.1 按秒/分钟/小时超时与倒计时最典型的是订单超时。假设订单表orders里有created_at字段我要找出所有创建超过30分钟仍未支付的订单SELECT id, created_at FROM orders WHERE status pending AND created_at DATE_SUB(NOW(), INTERVAL 30 MINUTE);这里把DATE_SUB放在常量一侧等号右边而不是套在字段上是为了让MySQL能正常走索引。关于索引问题第6节我会专门讲。如果要算“某个活动在3小时45分钟后结束”可以用复合单位或分钟SELECT DATE_ADD(2024-06-01 10:00:00, INTERVAL 225 MINUTE); -- 等同于 INTERVAL 3:45 HOUR_MINUTE秒级别用得相对少但做性能监控或秒杀限流时会遇到比如计算“当前时间往后500毫秒”SELECT DATE_ADD(NOW(), INTERVAL 500000 MICROSECOND);3.2 按天/周周期任务的“每N天”语义按天和周是最不容易出错的单位。比如“从今天起15天后的日期”SELECT DATE_ADD(CURDATE(), INTERVAL 15 DAY);这里的CURDATE()返回不带时间的当前日期。如果你用NOW()会连时分秒一起带过去有时候反而不是你想要的。按周也很直接统计“最近7天注册的用户”和“最近1周注册的用户”在语义上是等价的因为1 WEEK 7 DAY。但在代码可读性上INTERVAL 1 WEEK比INTERVAL 7 DAY更能表达业务意图SELECT COUNT(*) FROM users WHERE created_at DATE_SUB(NOW(), INTERVAL 1 WEEK);做签到、会员连续打卡这类需求时“每7天一个周期”也很常见。比如要生成下一个周期的起始日SELECT DATE_ADD(2024-06-19, INTERVAL 1 WEEK); -- 返回 2024-06-263.3 按月/季度/年自然周期与账期计算按月加减是DATE_ADD最有价值也是最需要谨慎的地方。比如“下个月1号开始生效”SELECT DATE_ADD(DATE_FORMAT(NOW(), %Y-%m-01), INTERVAL 1 MONTH);这里先用DATE_FORMAT把当前时间截断为本月1号再往后加一个月自然得到下月1号。季度通常是和账期、预算周期绑定的。算“下个季度第一天”可以这样SELECT DATE_ADD(DATE_FORMAT(NOW(), %Y-%m-01), INTERVAL 1 QUARTER);但这行代码有个隐患如果今天是6月19号本月1号加1个季度等于9月1号看起来没问题可是如果今天是4月15号本月1号加1个季度等于7月1号结果是“下个季度的第一天”也恰好成立。只要是从本月1号起算加一个季度永远落在三个月后的首日所以这个写法是安全的。真正容易翻车的是月末场景下面一节就讲。按年加相对简单但要小心闰年2月29号的情况SELECT DATE_ADD(2024-02-29, INTERVAL 1 YEAR);这个查询在MySQL中返回2025-02-28。也就是说MySQL不会因为加一年后“2月29日不存在”而报错而是自动帮你取到月末。很多业务逻辑没考虑到这种自动修正这是必须提前知道的行为。4. 月末、跨年、闰年时间计算最容易翻车的边界场景4.1 1月31日加一个月结果是什么这是我在面试里最喜欢问的问题之一。直觉上1月31日加1个月有人会脱口而出2月31日——但2月根本没有31日。MySQL的处理方式很明确如果目标月份的对应日不存在就把结果截断为该月的最后一天。SELECT DATE_ADD(2024-01-31, INTERVAL 1 MONTH); -- 返回 2024-02-292024年是闰年 SELECT DATE_ADD(2023-01-31, INTERVAL 1 MONTH); -- 返回 2023-02-28这个行为初看很“智能”但如果你在做业务必须想清楚一个问题你的“每月”究竟是自然月对齐还是固定N天比如会员服务如果用户1月31日开通按自然月算2月28日到期等于实际只享受了28天但如果你希望用户享受完整的30天就应该用INTERVAL 30 DAY而不是INTERVAL 1 MONTH。这两种语义没有谁对谁错但混用一定会出问题。我在某个项目里就见过运营跟技术扯皮技术用自然月计算的最长连续签到天数跟运营手里按30天计算的报表对不上。4.2 加减往返不一定“回到原点”由于上面的月末截断机制DATE_ADD和DATE_SUB不是严格的互逆运算。最经典的例子SELECT DATE_ADD(DATE_SUB(2024-03-31, INTERVAL 1 MONTH), INTERVAL 1 MONTH); -- 第一步 DATE_SUB(2024-03-31, INTERVAL 1 MONTH) 得到 2024-02-29 -- 第二步 DATE_ADD(2024-02-29, INTERVAL 1 MONTH) 得到 2024-03-29 -- 最终结果 2024-03-29而不是原来的 2024-03-31也就是说你“3月31日减一个月再加一个月”最后得到的是3月29日。这在某些对账脚本里会制造非常隐蔽的偏差。比如按月份快照做数据拉取如果用的是这类往返计算月份边界的数据就可能漏掉或重复。4.3 跨年与闰年的处理细节跨年相对友好一些。加一年、减一月MySQL会自动处理年份进位SELECT DATE_ADD(2023-12-15, INTERVAL 2 MONTH); -- 返回 2024-02-15 SELECT DATE_SUB(2024-01-10, INTERVAL 1 YEAR); -- 返回 2023-01-10但闰年这个边界特别考验测试用例是否覆盖。我建议凡是做按月、按年计算的逻辑上线前至少把下面这几个日期各跑一遍1月31日2月28日、2月29日3月31日、4月30日12月31日不要嫌麻烦。时间计算bug的特点是平时不出来一到月底、年底、闰年集中爆发而且影响的数据往往是账务、会员、权限这类对准确性要求最高的模块。5. 类型、NULL、时区与格式化隐藏在下层的五个坑5.1 date参数的类型决定返回类型很多人没注意到DATE_ADD和DATE_SUB的返回类型跟随输入参数。如果你传一个DATE类型并且单位也是“天”或以上通常返回DATE但如果单位精确到小时、分钟、秒即使你只传了日期也会返回带时间的DATETIME。SELECT DATE_ADD(2024-06-01, INTERVAL 1 DAY); -- 返回 2024-06-02DATE 类型 SELECT DATE_ADD(2024-06-01, INTERVAL 1 HOUR); -- 返回 2024-06-01 01:00:00虽然输入只有日期这个差异在 ORM 映射或代码强类型判断时有可能踩坑你以为返回的是java.sql.Date结果却带着00:00:00的时间部分反过来也一样。稳妥的做法是在最终展示层用DATE_FORMAT或应用层格式化统一处理不要把函数返回类型当成理所当然。5.2 NULL与非法日期的传播逻辑DATE_ADD和DATE_SUB对NULL的处理非常简单只要基准时间是NULL结果就是NULL间隔表达式是NULL结果也是NULL。不要在代码里假设它会帮你跳过或填充默认值。SELECT DATE_ADD(NULL, INTERVAL 1 DAY); -- NULL SELECT DATE_ADD(2024-06-01, INTERVAL NULL); -- NULL非法日期要复杂一些。比如2023-02-30本质上不是一个合法日期但在非严格SQL模式下MySQL会做“修正”可能返回2023-03-02之类的值在严格模式下则可能报错。生产环境我强烈建议开启严格模式并且在写入前就保证日期字段合法不要指望计算函数替你做清洗。5.3 时区对TIMESTAMP与DATETIME的不同影响这是团队跨时区协作时最容易忽略的问题。MySQL的TIMESTAMP类型在存储时会把值从当前会话时区转换到UTC读取时再转回会话时区而DATETIME类型完全不理会时区存进去是什么就是什么。所以当你写出SELECT DATE_ADD(NOW(), INTERVAL 1 DAY);NOW()返回的是会话时区的当前时间。如果你的应用服务器和数据库服务器时区不一致或者同一套库被不同时区的客户端连接这条SQL在不同连接上算出的“明天”可能差出好几个小时。我在实际项目里的教训是统一约定数据库连接串的时区参数并在应用层也统一用UTC交换、展示时再转本地不要各端各设一套。5.4 字符串日期别乱传SQL Mode 与隐式转换虽然DATE_ADD(2024-06-01, INTERVAL 1 DAY)能正常工作但不代表任意格式的字符串都可以。MySQL对日期字符串有一套解析规则最稳妥的是YYYY-MM-DD或YYYY-MM-DD HH:MM:SS。如果你传2024/06/01这类格式部分版本也能识别但依赖这种隐式转换并不是好习惯。尤其是当日期字符串还带着单位、中文、或其他非标准分隔符时计算结果可能直接变成NULL或者解析成0000-00-00。你可以用STR_TO_DATE先把字符串明确转换再进行加减这样至少报错时报得明明白白SELECT DATE_ADD(STR_TO_DATE(2024/06/01, %Y/%m/%d), INTERVAL 1 DAY);5.5 返回结果的格式化技巧DATE_ADD返回的结果是标准日期时间格式但在实际输出到报表或接口时我们经常想控制它的显示。这时通常配合DATE_FORMAT使用SELECT DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 7 DAY), %Y-%m-%d) AS expire_date;如果你只想取日期部分也可以用DATE()把结果截断成纯日期SELECT DATE(DATE_ADD(NOW(), INTERVAL 7 DAY));这里我还是想提醒计算、存储、展示三个阶段最好各管各的。计算用原生函数存储用规范类型展示用格式化。混在一起写虽然能省几行代码后期维护真的会头疼。6. 性能视角什么时候该用什么时候绕开它6.1 索引失效的典型反例很多初学者会写出这样的查询SELECT id, created_at FROM orders WHERE DATE_ADD(created_at, INTERVAL 7 DAY) NOW();这个写法的本意是“找出7天前之后创建的订单”逻辑上没错但MySQL在执行时无法对created_at字段直接使用索引因为它需要先对每一行的created_at做一次函数计算然后才能进行比较。数据量一大全表扫描逃不掉。正确做法是把计算放到常量一侧SELECT id, created_at FROM orders WHERE created_at DATE_SUB(NOW(), INTERVAL 7 DAY);这条SQL里DATE_SUB(NOW(), INTERVAL 7 DAY)的结果是一个固定值MySQL可以先算出这个值再用普通索引范围扫描去匹配created_at。一句话总结算时间没问题别套在字段上就行。6.2 推荐写法与替代工具除了DATE_ADD/DATE_SUBMySQL里还有几个跟时间计算相关的函数各有各的定位函数作用适用场景DATE_ADD/DATE_SUB基准时间加/减间隔通用偏移计算DATEDIFF两个日期相差的天数计算间隔天数TIMESTAMPDIFF两个时间按指定单位相差的值计算月、年、分钟等更灵活的单位差LAST_DAY某日期所在月的最后一天取月末DAYOFMONTH某日是当月第几天配合截断日期DATE_FORMAT格式化日期截断/展示举个例子如果想知道两个时间之间差了多少个月TIMESTAMPDIFF比DATE_ADD更方便SELECT TIMESTAMPDIFF(MONTH, 2024-01-15, 2024-06-19); -- 返回 5而LAST_DAY在做月末统计时比DATE_ADD绕来绕去清爽得多SELECT LAST_DAY(2024-06-19); -- 返回 2024-06-306.3 关于运算符写法的补充MySQL其实支持直接使用和-配合INTERVALSELECT 2024-06-01 INTERVAL 1 DAY; SELECT 2024-06-01 - INTERVAL 1 DAY;这是DATE_ADD/DATE_SUB的运算符糖功能和函数基本一致。我个人在实际代码里更常用函数写法因为可读性和可搜索性更强但如果你追求简洁运算符写法也没问题。唯一的建议是团队内统一风格别今天函数明天运算符混着来。7. 综合案例与个人实操经验到期提醒、月度统计、周期性报表7.1 案例一VIP会员到期日与续费提醒假设会员表members有以下字段user_id、vip_start开通日期、vip_days购买天数。到期日最简单的算法就是SELECT user_id, vip_start, DATE_ADD(vip_start, INTERVAL vip_days DAY) AS vip_expire FROM members;这里用INTERVAL vip_days DAY是允许的因为expr可以是字段、变量、表达式。要注意的是vip_days乘的是“固定天数”而不是自然月。如果业务上有“按自然月续费”的套餐就应该存vip_months然后在SQL里写INTERVAL vip_months MONTH。续费提醒的思路也类似SELECT user_id FROM members WHERE DATE_ADD(vip_start, INTERVAL vip_days DAY) BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 3 DAY);这条SQL同样要当心索引问题。如果vip_start有索引上面的写法会把计算套在vip_start上可能导致索引失效。如果会员表数据量大更推荐提前把vip_expire冗余存储为一个字段并在写入时算好再直接对vip_expire建索引查询。7.2 案例二滚动30天统计与自然月统计统计类需求是最容易把“滚动窗口”和“自然月窗口”搞混的地方。滚动30天应该用INTERVAL 30 DAYSELECT COUNT(*) FROM orders WHERE created_at DATE_SUB(NOW(), INTERVAL 30 DAY);而自然月统计通常要把时间截断到月初SELECT COUNT(*) FROM orders WHERE created_at DATE_FORMAT(NOW(), %Y-%m-01);这两条SQL在我的经验里经常被写错尤其是产品经理说“最近一个月”的时候你永远要追问一句自然月还是滚动30天。别看差别在月末这几天报表一旦口径错了后面所有下游数据都会受影响。7.3 案例三批次任务的周期性调度用DATE_ADD/DATE_SUB生成调度时间是另一个高频场景。比如我要让某个批处理任务在每个“下一个整点”运行可以用SELECT DATE_ADD(DATE_FORMAT(NOW(), %Y-%m-%d %H:00:00), INTERVAL 1 HOUR);如果想拿“今天零点”和“明天零点”作为统计分区边界SELECT DATE_FORMAT(NOW(), %Y-%m-%d 00:00:00) AS today_start, DATE_ADD(DATE_FORMAT(NOW(), %Y-%m-%d 00:00:00), INTERVAL 1 DAY) AS today_end;这种写法在数据仓库的增量抽取里非常常见。很多人喜欢在脚本里用日期命令拼字符串其实在SQL里一条DATE_ADD就能算明白还避免了跨平台脚本语法差异。7.4 我用DATE_ADD/DATE_SUB几年后的几条心得最后聊点文本以外的东西。这几条都是我实际踩过坑之后总结的提供给准备在项目里大规模使用这组函数的人参考。第一把所有边界日期单独写成一组测试用例。不要等到月底上线才发现月底逻辑错了。我自己的习惯是维护一个“时间边界测试清单”包含1月31日、2月28/29日、3月31日、4月30日、12月31日每次涉及月/年加减都先跑一遍。第二语义比写法重要。加1个月和加30天从结果上看大部分时候不一样。写代码前先在需求文档里明确口径这个“月”到底是自然月还是固定30天这个“年”到底要不要管闰月。别让技术方案去猜业务语义。第三能放在常量侧的计算绝不放在字段侧。这不仅是性能问题也是代码可读性问题。WHERE created_at DATE_SUB(NOW(), INTERVAL 7 DAY)一眼能看出是在“筛选最近7天”反过来套在字段上读代码的人还得先做一层函数求值才能理解。第四小心往返计算。任何“先减后加”或“先加后减”的逻辑如果基准日期落在月末结果可能跟原始值不一致。在做幂等任务或对账逻辑时强烈建议用一条固定的基准表达式不要依赖两次偏移“回到原点”。第五别忘了时区是全局设置不是一条SQL的事。DATE_ADD本身很单纯它就是按基准时间的值时区做运算。真正复杂的是你的应用、数据库、监控各自身处不同时区时大家对“今天”的定义不一样。技术选型时把时区约定写进项目规范比事后追查数据差几小时的bug要轻松得多。这组函数说难不难说简单也不简单关键不在于记住语法而在于理解它背后对“自然周期”和“固定周期”的不同处理方式。希望这篇文章能帮你少踩几个我当年踩过的坑。
