做后端开发的迟早要跟MySQL的日期时间函数打交道。无论是报表里的“近7天”“本月”“去年同期”还是业务表里的创建时间、更新时间、过期时间几乎每个系统都绕不开日期时间的处理。很多人一开始觉得日期函数无非就是NOW()、DATE_FORMAT()那几个真到了项目里才发现坑一个接一个时区对不上、索引没走、闰年算错、格式化格式写错。这篇文章我就把这些年写MySQL日期时间相关SQL的经验一次性倒出来从字段类型选择到函数拆解从业务场景到性能优化再整理一份排查速查表尽量让你看完就能直接去项目里用。1. 先搞清楚MySQL里的日期时间到底存成什么1.1 五种日期时间类型先选对类型再谈函数MySQL里面常用的日期时间类型一共有五个YEAR、DATE、TIME、DATETIME、TIMESTAMP。看起来简单但选错类型带来的麻烦比函数用错还难改因为类型一旦落库后面想迁移就要动表结构。先说YEAR它只保存年份占1个字节。绝大多数场景用不上但如果你的表只需要按年份做维度统计比如统计“哪一年注册的用户量”用它就很干净。DATE保存“年-月-日”没有时分秒适合生日、节假日、发行日期这种不需要精确时刻的数据。TIME保存“时:分:秒”可以带微秒适合记录时长比如通话时长、视频播放时长而不是某个具体的时刻。DATETIME保存完整的“YYYY-MM-DD HH:MM:SS”占8个字节支持微秒精度是业务表里最常见的选择。TIMESTAMP同样保存完整的日期时间但它跟系统时区挂钩存储时先把本地时间转成UTC查询时再按当前会话时区转回来占4个字节范围到2038年。很多人在DATETIME和TIMESTAMP之间纠结我直接给结论不考虑多时区迁移和2038问题大部分业务表用DATETIME就够了。TIMESTAMP的时区转换在某些场景是优势但如果你没想清楚反而会成为数据对不上的隐患。DATETIME占8字节TIMESTAMP占4字节可这2-4字节的差异在现代服务器上根本不值得牺牲可读性和心智负担。MySQL 8.0之后还可以用DATETIME(6)甚至DATETIME(3)保存微秒和毫秒精度需要精确到毫秒的业务直接用它别再自己转成整数时间戳存了。1.2 为什么不能用字符串存日期这个我必须单独拿出来说因为见过太多新人的建表SQL里写着create_time VARCHAR(32)一眼就知道后面要出事。字符串存日期坏在哪第一空间浪费VARCHAR带变长标识和字符集开销存储效率远不如原生日期类型。第二排序规则是字典序而不是时间序比如“2025-01-31 08:00:00”和“2025-01-31 08:00:01”按字符串比较还可以但一旦格式不统一排序就全乱了。第三做日期运算极其痛苦想DATE_ADD、DATEDIFF还得先STR_TO_DATE转换等于每次查询都多做一遍隐性类型转换。第四脏数据拦不住“2025年1月5号”“2025/1/5”“2025-1-5”这种写法字符串都能存进去用DATE类型直接报错反而是帮你从入口卡住了格式问题。所以建表原则很简单日期就存日期时间就存时间别用字符串打肿脸充胖子。如果你的老表已经用了字符串存日期建议分批迁移。先查一下有没有异常格式比如用SELECT * FROM t WHERE STR_TO_DATE(create_time, %Y-%m-%d %H:%i:%s) IS NULL找出解析不了的数据处理干净后再做ALTER TABLE修改字段类型。不要头铁直接改否则转换失败的数据会全部变成0000-00-00后面排查几个月都找不回原始值。1.3 时区差异TIMESTAMP和DATETIME不只是长度不同时区问题是我见过最容易引发生产事故的点。TIMESTAMP存的是时间戳本质上是UTC时刻读出来的时候MySQL按当前会话的time_zone转成本地时间再返回DATETIME存的是字面值你存进去的是2025-05-10 14:30:00查出来的就是2025-05-10 14:30:00跟时区没有任何关系。这个差异放到全球业务里特别明显。如果你用TIMESTAMP存“用户下单时间”在中国时区下看到的是20:00美国用户看到的却是当地时间07:00这有时候是需求有时候就是事故。需求是“每个用户看自己当地时间”TIMESTAMP省了你自己做时区转换的功夫需求是“大家统一看北京时间”那你用TIMESTAMP就是给自己埋雷每次查出来的时间都随服务器和连接串配置飘忽不定。建议你在应用层统一约定数据库连接串里的time_zone或者在建表时就明确这个字段的时区语义。比如一个多租户系统租户在国内就用DATETIME存北京时间在国外就把存储统一成UTC的DATETIME展示时由后端做转换。最怕的是开发环境一个时区、测试环境一个时区、生产环境又一个时区同样的SQL在不同环境查出来不一样到时候扯皮都扯不清楚。2. 手把手拆解核心日期函数取当前时间、时间戳互转、格式化2.1 取当前时间NOW、CURDATE、CURTIME到底选哪个这一组是使用频率最高的基础函数也是新手最容易混的。NOW()返回当前日期和时间比如2025-05-10 14:30:22CURDATE()只返回日期比如2025-05-10CURTIME()只返回时间比如14:30:22。从需求粒度来选插入订单或日志时通常用NOW()因为要同时知道日期和时刻只想记录“今天是几号”用CURDATE()只想记录“现在是几点”用CURTIME()。还有个容易忽略的细节NOW()在一条语句的执行过程中是固定的它只在语句开始取一次时间不管同一句SQL里出现多少次得到的结果都一样。但SYSDATE()不一样它是实时获取当前时间每运行到一次就会重新取一次。这个差异在批量插入时可能造成同一批数据的时间不一致别小看这个“不一致”等你排查数据链路的时候会怀疑是不是并发写入了两批数据实际上就是SYSDATE()引起的。另外如果你一直在应用层用代码生成当前时间再传给MySQL我建议能省则省直接让MySQL在INSERT时用DEFAULT CURRENT_TIMESTAMP自动填充。应用服务器和数据库服务器哪怕是同一台机器时钟都可能有几秒偏差更别说分部署部署了。统一让数据库写入时间至少保证数据表里的时间口径一致。2.2 时间戳互转UNIX_TIMESTAMP和FROM_UNIXTIME的那些细节UNIX_TIMESTAMP和FROM_UNIXTIME这组函数主要用于对接外部接口、日志系统和历史遗留数据。UNIX_TIMESTAMP()可以把日期时间转成秒级时间戳FROM_UNIXTIME()把整数时间戳转回可读的日期时间字符串。实际使用中有两个高频坑。第一个是毫秒问题。外部接口给你的时间戳经常是13位的毫秒值比如1747381800000直接传给FROM_UNIXTIME(1747381800000)得到的结果是“1970-01-01 13:xx:xx”这种完全看不懂的时间。正确做法是FROM_UNIXTIME(1747381800000 / 1000)。同理你想把当前时间转成毫秒级时间戳要写UNIX_TIMESTAMP(NOW(3)) * 1000注意NOW(3)表示精确到毫秒但UNIX_TIMESTAMP返回的仍然是秒所以要乘1000。第二个坑是时区影响。FROM_UNIXTIME的转换结果受当前会话的time_zone影响如果你要统一展示北京时间先执行SET time_zone 08:00否则不同连接查出来可能差好几个小时。有人喜欢把所有日期时间都存成整数时间戳觉得省空间、比较快。我不建议这么干。原因是SQL可读性差每次查询都要包一层转换函数还会让索引失效。只有一种场景我可以接受接口对接时需要签名校验或者要跟第三方系统做秒级时间戳比对这时才在应用层用整数时间戳数据库里仍然存DATETIME。2.3 格式化与解析DATE_FORMAT、STR_TO_DATE别再用错DATE_FORMAT是报表SQL里的主力函数作用是把日期时间按指定格式转成字符串。常用的格式符%Y四位年、%m两位月、%d两位日、%H24小时制、%i分钟、%s秒、%W星期几、%M月份英文名、%j一年中的第几天。举个例子统计某天订单按小时的分布核心写法是GROUP BY DATE_FORMAT(create_time, %Y-%m-%d %H:00)这样所有时间都被对齐到了整点再分组统计就很方便。按周的统计用DATE_FORMAT(create_time, %x-%v)它按ISO周计算比%Y-%u在跨年时更稳定。这里有个细节%m是数字月份%M是英文月份名新手把%Y-%M-%d当成“2025-05-10”去用结果得到“2025-May-10”看起来没报错但已经不是你要的格式了。反向解析用STR_TO_DATE把字符串变成日期。它的格式必须和字符串严格匹配比如STR_TO_DATE(2025/05/10, %Y/%m/%d)能成功但STR_TO_DATE(2025-05-10, %Y/%m/%d)会返回NULL。最让人抓狂的是它解析失败不报错只返回NULL如果你没做非空校验统计出来的数据就是零还不容易发现。所以每次用到STR_TO_DATE我都建议先在SELECT里单独跑一次确认解析成功再放进正式SQL。3. 几个真实业务场景直接套用日期函数组合3.1 年龄计算TIMESTAMPDIFF比手算靠谱一百倍计算用户年龄是日期函数的经典考题。新手特别喜欢写(NOW() - birthday) / 365这种写法不仅没有处理闰年还会因为生日日期大于当前日期比如今年还没过生日算出来比实际大一岁。正确姿势是用TIMESTAMPDIFFSELECT name, TIMESTAMPDIFF(YEAR, birthday, CURDATE()) AS age FROM users;TIMESTAMPDIFF支持YEAR、QUARTER、MONTH、WEEK、DAY、HOUR、MINUTE、SECOND等粒度它会自动处理跨年和进位。比如一个孩子是2020年2月29日出生2024年2月28日时TIMESTAMPDIFF(YEAR, 2020-02-29, 2024-02-28)算出来是3到2024年2月29日才算4符合真实年龄的语义手写除法做不到这种细节。DATEDIFF是另一个常见函数只返回两个日期之间相差的天数不关心时分秒。比如计算两个订单之间隔了多少天DATEDIFF(order_b_time, order_a_time)返回值是整数。注意顺序DATEDIFF(A, B)计算的是A减B的天数差如果A在B之前结果就是负数。我还建议大家在做天数差时先明确自己的统计口径是“自然日差”还是“满24小时才差一天”DATEDIFF按自然日算TIMESTAMPDIFF(DAY, ...)也是按自然日算但TIMESTAMPDIFF(HOUR, ...)就是严格按小时差。口径选错报表数据就差一天对账时非常头疼。3.2 本月、上月、本年报表别再写死日期做报表最怕手写死日期比如WHERE create_time 2025-05-01 AND create_time 2025-05-31这种SQL到了下个月就要改代码万一忘了改就出线上事故。正确做法是用函数动态计算周期起点本月初DATE_FORMAT(CURDATE(), %Y-%m-01)上个月月初DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), %Y-%m-01)上个月月末LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 1 MONTH))我举个例子统计本月每天新增用户数SELECT DATE(create_time) AS day, COUNT(*) AS cnt FROM users WHERE create_time DATE_FORMAT(CURDATE(), %Y-%m-01) AND create_time DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 MONTH), %Y-%m-01) GROUP BY day;这里我用月初、下月月初把查询范围左闭右开地覆盖整个月。很多人习惯写成BETWEEN 某月第一天 AND 某月最后一天如果create_time带时分秒最后一天23:59:59之后的微妙级数据就会被漏掉。另外你以为LAST_DAY()能拿到月末最后一刻它只返回日期时间是00:00:00你要的是月末最后一刻还得自己拼23:59:59拼完还可能漏掉微秒。所以干脆统一用“下个周期的起点”作为右边界这个习惯一旦养成边界bug能少一大半。3.3 超时判断与会话时长INTERVAL的灵活用法电商系统里有个经典场景用户下单后15分钟内未支付就自动关单。轮询任务里判断超时的SQL一般是SELECT * FROM orders WHERE status 待支付 AND create_time NOW() - INTERVAL 15 MINUTE;记住INTERVAL的单位是MINUTE别写成MINUTES。这个写法本身没问题但要注意两点一是status和create_time要建联合索引否则定时任务每次扫全表表一大人就傻了二是如果做“超过N分钟”的判断用create_time NOW() - INTERVAL N MINUTE如果做“在N分钟内”的判断用create_time NOW() - INTERVAL N MINUTE不要反过来。会话时长的计算常见于登录日志表。每条记录有login_time、logout_time会话时长用TIMESTAMPDIFF(SECOND, login_time, logout_time)得到秒数再在应用层转成“N小时M分钟”或者“N天N小时”。我的经验是时长统计最好不要在SQL里做舍入比如除以60取整因为不同产品看到“60分钟整”和“59.6分钟”的舍入规则不一样。SQL只负责提供精确秒数舍入策略放到应用层统一管理后面改需求也方便不用再改SQL。4. 日期函数影响性能的几个关键点4.1 索引列上套函数神仙索引也救不了这是MySQL性能优化里最常见的一条但在日期函数上栽跟头的人特别多。比如一个常见错误写法SELECT * FROM orders WHERE DATE(create_time) 2025-05-10;逻辑上没问题但它把create_time每一行都套了一次DATE()函数MySQL没法直接使用索引只能老老实实全表扫描。表只有几千行感觉不出来一旦到了几百万行这个查询能把数据库拖垮。正确写法是改成范围查询SELECT * FROM orders WHERE create_time 2025-05-10 00:00:00 AND create_time 2025-05-11 00:00:00;同理WHERE YEAR(create_time) 2025也不走索引应该改写成create_time 2025-01-01 00:00:00 AND create_time 2026-01-01 00:00:00。别一看执行计划是全表就说“MySQL太慢”先看看是不是你自己用函数把索引废掉了。判断方法很简单用EXPLAIN看type字段如果等于ALL或者index多半就是没走索引。4.2 范围查询的边界BETWEEN不等于左闭右闭BETWEEN是个容易给人一种“我写得很正确”错觉的写法。BETWEEN 2025-05-01 00:00:00 AND 2025-05-31 23:59:59看起来覆盖了完整的一个月但问题在于微妙级精度。如果你的create_time是DATETIME(3)或者DATETIME(6)类型一个订单的时间刚好是2025-05-31 23:59:59.500这个查询直接就把它漏掉了。平时数据量小无所谓月底对账时缺个一两条排查起来极其痛苦。标准写法是左闭右开也就是搭配右边界取下一个周期的起点-- 查询5月份 WHERE create_time 2025-05-01 00:00:00 AND create_time 2025-06-01 00:00:00;这个写法对DATE、DATETIME、DATETIME(6)所有精度都成立不用关心字段有没有微秒。我见过不少老系统就是因为BETWEEN漏数据运营反馈“5月的单量比4月少了”研发查了半天都没找到原因。后来统一改成左闭右开这类问题再没出现过。4.3 大表日期统计优化预聚合、分区与离线兜底如果一张订单表有几千万行你想统计近30天每天的单量和销售额直接GROUP BY DATE(create_time)可能要让MySQL扫一大片索引。这时候我一般按优先级考虑三种方案。第一预聚合。建一张日统计表每天凌晨用定时任务把前一天的数据汇总进去线上报表只查日汇总表。因为历史日期不会变化没必要每次都实时从头算。等这一层缓存建立起后报表查询会快一个数量级。第二按时间分区。MySQL的RANGE分区非常适合按时间归档比如按月分一个区查询的时候只扫最近的分区性能提升很直观。但有个硬约束分区列必须包含在表的主键里所以表结构一开始就要设计好后期再改分区很麻烦。第三如果公司有专门的OLAP引擎比如列存分析型数据库把大范围的时间聚合查询丢过去MySQL专心做在线事务。这是最彻底的解法但不是所有团队都有这个基础设施。另外做“近7天”这种滚动统计时尽量避免用DATE_SUB(CURDATE(), INTERVAL 7 DAY)去查大表全量明细。可以考虑在应用层维护一个最近N天的结果集缓存MySQL这边只负责当日增量统计然后合并计算。这种“离线增量”的组合是我在大促场景里用过多次的稳定方案。5. 常见问题速查与避坑实录5.1 常见报错和现象速查表我把这几年遇到的日期函数相关高频问题整理成一张速查表遇到对不上号的直接对照着排查现象可能原因解决思路DATE_FORMAT返回NULL格式符写错比如%M当数字月份用检查%Y/%m/%d等格式串STR_TO_DATE返回NULL字符串与格式串不一致先单独执行SELECT做验证查询走全表扫描索引列上套了DATE()或YEAR()改成范围查询BETWEEN月底漏数据字段带微秒右边界没覆盖改用和左闭右开写法时间戳转出来是1970年毫秒级时间戳没除以1000先除1000再做FROM_UNIXTIMETIMESTAMP字段查出来时间不对会话时区不一致连接串或会话中SET time_zone统一DATEDIFF结果是负数参数顺序反了DATEDIFF(A,B)代表A减B插入2025-02-30报错用字符串硬塞非法日期使用DATE类型从入口拦截脏数据NOW()和SYSDATE()同批数据时间不一致SYSDATE()实时取当前时间统一使用NOW()固定语句时间这张表是我在各种项目排查中反复用到的。每次遇到日期函数相关异常我不急着改代码而是先跑几个SELECT确认函数本身的输出再往上层追问题。这样往往十分钟就能定位比拿着代码猜快很多。5.2 我自己踩过的三个坑第一个坑是一份月度订单统计报表SQL里用了WHERE create_time BETWEEN 2025-04-01 00:00:00 AND 2025-04-30 23:59:59结果月底对账时发现少了几条记录。排查了大半天后来才意识到4月30日23:59:59之后还有微秒级数据被漏掉了。从那以后我所有日期范围查询一律改成左闭右开再也没有因为边界丢过数据。第二个坑是某个用户表把时间字段存成了整数时间戳产品要按天出活跃报表SQL里到处是FROM_UNIXTIME又慢又难读还要时时惦记时区。后来痛下决心做了一次迁移把整数时间戳统一改成DATETIME字段报表SQL变得清楚多了查询性能也没见下降。那次之后我建表默认DATETIME除非有极强的存储理由否则不用时间戳。第三个坑比较隐蔽是我在某项目中用了SYSDATE()做落库时间。SYSDATE()是实时取的批量插入上百行时每行数据的时间会有轻微差异导致排查数据时看到同一批次的数据时间不一致还以为是并发写入出了问题。后来全部换成NOW()语义就清晰了。只要不是对实时性极其敏感的场景同一个事物里的时间应该保持同一口径这就是我现在的习惯。日期时间函数本身不难难的是建立一套固定的使用习惯范围查询永远左闭右开索引列永远不套函数时间类型能选DATETIME就不选字符串同一个批次的时间用NOW()统一固定。这些习惯看起来都是小事但每一条都能帮你少踩很多年的坑。如果你在实际项目里也遇到过日期函数的奇怪问题不妨先按这个排查思路走一遍大概率能快速定位。