做MySQL开发或者数据分析的同学几乎都逃不过跟时间差打交道。订单超时未发货要算超了多久、用户连续登录要判断中间断了几天、会员到期要提前多少天提醒这些需求落到SQL里核心就是三个函数DATEDIFF、TIMESTAMPDIFF、TIMEDIFF。名字看着像三胞胎实际行为差得离谱参数顺序还故意互相“反着来”新手用错是家常便饭。这篇先把三个函数各自的语法、底层逻辑、边界行为和典型业务场景讲透顺便把我这些年踩过的坑一起写出来。适合刚接触MySQL的开发者也适合写了几年SQL但对这几个函数边界不太清楚的兄弟。1. 三个函数看着像三胞胎实际性格差很远第一次接触这三个函数的人通常会陷入一种“名字都差不多随便用一个就行”的错觉。DATEDIFF、TIMESTAMPDIFF、TIMEDIFF里面都带 DIFF但返回类型、参数顺序、计算粒度完全不同。用错一个结果可能差出几十倍甚至直接报错。1.1 先看一张对比表心里有个底函数返回类型参数顺序示例结果DATEDIFF(date1, date2)整数天数date1 - date2DATEDIFF(2024-03-10, 2024-03-01)返回 9TIMESTAMPDIFF(unit, datetime1, datetime2)整数按指定单位datetime2 - datetime1TIMESTAMPDIFF(DAY, 2024-03-01, 2024-03-10)返回 9TIMEDIFF(time1, time2)TIME 类型time1 - time2TIMEDIFF(10:30:00, 08:00:00)返回02:30:00这个表里最反直觉的地方有两个第一TIMESTAMPDIFF的参数顺序是“结束时间减开始时间”而DATEDIFF是“第一个参数减第二个参数”。如果你习惯性地把TIMESTAMPDIFF按DATEDIFF的顺序写结果就是负数而这个负数在业务表里往往被当成异常数据排查半天还以为是数据脏。第二TIMEDIFF的返回不是数字是HH:MM:SS这种 TIME 格式。很多人用TIMEDIFF算完想直接和 3600 做比较结果隐式转换后得到一堆莫名其妙的数字。后面会细说。1.2 三个函数分别适合什么场景我自己的使用习惯可以总结成三句话只关心“日历上隔了几天”比如“下单后5天内未发货”的工单判断用DATEDIFF。它不看时间只看日期。需要按小时、分钟、秒、月、年来算间隔或者需要精确到某个单位用TIMESTAMPDIFF。它是这几个函数里最“精密”的一个。需要把时间差展示成“几小时几分几秒”或者做两个时刻的纯时间差值比如“这次请求耗时 00:00:03.215”用TIMEDIFF。这三个函数不是互相替代的关系更像扳手、螺丝刀和钳子。先想清楚业务问的是“隔了几格日历”还是“精确经过了多少物理时间”再选函数能少走很多弯路。2. DATEDIFF只数日期格子的天数计算器DATEDIFF是我见过使用率最高的时间差函数也是被误解最深的一个。它的语法很简单DATEDIFF(date1, date2)返回date1 - date2的天数差。2.1 DATEDIFF 底层做了什么取日期、去时刻关键点在于DATEDIFF在计算之前会先把两个参数的时间部分全部忽略只留下日期然后按“日历格数”做差。举个例子SELECT DATEDIFF(2024-03-11 23:59:59, 2024-03-11 00:00:01) AS same_day_diff, -- 0 DATEDIFF(2024-03-12 23:59:59, 2024-03-11 00:00:01) AS cross_day_diff; -- 1第一条记录两个时刻相差接近24小时但因为是同一天DATEDIFF返回 0。第二条记录两个时刻相差不到24小时但因为跨了日期边界返回 1。这就是“日历格数”的含义DATEDIFF回答的是“从3月11日到3月12日是1天”而不是“这两个时刻之间满24小时了吗”。很多人拿DATEDIFF去判断“用户是否24小时未登录”这种用法是错的。正确做法应该用TIMESTAMPDIFF(HOUR, ...)或者直接比较last_login_time NOW() - INTERVAL 24 HOUR。2.2 实战场景账龄、连续登录与到期提醒DATEDIFF最擅长处理“按自然日计算”的业务。比如应收账款的账龄分析财务想知道某笔款项已经拖了多少天SELECT invoice_id, DATEDIFF(CURDATE(), due_date) AS overdue_days FROM invoices WHERE paid_flag 0 AND due_date CURDATE();这就是典型“今天减应还日期”得到的是自然日逾期天数不需要关心具体时刻。再比如判断用户是否连续登录。在用户登录流水表里计算每个用户最近两次登录之间的间隔WITH login_sorted AS ( SELECT user_id, login_date, LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date) AS prev_login_date FROM user_login_log ) SELECT user_id, DATEDIFF(login_date, prev_login_date) AS login_gap_days FROM login_sorted WHERE prev_login_date IS NOT NULL AND DATEDIFF(login_date, prev_login_date) 1;只要间隔大于1就说明这个用户某一天没有登录连续登录断了。DATEDIFF在“只看日期”的场景里非常好用。还有会员到期提醒SELECT user_id, expire_date, DATEDIFF(expire_date, CURDATE()) AS remain_days FROM memberships WHERE DATEDIFF(expire_date, CURDATE()) BETWEEN 0 AND 30;这种需求如果把时间带进来反而麻烦因为用户购买会员通常只精确到日DATEDIFF天然匹配业务粒度。2.3 和SQL Server同名函数的区别这是一个很多跨数据库开发的人踩过的坑。在 SQL Server 里DATEDIFF的签名是DATEDIFF(datepart, startdate, enddate)它有三个参数第一个是单位比如 day、month、year而且单位写在最前面。MySQL 的DATEDIFF只有两个参数没有日期单位的概念默认就是天数。如果你在SQL Server待久了回到MySQL里顺手写了DATEDIFF(DAY, startdate, enddate)MySQL会直接把DAY当成一个字符串隐式转换成日期结果大概率是 NULL 或者报错。反方向也一样MySQL习惯写两个参数的人切到SQL Server也会一脸懵。提示DATEDIFF一旦遇到 NULL 参数直接返回 NULL。做报表的时候要注意LEFT JOIN关联不上产生的 NULL 值否则DATEDIFF(expire_date, CURDATE())会出现一堆 NULL过滤条件把它们丢掉后数据就对不上了。3. TIMESTAMPDIFF最灵活但月差和年差的坑也最多TIMESTAMPDIFF是三个函数里功能最强的一个也是踩坑深度最深的一个。它的语法TIMESTAMPDIFF(unit, datetime_expr1, datetime_expr2)返回datetime_expr2 - datetime_expr1的差值结果按照unit指定的单位表示而且是整数。3.1 支持的九个单位与内部换算逻辑TIMESTAMPDIFF支持的单位如下表unit 参数粒度典型应用FRAC_SECOND微秒高精度计时日常极少用SECOND秒接口耗时、会话时长MINUTE分钟订单处理耗时、超时判断HOUR小时跨时区计算、服务时长DAY日自然日间隔、活跃间隔WEEK周周报统计、连续周活跃MONTH月账期、订阅周期QUARTER季度季度报表YEAR年年龄、工龄它的内部逻辑可以理解成先把两个 DATETIME 换算成“以指定单位为刻度”的序号然后对序号做差。这个说法的意义在于TIMESTAMPDIFF是按“日历刻度”计算的不是按物理时长换算的。举个最容易理解的例子SELECT TIMESTAMPDIFF(DAY, 2024-01-01 23:00:00, 2024-01-02 01:00:00) AS day_diff; -- 1两个时刻只差2小时但因为跨了日期边界DAY单位返回 1。如果你用秒差除以86400得到的是约 0.083 天。一个回答“日历翻了几格”一个回答“物理经过了几天”含义完全不同。同理SELECT TIMESTAMPDIFF(HOUR, 2024-03-01 12:00:00, 2024-03-01 14:30:00) AS hour_diff; -- 2返回 2而不是 2.5因为TIMESTAMPDIFF只保留整数部分多余的 30 分钟直接截断不会四舍五入。3.2 最容易误解的“月差”和“年差”边界问题如果你只用TIMESTAMPDIFF算小时、分钟、秒可能一直感觉良好。一旦把它用到月差、年差上立刻会发现一个诡异的现象SELECT TIMESTAMPDIFF(MONTH, 2024-01-31, 2024-02-01) AS month_diff_by_day, -- 1 TIMESTAMPDIFF(MONTH, 2024-01-15, 2024-02-14) AS month_diff_half; -- 11月31日到2月1日自然语义上只过了1天但TIMESTAMPDIFF返回 1个月。1月15日到2月14日明明不足一个月它也返回 1个月。这就让很多人抓狂。原因就是我前面说的“日历格数”逻辑。MONTH单位把两个时间分别换算到“年月”的刻度上2024-01-31 变成 2024年1月这个格子2024-02-01 变成 2024年2月这个格子两者差一格就是 1 个月。月份里的“日”没有参与判断。年差的坑更隐蔽。看这个例子SELECT TIMESTAMPDIFF(YEAR, 2000-06-01, 2024-05-01) AS year_diff; -- 24出生日期是 2000年6月1日到 2024年5月1日为止这个人的实际周岁是 23 岁还没过24岁生日。但TIMESTAMPDIFF(YEAR, ...)返回 24因为它是拿年份数字 2024 减 2000得到 24。很多人写用户年龄统计的时候直接用SELECT TIMESTAMPDIFF(YEAR, birthdate, CURDATE()) AS age FROM users;结果每年都会在“生日还没到”的那段时间里统计出一批虚增一岁的用户。那么正确的年龄怎么算需要补一步“生日是否已经过了”的判断SELECT user_id, birthdate, TIMESTAMPDIFF(YEAR, birthdate, CURDATE()) - (DATE_ADD(birthdate, INTERVAL TIMESTAMPDIFF(YEAR, birthdate, CURDATE()) YEAR) CURDATE()) AS age FROM users;思路是先用TIMESTAMPDIFF算出粗值然后从生日往后加“粗值”的年数如果加完已经超过了当前日期说明今年生日还没到需要减 1。这个写法对 2月29日出生的人也能正确处理因为MySQL会自动处理闰年的日期溢出。3.3 年龄计算、订单耗时等业务写法TIMESTAMPDIFF真正适合的场景是“需要单位明确、精确到指定刻度”的场景。比如电商里统计订单从下单到支付的平均耗时用分钟做单位SELECT TIMESTAMPDIFF(MINUTE, order_time, pay_time) AS pay_minutes FROM orders WHERE pay_time IS NOT NULL;如果想判断某个支付接口是否超过 30 分钟未响应直接SELECT * FROM payment_records WHERE TIMESTAMPDIFF(MINUTE, create_time, NOW()) 30;需要注意这里TIMESTAMPDIFF(MINUTE, create_time, NOW())把函数用在了查询条件里如果表很大create_time上的索引会失效。这个问题在第 5 章会重点讲。4. TIMEDIFF返回时间格式的差值上限838小时TIMEDIFF大概是三个函数里存在感最低的一个但它有自己不可替代的场景。语法TIMEDIFF(time1, time2)返回time1 - time2结果是 TIME 类型格式是HH:MM:SS。4.1 返回TIME类型与溢出边界TIME 类型在 MySQL 里不是只到 23:59:59它的取值范围是-838:59:59到838:59:59。这意味着TIMEDIFF可以处理跨越几十天的差值但有一个上限大约 34.96 天838小时 / 24。看两个例子SELECT TIMEDIFF(2024-01-03 10:00:00, 2024-01-02 09:00:00) AS diff_25h, -- 25:00:00 TIMEDIFF(2024-01-02 10:00:00, 2024-01-03 09:00:00) AS diff_neg; -- -23:00:00跨天没问题直接输出25:00:00后一个因为前一个时间小于后一个输出负值-23:00:00这个负号在展示层很容易被忽略做报表时要格外小心。一旦差值超过 838 小时不同 MySQL 版本的表现不一样有的直接报错有的返回 NULL有的截断成边界值。所以如果你要计算超过35天的时间差不要用TIMEDIFF直接用TIMESTAMPDIFF(HOUR, ...)或者TIMESTAMPDIFF(SECOND, ...)更稳妥。4.2 时间格式可视化的特点TIMEDIFF返回的 TIME 类型在结果集里看起来是字符串但它本质是时间类型。如果你把它跟数字比较MySQL会做隐式转换。举例SELECT TIMEDIFF(10:30:00, 08:00:00) 2;这时候2会被转换成00:00:02而不是 2 小时。结果可能完全出乎意料。如果需要判断耗时是否超过某个小时数应该用SELECT TIMESTAMPDIFF(SECOND, start_time, end_time) AS duration_seconds FROM sessions;或者把TIMEDIFF的结果用TIME_TO_SEC转成秒数再比较SELECT TIME_TO_SEC(TIMEDIFF(end_time, start_time)) AS duration_seconds FROM sessions;TIME_TO_SEC(02:30:00)返回 9000 秒这种数值才能安全地参与算术和比较。4.3 适用场景当日耗时、服务响应延迟TIMEDIFF最适合用在两个时刻差值不超过一天并且你希望结果以“几小时几分几秒”这种人类可读形式展示的场景。比如客服系统的工单响应耗时SELECT ticket_id, TIMEDIFF(first_response_time, created_time) AS response_time FROM tickets;查询结果直接就是00:23:45业务方一眼就能看懂。再比如定时任务里记录一次跑批的耗时UPDATE batch_task_log SET duration TIMEDIFF(end_time, start_time) WHERE task_id 123;这样日志表里存的是HH:MM:SS格式看日志很方便。如果要做聚合分析再配合TIME_TO_SEC转秒。5. 一个订单场景串起三个函数什么时候该用哪个光讲函数不串业务知识是散的。下面用一个电商订单的完整生命周期把三个函数全部用上顺便把最容易踩的坑点火起来。假设有一张订单表CREATE TABLE orders ( id INT PRIMARY KEY, order_time DATETIME, pay_time DATETIME, deliver_time DATETIME, receive_time DATETIME );现在业务方提出三个统计需求。5.1 从下单到收货每一段耗时怎么算需求一统计“下单后到支付完成”的平均耗时精确到分钟。这个用TIMESTAMPDIFF最合适SELECT AVG(TIMESTAMPDIFF(MINUTE, order_time, pay_time)) AS avg_pay_minutes FROM orders WHERE pay_time IS NOT NULL AND order_time IS NOT NULL;需求二统计“发货后超过7天仍未收货”的订单数。这里业务问的是自然日用DATEDIFF就行SELECT COUNT(*) FROM orders WHERE receive_time IS NULL AND DATEDIFF(CURDATE(), deliver_time) 7;需求三看看今天每个订单从支付到发货的耗时以HH:MM:SS格式导出给运营看。用TIMEDIFFSELECT id, TIMEDIFF(deliver_time, pay_time) AS deliver_duration FROM orders WHERE DATE(deliver_time) CURDATE() AND pay_time IS NOT NULL AND deliver_time IS NOT NULL;一个流转链路下来三个函数各管一段谁也不会越界。5.2 WHERE过滤里的函数与索引陷阱这是我在实际项目里反复强调的一个问题不要在 WHERE 条件里对索引列使用函数。比如-- 不推荐只能全表扫 SELECT * FROM orders WHERE TIMESTAMPDIFF(DAY, order_time, NOW()) 7; -- 推荐改写为范围查询能走索引 SELECT * FROM orders WHERE order_time NOW() - INTERVAL 7 DAY;TIMESTAMPDIFF(DAY, order_time, NOW())在每一行上都要计算一次MySQL 无法用order_time上的索引加速。改写后变成order_time NOW() - INTERVAL 7 DAY这是一个常规的范围条件索引生效数据量大的时候性能差距是数量级的。DATEDIFF和TIMEDIFF也一样-- 不推荐 WHERE DATEDIFF(expire_date, CURDATE()) 30; -- 推荐 WHERE expire_date CURDATE() INTERVAL 30 DAY AND expire_date CURDATE();写 SQL 的时候可以养成的习惯函数作用在常量一侧不要作用在列上。这样既能绕开函数导致的索引失效也让 SQL 语义更清楚。5.3 NULL传递、类型转换等细节三个时间差函数对 NULL 的处理是完全一致的任何一个参数为 NULL结果就是 NULL。这个特性在LEFT JOIN场景里尤其容易埋雷。比如你关联会员表有些用户没有购买记录关联字段是 NULL那么DATEDIFF(expire_date, CURDATE())也是 NULL。如果你直接写WHERE DATEDIFF(expire_date, CURDATE()) 30这些 NULL 行会被过滤掉最后统计出的“即将到期用户数”偏少。正确的做法是先排除 NULL或者用COALESCE给一个明确的默认值SELECT u.user_id, COALESCE(DATEDIFF(m.expire_date, CURDATE()), 0) AS remain_days FROM users u LEFT JOIN memberships m ON u.user_id m.user_id;另外几个函数对字符串参数的容忍度很高DATEDIFF(2024-03-01, 2024-02-28)这种写法没问题MySQL 会自动转成日期。但前提是字符串格式符合日期解析规则最稳妥的是统一使用YYYY-MM-DD格式。如果遇到2024/03/01这种歧义格式不同版本的行为可能不一致生产环境里不要赌这个。6. 几张速查表和几个值得记住的教训文章最后把我觉得最值得收藏的内容整理成速查表和经验清单。6.1 一分钟选型表业务问法推荐函数写法示例两个日期隔了多少天DATEDIFFDATEDIFF(CURDATE(), create_date)两个时刻隔了多少小时/分钟/秒TIMESTAMPDIFFTIMESTAMPDIFF(MINUTE, start_time, end_time)两个时刻隔了多少月/年TIMESTAMPDIFFTIMESTAMPDIFF(MONTH, start_date, end_date)想把耗时显示成几小时几分几秒TIMEDIFFTIMEDIFF(end_time, start_time)耗时超过35天TIMESTAMPDIFFTIMESTAMPDIFF(HOUR, start_time, end_time)参数顺序再强调一次DATEDIFF(date1, date2)date1 减 date2TIMESTAMPDIFF(unit, datetime1, datetime2)datetime2 减 datetime1TIMEDIFF(time1, time2)time1 减 time2TIMESTAMPDIFF和另外两个的顺序是反的写的时候脑子里过一遍避免低级错误。6.2 几个可以直接抄的坑位清单第一TIMESTAMPDIFF(MONTH, ...)和TIMESTAMPDIFF(YEAR, ...)是按“日历格数”算的不看是否满整月整年。算年龄必须补判断否则生日前会虚增一岁。第二TIMEDIFF返回的是 TIME 类型最大值只有838小时超过35天不能用。第三WHERE条件里不要用函数包裹索引列先算常量再比较否则索引失效大表直接卡死。第四三个函数遇到 NULL 都会传染做 LEFT JOIN 关联时先补默认值或者加 IS NOT NULL 过滤。6.3 我做项目时形成的习惯现在我处理时间差需求会先在脑子里把业务层的问题翻译成“日历格数”还是“物理时长”。“隔了几天”“跨了几个月”属于日历格数“这道接口耗时多少毫秒”“这段视频播放了多久”属于物理时长。前者优先DATEDIFF和TIMESTAMPDIFF后者优先TIMESTAMPDIFF和TIME_TO_SEC的组合只有展示层需要HH:MM:SS格式时才用TIMEDIFF。这个分类方式帮我少踩了很多坑也建议刚接触的同学这样去记忆。这篇先把时间差函数讲透了MySQL 里跟时间相关的其实还有一套东西日期加减、日期格式化、时区转换每一个拿出来都能单独写一篇。后面有时间继续更先把这三个 DIFF 用熟已经能解决日常开发里八成以上的时间差需求了。