尧图网页设计 
1. 为什么日期函数是SQL查询里最容易出事故的地方先说个我自己的真实经历。有一次业务方要一份“昨天新增用户”的数据报表我当时图省事直接写了一句WHERE create_time 2024-01-15结果跑出来只有零点零几秒的数据量业务方当场就炸了。排查到最后才发现create_time字段里存的是完整的日期时间值比如2024-01-15 14:32:08而我用等号去匹配一个纯日期字符串数据库只会把它当成2024-01-15 00:00:00来比对。这一下就只匹配到了零点整那一秒的数据其余一整天的新增用户全被漏掉了。这个事故里没有任何高深的技术纯粹就是对日期函数和日期类型理解不到位。你如果去翻各类SQL面试题和慢查询优化案例会发现日期相关的坑几乎占据了半壁江山——日期格式不统一、时区换算错误、索引失效、函数用错导致结果差一天这些问题在开发岗、数据分析岗、运维岗里全都出现过。所以这篇博文我想认真聊一聊写SQL查询时常用到的日期函数到底有哪些它们各自解决什么问题在不同数据库里写法有什么差异以及在实战中哪些地方最容易翻车。不论你用MySQL、SQL Server、Oracle还是PostgreSQL这篇内容都能帮你把日期查询这块的地基打扎实避免在“最简单”的地方摔跟头。我把整篇内容按“函数分类 → 数据库差异 → 时区与格式化 → 综合实战 → 性能优化”的顺序来组织。先掌握每个函数是干什么的再理解不同数据库的写法差异最后落到真实业务场景里去组合使用这样你返回去写自己的查询时思路会比之前清晰好几个量级。2. 日期函数的两大核心用途与常用函数概览2.1 用途一对日期时间值做“加工”日期函数的第一类用途是对日期时间值本身做各种转换和计算。举个最直白的例子你有一个订单表里面有下单时间order_time你想知道每个订单是在星期几下的单、是哪个季度下的单、距离现在过去了多少天、或者把时间截断到“天”这个粒度这些都属于对日期值的“加工”。这类需求在业务报表里太常见了。运营要看“本周下单用户分布”财务要看“上季度的收入按月汇总”风控要看“过去30分钟内同一IP的登录次数”每一个都离不开日期函数的支持。2.2 用途二在查询条件里框定时间范围第二类用途是把日期函数用在WHERE子句里帮你筛选出某个时间范围内的数据。这应该是日常使用频率最高的场景。比如最常见的“查最近7天的订单”你可以写WHERE order_time DATE_SUB(NOW(), INTERVAL 7 DAY)也可以写WHERE order_time CURDATE() - INTERVAL 7 DAY这两种写法逻辑上是等价的但背后体现的是你对日期函数理解深度的差异。很多新手只会背第一种写法却不知道为什么用DATE_SUB而不是直接减一个数字也不清楚NOW()和CURDATE()有什么区别。这些细节恰恰是查询结果是否准确的决胜点。2.3 横跨四大数据库的常用日期函数速查表为了让你先有一个全局视角我把MySQL、SQL ServerT-SQL、Oracle、PostgreSQL这四种主流数据库里最常用的日期函数整理成了一张对照表。后面每个函数的细节我都会把它拆开讲透。功能需求MySQLSQL ServerOraclePostgreSQL获取当前日期时间NOW()GETDATE()SYSTIMESTAMPNOW()获取当前日期不含时间CURDATE()CAST(GETDATE() AS DATE)TRUNC(SYSDATE)CURRENT_DATE提取年份YEAR(date)DATEPART(YEAR, date)EXTRACT(YEAR FROM date)EXTRACT(YEAR FROM date)提取月份MONTH(date)DATEPART(MONTH, date)EXTRACT(MONTH FROM date)EXTRACT(MONTH FROM date)提取日DAY(date)DATEPART(DAY, date)EXTRACT(DAY FROM date)EXTRACT(DAY FROM date)日期加减DATE_ADD / DATE_SUBDATEADDADD_MONTHS等date INTERVAL 1 day两个日期相差天数DATEDIFF(date1, date2)DATEDIFF(day, date1, date2)date1 - date2date1 - date2格式化日期DATE_FORMAT(date, fmt)FORMAT(date, fmt)TO_CHAR(date, fmt)TO_CHAR(date, fmt)字符串转日期STR_TO_DATE(str, fmt)CONVERT / CASTTO_DATE(str, fmt)TO_DATE(str, fmt)这张表是我在平时工作中反复要用到的参考基准。你不需要把整张表背下来但建议收藏起来等到真正写查询时对照着查身份就是一张“查字典”。下面我会挑其中最核心的几个函数逐个展开讲清楚它们的原理和典型用法。3. 日期提取与日期计算日常查询中最常调用的两类函数3.1 提取日期中的年、月、日、周、季度提取类函数是理解整个日期函数体系的起点。它们做的事情很简单就是把一个完整的日期时间值里的某个部分单独拿出来。为什么这个能力重要因为业务统计的角度几乎总是落在“年、月、日、周、季度”这些时间粒度上。在MySQL里最基础的是YEAR()、MONTH()、DAY()这三个但我更推荐你尽快掌握EXTRACT()函数因为它在Oracle和PostgreSQL里也能用语法差异极小一套思路走天下。举个例子SELECT EXTRACT(YEAR FROM order_time) AS order_year, EXTRACT(MONTH FROM order_time) AS order_month, EXTRACT(DAY FROM order_time) AS order_day, EXTRACT(WEEK FROM order_time) AS order_week, EXTRACT(QUARTER FROM order_time) AS order_quarter FROM orders;在SQL Server里对应的是DATEPART()函数SELECT DATEPART(YEAR, order_time) AS order_year, DATEPART(MONTH, order_time) AS order_month, DATEPART(DAY, order_time) AS order_day, DATEPART(WEEK, order_time) AS order_week, DATEPART(QUARTER, order_time) AS order_quarter FROM orders;看起来只是把FROM换成了逗号但需要注意SQL Server里的DATEPART(WEEK, ...)返回的是一年中的第几周而DATEPART(WEEKDAY, ...)返回的是星期几1代表周日7代表周六。这个细节非常容易踩坑——如果你直接拿DATEPART(WEEKDAY, order_time)的结果去和“数字2”比对以为代表周二结果很可能是周三甚至别的值因为不同数据库对一周起始日的定义不一样。在Oracle里EXTRACT()只能提取YEAR、MONTH、DAY这三个最基本的字段想提取周、季度就得靠TO_CHAR()配合格式模板来实现比如SELECT TO_CHAR(order_time, WW) AS order_week, -- 一年中的第几周 TO_CHAR(order_time, Q) AS order_quarter -- 季度 FROM orders;实战提醒提取“周”和“季度”时一定要先确认你所使用数据库对“一周从哪天开始”的定义。MySQL默认周日为一周的第一天PostgreSQL默认周一SQL Server可以用DATEFIRST查看和设置。这个差异会直接影响你的周报数据是否准确。3.2 日期加减不只是加一天减一天那么简单日期加减操作在生产环境里的出现频率极高。什么“查近30天的订单”“统计上个月的收入”“计算每个用户的生命周期天数”本质都是日期加减或日期差值的计算。MySQL里的两个核心函数是DATE_ADD()和DATE_SUB()用法非常直接-- 查询24小时前的订单 SELECT * FROM orders WHERE order_time DATE_SUB(NOW(), INTERVAL 24 HOUR); -- 查询3个月后的日期 SELECT DATE_ADD(2024-01-15, INTERVAL 3 MONTH);如果你嫌函数名太长MySQL还提供了一个更简洁的写法直接用加减号配合INTERVAL关键字SELECT NOW() INTERVAL 30 DAY, -- 30天后的当前时间 NOW() - INTERVAL 1 HOUR; -- 1小时前的当前时间在SQL Server里日期加减是通过DATEADD函数完成的。它的语法顺序是DATEADD(interval, number, date)这里的number可以为负数表示往前减-- 查询最近7天的数据SQL Server写法 SELECT * FROM orders WHERE order_time DATEADD(DAY, -7, GETDATE());PostgreSQL的写法更自由一些直接使用INTERVAL字面量做加减SELECT NOW() - INTERVAL 7 days;而Oracle则不太一样内置函数只有ADD_MONTHS专门处理月份加减其他单位的时间加减直接对日期做加减法即可。比如“当前时间往前推3小时”Oracle里可以写成SYSDATE - 3/24因为Oracle里日期类型做加减法时数字1代表1天所以3/24就是3小时。关键理解不同数据库对“日期加减”的实现逻辑不同但核心思想高度一致——不要自己去转换成毫秒或秒来做运算直接用INTERVAL或内置函数这样可读性更高也不容易算错。我见过有同事把日期转成字符串、截断后再拼回去做加减最后结果差了1天还查不出原因完全没必要。3.3 日期差值计算DATEDIFF背后的单位与语义陷阱计算两个日期之间相差多少天、多少小时是又一个高频需求。MySQL的DATEDIFF()函数有一个容易踩坑的点它只计算“日期的差异”完全不理会时间部分。什么意思呢我直接给你看个例子SELECT DATEDIFF(2024-01-16 23:59:59, 2024-01-15 00:00:01);这个查询返回的结果是1。实际上两个时间点之间只差了23小时59分58秒不到24小时但DATEDIFF()是按“日历日”来算的——它只看日期部分2024-01-16减2024-01-15就是1天。如果你需要精确的时间差就不能用DATEDIFF()应该用TIMESTAMPDIFF()并指定单位-- 精确计算小时差结果为23 SELECT TIMESTAMPDIFF(HOUR, 2024-01-15 00:00:01, 2024-01-16 23:59:59);TIMESTAMPDIFF()支持的单位包括MICROSECOND、SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEAR非常灵活。在SQL Server里DATEDIFF函数的第一个参数就是单位而且它同样存在“只看边界”的问题。比如SELECT DATEDIFF(MONTH, 2024-01-31, 2024-02-01);这段查询返回1因为它比较的是月份字段的数值变化2减1等于1系统根本不在意你是不是只隔了一天。这是DATEDIFF系列函数的通用语义特征理解了这个底层逻辑你才能在业务里正确判断是否该用它。3.4 取整与截断DATE_TRUNC和DATE_FORMAT的灵活应用有一类操作非常实用但很多新手不太会主动用就是把时间“截断”到指定粒度。什么叫截断我有一个时间值2024-01-15 14:32:08我只关心它是哪一年、哪一月、哪一天想把后面的时分秒全部清零这就是截断。PostgreSQL里有一个极其优雅的函数叫DATE_TRUNCSELECT DATE_TRUNC(month, TIMESTAMP 2024-01-15 14:32:08); -- 结果2024-01-01 00:00:00 SELECT DATE_TRUNC(day, TIMESTAMP 2024-01-15 14:32:08); -- 结果2024-01-15 00:00:00MySQL里没有直接叫DATE_TRUNC的函数最接近的是DATE_FORMAT()。当你需要按天、按小时做分组统计时DATE_FORMAT()是绝对的主力-- 按天统计订单金额 SELECT DATE_FORMAT(order_time, %Y-%m-%d) AS order_day, SUM(order_amount) AS total_amount FROM orders GROUP BY DATE_FORMAT(order_time, %Y-%m-%d) ORDER BY order_day;这个写法在实际报表中非常常见。%Y表示四位年份%m表示两位月份%d表示两位日期%H表示24小时制的小时。把这几个格式化符号组合起来你就能把时间切到你想要的任意粒度上。SQL Server里对应的写法是FORMAT()函数或者CAST()到DATE类型SELECT FORMAT(order_time, yyyy-MM-dd) AS order_day, SUM(order_amount) AS total_amount FROM orders GROUP BY FORMAT(order_time, yyyy-MM-dd);注意一点FORMAT()函数在SQL Server里虽然好用但性能开销比CONVERT()大不少。如果表里有几十万行以上数据建议优先用CAST(order_time AS DATE)做分组既直观又高效。真实项目里我见过因为滥用FORMAT()导致查询从几百毫秒变十几秒的案例优化空间非常大。4. 不同数据库下的日期函数差异语法对照与选择逻辑4.1 MySQL体系函数丰富但命名与其他库差异明显MySQL的日期函数体系在四大数据库里可以说是最“亲民”的因为它把常用操作都封装成了语义化很强的函数名NOW()、CURDATE()、DATE_ADD()、DATE_SUB()、DATEDIFF()、DATE_FORMAT()、STR_TO_DATE()。这些名字一看就知道是干什么的学习成本很低。但它的劣势你也需要正视MySQL的函数在其他数据库里几乎不能直接通用。比如DATE_FORMAT()的格式符号是%Y-%m-%d到了SQL Server、Oracle里却是yyyy-MM-dd或YYYY-MM-DD的写法。这意味着如果你负责维护多套数据库环境备忘笔记必须做得足够细致否则“同样的格式化逻辑换库重写”这一步就会频繁出错。MySQL里还有一个值得单独强调的是STR_TO_DATE()函数它负责把字符串解析成日期类型SELECT STR_TO_DATE(2024/01/15 14:30:00, %Y/%m/%d %H:%i:%s);这个函数在导入外部数据、ETL清洗环节中特别常用。外部系统导出的CSV里经常有各种非标准格式的日期字符串靠STR_TO_DATE()可以统一转换成日期类型再进行后续计算。4.2 SQL Server体系DATEPART与DATEADD的核心地位SQL ServerT-SQL的日期函数和MySQL差异极大。如果你写惯了MySQL刚切到SQL Server时最容易不适应的是同样是提取年份MySQL是YEAR(date)SQL Server是DATEPART(YEAR, date)同样是日期加减MySQL是DATE_ADD(date, INTERVAL 1 DAY)SQL Server是DATEADD(DAY, 1, date)。这种差异背后并没有深刻的原理纯粹就是两家产品设计历史的路径不同。所以我的建议是你在心里给每种数据库建一个独立的“函数抽屉”写之前先确认当前在哪个抽屉里不要混着记忆。SQL Server里还有一个函数需要特别提醒就是GETDATE()返回的是服务器本地时间而且包含毫秒。如果你做时间比较时不需要毫秒最好先通过CAST做一次类型转换避免边界值对不上SELECT * FROM orders WHERE order_time CAST(GETDATE() AS DATE);这段SQL的意思是把当前时间截断到天再和订单时间比较常用于“查询今天的所有订单”这种场景。4.3 Oracle体系TO_CHAR与TO_DATE的格式化艺术Oracle的日期处理风格是高度依赖格式模板的。它的核心函数就是TO_CHAR()日期转字符串和TO_DATE()字符串转日期所有格式化需求都靠这两个函数扛起来。-- Oracle把日期转成指定格式字符串 SELECT TO_CHAR(SYSDATE, YYYY-MM-DD HH24:MI:SS) FROM dual; -- Oracle把字符串转成日期 SELECT TO_DATE(2024-01-15 14:30:00, YYYY-MM-DD HH24:MI:SS) FROM dual;Oracle格式模板里需要特别留意的坑是HH24和HH的区别。HH24表示24小时制HH表示12小时制。如果你要处理下午2点这种时间用HH会把14:30显示成02:30这在很多报表场景里是致命错误。另外Oracle的SYSDATE只返回服务器本地时间SYSTIMESTAMP则返回带时区信息的完整时间戳。如果你的系统有跨时区业务优先使用SYSTIMESTAMP并且配合AT TIME ZONE做时区转换这样才不会出现“中国这边下午3点美国那边显示的却是凌晨3点”的混乱。4.4 PostgreSQL体系INTERVAL与DATE_TRUNC的灵活组合PostgreSQL在日期函数设计上是公认的“优雅典范”。它的核心优势有两个一个是INTERVAL类型极其灵活另一个是DATE_TRUNC函数强大直观。INTERVAL不只是用在加减法里它本身就是一个独立的数据类型可以直接存储一段时长SELECT order_time, order_time INTERVAL 1 day AS next_day, order_time - INTERVAL 2 hours AS minus_2h FROM orders;DATE_TRUNC和MySQL里的DATE_FORMAT相比最大的优点是它返回的仍然是时间戳类型而不是字符串。这就意味着你截断完可以直接做比较运算不需要再做一次类型转换逻辑链路更短、更不容易出错-- 按小时统计最近24小时的请求量 SELECT DATE_TRUNC(hour, request_time) AS request_hour, COUNT(*) AS request_count FROM user_requests WHERE request_time NOW() - INTERVAL 24 hours GROUP BY DATE_TRUNC(hour, request_time) ORDER BY request_hour;5. 时区与格式化看似简单却频繁翻车的两个深坑5.1 时区问题为什么你查的“今天”和别人查的“今天”对不上时区处理是最容易出“看起来没问题、实际全错”的领域。举个具体场景你的数据库服务器部署在阿里云上海节点服务器时区是Asia/Shanghai订单表里的order_time存储的是北京时间。这一切看起来都没问题。但后来业务出海加拿大的同事通过一个海外节点的应用服务器写入数据应用服务器时区是UTC驱动连接数据库时还自动做了时区转换结果原本应写入北京时间的订单落库变成了UTC时间整个报表数据全都偏了8小时。处理这种问题我个人的经验是遵循三条铁律第一数据落库之前统一时区。不管应用服务器在哪个地区写入数据库前都先把时间转换成UTC存储展示层再根据用户时区做转换。这样数据库里的数据永远是“一个基准时间线”不会乱。第二数据库连接参数里明确指定时区。MySQL的连接串里加上connectionTimeZoneSERVER或forceConnectionTimeZoneToSessiontrue取决于连接驱动版本之类参数让驱动不偷偷做时区换算。Java的JDBC连接如果你不设置serverTimezone旧版本驱动经常默认成UTC这就是很多“晚上跑的定时任务算出来的日期差一天”的元凶。第三查询对比时统一口径。如果存的是UTC查询“今天”就必须先把“今天的零点零分”换算成UTC再查。MySQL里可以用CONVERT_TZ()做显式转换-- 把北京时间转成UTC查询 SELECT * FROM orders WHERE order_time CONVERT_TZ(2024-01-15 00:00:00, 08:00, 00:00);注意CONVERT_TZ()函数在MySQL里依赖时区表数据。如果你的MySQL没有加载过mysql时区表这个函数会直接返回NULL。解决办法是在服务器上执行mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql。5.2 格式化符号的差异%Y、%m、%H和YYYY、MM、HH不是一回事格式化符号的差异是跨数据库开发时最容易掉进去的坑。我在第四节里已经提到了MySQL的%Y-%m-%d %H:%i:%s和SQL Server、Oracle的yyyy-MM-dd HH:mm:ss这两种体系的差异。这里我需要再补一刀即使是同一套体系不同数据库对YYYY和yyyy的处理也可能不一样。Oracle里YYYY表示“年份”但在做日期解析时RRRR和YYYY有细微差别涉及到两位年份转四位年份时的世纪推断规则。SQL Server里yyyy和YYYY在FORMAT()函数里通常是等价的但在某些区域语言设置下输出可能有差异。为了避免这类问题我的做法很简单格式化逻辑尽量收敛到数据库统一的函数里不要散落在各段SQL里各写一套。如果项目里有很多地方都要把日期格式化成yyyy-MM-dd可以直接封装成视图或者SQL函数统一调用避免每个人各写各的、格式七零八落。5.3 索引失效的隐患不要在索引列上套函数这个坑我放在时区和格式化这一节里说是因为它和“日期列使用”的关联度太高了。很多人为了查询方便喜欢在WHERE条件里对日期列调用函数比如下面这样-- 错误示例对索引列使用DATE_FORMAT导致索引失效 SELECT * FROM orders WHERE DATE_FORMAT(order_time, %Y-%m-%d) 2024-01-15;这条SQL虽然能跑出正确结果但它会让数据库放弃索引扫描走全表扫描。原因很简单索引里存的是order_time的原始值你把它套上DATE_FORMAT()之后数据库无法直接利用B树索引去快速定位只能把每一行的原始值都强行计算一遍格式化再和字符串去比等于把整张表“摸”了一遍。正确的写法是直接对原始日期列做范围比较-- 正确示例直接用日期范围查询可以命中索引 SELECT * FROM orders WHERE order_time 2024-01-15 00:00:00 AND order_time 2024-01-16 00:00:00;这个写法的意义在于你不查“格式化之后的字符串等于什么”而是查“原始时间值落在哪个区间内”索引就能派上用场了。这种左闭右开区间 start AND end的写法应当成为你写日期范围查询的默认习惯无论MySQL、SQL Server还是Oracle都适用。6. 回到真实业务从订单统计到用户留存分析的完整SQL实战6.1 场景一统计“近30天”订单量并按天分组这是最经典的日期函数组合场景。我直接给出一段完整的MySQL写法并在注释里说明每个部分的作用SELECT DATE_FORMAT(order_time, %Y-%m-%d) AS order_day, COUNT(DISTINCT order_id) AS order_count, SUM(order_amount) AS total_amount FROM orders WHERE order_time DATE_SUB(CURDATE(), INTERVAL 29 DAY) AND order_time DATE_ADD(CURDATE(), INTERVAL 1 DAY) GROUP BY DATE_FORMAT(order_time, %Y-%m-%d) ORDER BY order_day;这段SQL里有个细节值得单独说为什么是INTERVAL 29 DAY而不是INTERVAL 30 DAY因为“近30天”包含今天在内如果从今天往前推30天再取到今天结束实际上是31天的时间窗口。业务方嘴上说的“近30天”你最好先在需求确认阶段就问清楚“是包含今天的滚动30天还是昨天的30天窗口”这种需求定义不清晰导致的数据口径冲突在跨部门协作里非常常见。6.2 场景二查询“上月”数据自动规避月初月末的边界问题每逢月初就会有同事来问“怎么取上个月的数据最稳”网上很多答案教你用MONTH(NOW()) - 1去算但这样一旦现在是1月MONTH(NOW()) - 1等于0就出bug了。正确做法是使用日期运算把“上个月”的完整区间算出来-- MySQL取上个月1号零点到本月1号零点 SELECT * FROM orders WHERE order_time DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), %Y-%m-01) AND order_time DATE_FORMAT(CURDATE(), %Y-%m-01);这个思路的精髓在于你用“本月1号”减去一个月得到“上月1号”再从“本月1号”作为右边界。无论现在是1月底还是10月底这段SQL都成立不会因为月份天数不同而出错。把这个“区间构造法”想明白了以后“查询本周”“查询本季度”“查询今年”都能用同样的套路推出来。6.3 场景三计算两个时间点的间隔天数与业务含义留存分析里最常做的就是“计算每个用户首次下单和最近一次下单间隔了多少天”。在MySQL里如果只需要整天的差值用DATEDIFF()就够SELECT user_id, MIN(order_time) AS first_order_time, MAX(order_time) AS last_order_time, DATEDIFF(MAX(order_time), MIN(order_time)) AS days_between FROM orders GROUP BY user_id;但如果你需要更细的粒度比如“从首次下单到最近一次下单精确到小时”的间隔就得用TIMESTAMPDIFF()SELECT user_id, TIMESTAMPDIFF(HOUR, MIN(order_time), MAX(order_time)) AS hours_between FROM orders GROUP BY user_id;这里真正的业务难点不是函数怎么用而是你定义“间隔”的语义是什么。是按自然日算按24小时算是否排除下单当天这些口径差异会直接影响留存率、复购率等核心运营指标建议在写SQL之前先和业务方把口径对齐。6.4 场景四按周和季度汇总处理周起始日带来的统计差异按周和季度做汇总时最容易出现的现象是“同一份数据不同人跑出来的周报曲线对不上”。根源通常在于A同事认为周一是一周的开始B同事认为周日是一周的开始数据库的默认设置又各不相同。MySQL里可以使用YEARWEEK()函数但它的第二个参数可以指定周起始日-- 第二个参数为1代表周一为一周的开始 SELECT YEARWEEK(order_time, 1) AS order_week, SUM(order_amount) AS total_amount FROM orders GROUP BY YEARWEEK(order_time, 1) ORDER BY order_week;PostgreSQL里则可以用DATE_TRUNC(week, order_time)它默认周一为一周的开始语义清晰SELECT DATE_TRUNC(week, order_time) AS order_week_start, SUM(order_amount) AS total_amount FROM orders GROUP BY DATE_TRUNC(week, order_time) ORDER BY order_week_start;至于季度汇总Oracle里直接用TO_CHAR(order_time, Q)加上TO_CHAR(order_time, YYYY)就能组合出“2024-Q1”这种展示格式其他数据库可以参考前面速查表里的对应写法来替换。7. 慢查询与日期条件一个DBA视角的性能优化经验7.1 大表日期查询为什么会慢日期条件在日常查询里太常见了表和表之间做关联时也老是绕不开时间范围。一个查询一旦涉及日期字段性能瓶颈通常出在三个地方第一WHERE条件里的日期列被函数包裹导致索引失效这个我在5.3节已经说过了。需要再次强调的是这几乎是慢SQL排查榜单里出现率最高的问题没有之一。第二你使用的是LIKE模糊匹配日期字符串。比如有些遗留系统把时间存成了VARCHAR查询时写成WHERE time_str LIKE 2024-01%来模拟前缀匹配。这种方式在数据量小的时候无所谓一旦数据量上百万这条查询基本就是灾难。第三天真的写法是把日期范围拆成了时间点判断且中间的OR条件太多导致优化器无法高效利用索引。比如WHERE order_time 2024-01-15 00:00:00 OR order_time 2024-01-15 01:00:00这种写法应改成范围查询。7.2 日期范围查询的最优写法建议我这些年帮团队review慢SQL总结出一套日期范围查询的“安全牌”写法几乎可以无脑套用-- 最优写法左闭右开区间直接在原始列上做比较 SELECT * FROM orders WHERE order_time 2024-01-15 00:00:00 AND order_time 2024-01-16 00:00:00这套写法的好处有三个一是order_time上的索引可以用上二是查询结果精确地覆盖了整个1月15日三是逻辑直白后续接手代码的人一眼就能看懂。如果你的场景需要动态计算时间起点也尽量把计算过程放在等号右侧的“常量位置”而不是套在列上-- 动态起点列不被污染 SELECT * FROM orders WHERE order_time DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND order_time DATE_ADD(CURDATE(), INTERVAL 1 DAY)“列不被污染”是整个日期查询优化的核心心法。列始终以一个干净的原始值出现在比较符左侧所有日期计算都在右侧完成数据库就能发挥索引的最大价值。7.3 排查慢日志里的日期慢查询时的三个检查点如果你现在手上有一个慢查询要排查我建议按下面的顺序去定位EXPLAIN出来看type列是不是ALL全表扫描如果是优先检查WHERE条件里有没有对日期列套函数。看key列用到的索引是哪个如果预期要命中的索引没出现在key里大概率是索引没用上。对比过滤后的行数。rows如果接近全表行数说明条件本身选择性太差即使走索引也可能慢这时要思考是否要增加联合索引比如把(user_id, order_time)作为复合索引。我自己干过一件印象很深的事一张千万级的订单表查询“某个用户最近一笔订单”时因为order_time上单独有索引而条件是user_id ? ORDER BY order_time DESC LIMIT 1结果走了全表排序耗时800多毫秒。后来加了一个(user_id, order_time)联合索引同样的查询直接变成几毫秒。日期函数本身没有错错的是索引设计和查询条件之间的匹配关系没有对上。8. 从日期函数到日期思维一些沉淀下来的实战建议前面几节把常用函数、数据库差异、时区、格式化、性能优化都过了一遍。最后这段算是我的个人总结谈不上什么大道理就是一些踩坑之后的体感积累。第一写日期查询前先明确“日期”和“时间”是不是一个概念。很多业务字段名义上是日期实际上存的是完整时间戳。你拿去做精确匹配必然出问题。统一思维模式凡是带时间的字段一律用区间匹配没有例外。第二日期格式化符号要有一个“自己的对照表”。我不建议你每次都在网上临时查建议把自己常用的%Y-%m-%d %H:%i:%s和YYYY-MM-DD HH24:MI:SS两套体系各自抄一份放笔记里顺手添上你手头数据库的特殊细节比如MySQL的%i代表分钟、SQL Server的FORMAT()性能偏弱这些内容都是时间沉淀出来的。第三所有时间范围判断统一用“左闭右开”区间。也就是 start AND end而不是 start AND end。前者天然避免了“23:59:59”这种边界值的争论。关于要不要真的去匹配23:59:59晚一分钟的数据算不算当天这类问题的讨论在需求评审会上没有意义直接用左闭右开把问题消灭在源头。第四留心数据库服务器时区和应用服务器时区的一致性。我遇到过太多“昨天都好好的今天突然不对了”的案例最后查出来是某个中间件更新把应用服务器时区给改了。建议你在项目部署手册里明确规定所有应用服务器一律使用UTC作为内部时间标准日志里记录UTC展示层再转本地时间这种约定一劳永逸。最后再分享一个小技巧。如果你经常做报表可以预先把常用的日期区间写成一个可复用的SQL片段或视图比如“今天”“昨天”“本周”“上月”的起止时间。这样业务方临时要数的时候你直接拼接查询条件就行不用每次从零思考应该用哪个函数响应速度会快非常多。日期函数永远是工具真正值钱的是你对时间口径的理解和把控。 SQL日期函数全解析：从语法差异到索引失效与性能优化实战
1. 为什么日期函数是SQL查询里最容易出事故的地方先说个我自己的真实经历。有一次业务方要一份“昨天新增用户”的数据报表我当时图省事直接写了一句WHERE create_time 2024-01-15结果跑出来只有零点零几秒的数据量业务方当场就炸了。排查到最后才发现create_time字段里存的是完整的日期时间值比如2024-01-15 14:32:08而我用等号去匹配一个纯日期字符串数据库只会把它当成2024-01-15 00:00:00来比对。这一下就只匹配到了零点整那一秒的数据其余一整天的新增用户全被漏掉了。这个事故里没有任何高深的技术纯粹就是对日期函数和日期类型理解不到位。你如果去翻各类SQL面试题和慢查询优化案例会发现日期相关的坑几乎占据了半壁江山——日期格式不统一、时区换算错误、索引失效、函数用错导致结果差一天这些问题在开发岗、数据分析岗、运维岗里全都出现过。所以这篇博文我想认真聊一聊写SQL查询时常用到的日期函数到底有哪些它们各自解决什么问题在不同数据库里写法有什么差异以及在实战中哪些地方最容易翻车。不论你用MySQL、SQL Server、Oracle还是PostgreSQL这篇内容都能帮你把日期查询这块的地基打扎实避免在“最简单”的地方摔跟头。我把整篇内容按“函数分类 → 数据库差异 → 时区与格式化 → 综合实战 → 性能优化”的顺序来组织。先掌握每个函数是干什么的再理解不同数据库的写法差异最后落到真实业务场景里去组合使用这样你返回去写自己的查询时思路会比之前清晰好几个量级。2. 日期函数的两大核心用途与常用函数概览2.1 用途一对日期时间值做“加工”日期函数的第一类用途是对日期时间值本身做各种转换和计算。举个最直白的例子你有一个订单表里面有下单时间order_time你想知道每个订单是在星期几下的单、是哪个季度下的单、距离现在过去了多少天、或者把时间截断到“天”这个粒度这些都属于对日期值的“加工”。这类需求在业务报表里太常见了。运营要看“本周下单用户分布”财务要看“上季度的收入按月汇总”风控要看“过去30分钟内同一IP的登录次数”每一个都离不开日期函数的支持。2.2 用途二在查询条件里框定时间范围第二类用途是把日期函数用在WHERE子句里帮你筛选出某个时间范围内的数据。这应该是日常使用频率最高的场景。比如最常见的“查最近7天的订单”你可以写WHERE order_time DATE_SUB(NOW(), INTERVAL 7 DAY)也可以写WHERE order_time CURDATE() - INTERVAL 7 DAY这两种写法逻辑上是等价的但背后体现的是你对日期函数理解深度的差异。很多新手只会背第一种写法却不知道为什么用DATE_SUB而不是直接减一个数字也不清楚NOW()和CURDATE()有什么区别。这些细节恰恰是查询结果是否准确的决胜点。2.3 横跨四大数据库的常用日期函数速查表为了让你先有一个全局视角我把MySQL、SQL ServerT-SQL、Oracle、PostgreSQL这四种主流数据库里最常用的日期函数整理成了一张对照表。后面每个函数的细节我都会把它拆开讲透。功能需求MySQLSQL ServerOraclePostgreSQL获取当前日期时间NOW()GETDATE()SYSTIMESTAMPNOW()获取当前日期不含时间CURDATE()CAST(GETDATE() AS DATE)TRUNC(SYSDATE)CURRENT_DATE提取年份YEAR(date)DATEPART(YEAR, date)EXTRACT(YEAR FROM date)EXTRACT(YEAR FROM date)提取月份MONTH(date)DATEPART(MONTH, date)EXTRACT(MONTH FROM date)EXTRACT(MONTH FROM date)提取日DAY(date)DATEPART(DAY, date)EXTRACT(DAY FROM date)EXTRACT(DAY FROM date)日期加减DATE_ADD / DATE_SUBDATEADDADD_MONTHS等date INTERVAL 1 day两个日期相差天数DATEDIFF(date1, date2)DATEDIFF(day, date1, date2)date1 - date2date1 - date2格式化日期DATE_FORMAT(date, fmt)FORMAT(date, fmt)TO_CHAR(date, fmt)TO_CHAR(date, fmt)字符串转日期STR_TO_DATE(str, fmt)CONVERT / CASTTO_DATE(str, fmt)TO_DATE(str, fmt)这张表是我在平时工作中反复要用到的参考基准。你不需要把整张表背下来但建议收藏起来等到真正写查询时对照着查身份就是一张“查字典”。下面我会挑其中最核心的几个函数逐个展开讲清楚它们的原理和典型用法。3. 日期提取与日期计算日常查询中最常调用的两类函数3.1 提取日期中的年、月、日、周、季度提取类函数是理解整个日期函数体系的起点。它们做的事情很简单就是把一个完整的日期时间值里的某个部分单独拿出来。为什么这个能力重要因为业务统计的角度几乎总是落在“年、月、日、周、季度”这些时间粒度上。在MySQL里最基础的是YEAR()、MONTH()、DAY()这三个但我更推荐你尽快掌握EXTRACT()函数因为它在Oracle和PostgreSQL里也能用语法差异极小一套思路走天下。举个例子SELECT EXTRACT(YEAR FROM order_time) AS order_year, EXTRACT(MONTH FROM order_time) AS order_month, EXTRACT(DAY FROM order_time) AS order_day, EXTRACT(WEEK FROM order_time) AS order_week, EXTRACT(QUARTER FROM order_time) AS order_quarter FROM orders;在SQL Server里对应的是DATEPART()函数SELECT DATEPART(YEAR, order_time) AS order_year, DATEPART(MONTH, order_time) AS order_month, DATEPART(DAY, order_time) AS order_day, DATEPART(WEEK, order_time) AS order_week, DATEPART(QUARTER, order_time) AS order_quarter FROM orders;看起来只是把FROM换成了逗号但需要注意SQL Server里的DATEPART(WEEK, ...)返回的是一年中的第几周而DATEPART(WEEKDAY, ...)返回的是星期几1代表周日7代表周六。这个细节非常容易踩坑——如果你直接拿DATEPART(WEEKDAY, order_time)的结果去和“数字2”比对以为代表周二结果很可能是周三甚至别的值因为不同数据库对一周起始日的定义不一样。在Oracle里EXTRACT()只能提取YEAR、MONTH、DAY这三个最基本的字段想提取周、季度就得靠TO_CHAR()配合格式模板来实现比如SELECT TO_CHAR(order_time, WW) AS order_week, -- 一年中的第几周 TO_CHAR(order_time, Q) AS order_quarter -- 季度 FROM orders;实战提醒提取“周”和“季度”时一定要先确认你所使用数据库对“一周从哪天开始”的定义。MySQL默认周日为一周的第一天PostgreSQL默认周一SQL Server可以用DATEFIRST查看和设置。这个差异会直接影响你的周报数据是否准确。3.2 日期加减不只是加一天减一天那么简单日期加减操作在生产环境里的出现频率极高。什么“查近30天的订单”“统计上个月的收入”“计算每个用户的生命周期天数”本质都是日期加减或日期差值的计算。MySQL里的两个核心函数是DATE_ADD()和DATE_SUB()用法非常直接-- 查询24小时前的订单 SELECT * FROM orders WHERE order_time DATE_SUB(NOW(), INTERVAL 24 HOUR); -- 查询3个月后的日期 SELECT DATE_ADD(2024-01-15, INTERVAL 3 MONTH);如果你嫌函数名太长MySQL还提供了一个更简洁的写法直接用加减号配合INTERVAL关键字SELECT NOW() INTERVAL 30 DAY, -- 30天后的当前时间 NOW() - INTERVAL 1 HOUR; -- 1小时前的当前时间在SQL Server里日期加减是通过DATEADD函数完成的。它的语法顺序是DATEADD(interval, number, date)这里的number可以为负数表示往前减-- 查询最近7天的数据SQL Server写法 SELECT * FROM orders WHERE order_time DATEADD(DAY, -7, GETDATE());PostgreSQL的写法更自由一些直接使用INTERVAL字面量做加减SELECT NOW() - INTERVAL 7 days;而Oracle则不太一样内置函数只有ADD_MONTHS专门处理月份加减其他单位的时间加减直接对日期做加减法即可。比如“当前时间往前推3小时”Oracle里可以写成SYSDATE - 3/24因为Oracle里日期类型做加减法时数字1代表1天所以3/24就是3小时。关键理解不同数据库对“日期加减”的实现逻辑不同但核心思想高度一致——不要自己去转换成毫秒或秒来做运算直接用INTERVAL或内置函数这样可读性更高也不容易算错。我见过有同事把日期转成字符串、截断后再拼回去做加减最后结果差了1天还查不出原因完全没必要。3.3 日期差值计算DATEDIFF背后的单位与语义陷阱计算两个日期之间相差多少天、多少小时是又一个高频需求。MySQL的DATEDIFF()函数有一个容易踩坑的点它只计算“日期的差异”完全不理会时间部分。什么意思呢我直接给你看个例子SELECT DATEDIFF(2024-01-16 23:59:59, 2024-01-15 00:00:01);这个查询返回的结果是1。实际上两个时间点之间只差了23小时59分58秒不到24小时但DATEDIFF()是按“日历日”来算的——它只看日期部分2024-01-16减2024-01-15就是1天。如果你需要精确的时间差就不能用DATEDIFF()应该用TIMESTAMPDIFF()并指定单位-- 精确计算小时差结果为23 SELECT TIMESTAMPDIFF(HOUR, 2024-01-15 00:00:01, 2024-01-16 23:59:59);TIMESTAMPDIFF()支持的单位包括MICROSECOND、SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEAR非常灵活。在SQL Server里DATEDIFF函数的第一个参数就是单位而且它同样存在“只看边界”的问题。比如SELECT DATEDIFF(MONTH, 2024-01-31, 2024-02-01);这段查询返回1因为它比较的是月份字段的数值变化2减1等于1系统根本不在意你是不是只隔了一天。这是DATEDIFF系列函数的通用语义特征理解了这个底层逻辑你才能在业务里正确判断是否该用它。3.4 取整与截断DATE_TRUNC和DATE_FORMAT的灵活应用有一类操作非常实用但很多新手不太会主动用就是把时间“截断”到指定粒度。什么叫截断我有一个时间值2024-01-15 14:32:08我只关心它是哪一年、哪一月、哪一天想把后面的时分秒全部清零这就是截断。PostgreSQL里有一个极其优雅的函数叫DATE_TRUNCSELECT DATE_TRUNC(month, TIMESTAMP 2024-01-15 14:32:08); -- 结果2024-01-01 00:00:00 SELECT DATE_TRUNC(day, TIMESTAMP 2024-01-15 14:32:08); -- 结果2024-01-15 00:00:00MySQL里没有直接叫DATE_TRUNC的函数最接近的是DATE_FORMAT()。当你需要按天、按小时做分组统计时DATE_FORMAT()是绝对的主力-- 按天统计订单金额 SELECT DATE_FORMAT(order_time, %Y-%m-%d) AS order_day, SUM(order_amount) AS total_amount FROM orders GROUP BY DATE_FORMAT(order_time, %Y-%m-%d) ORDER BY order_day;这个写法在实际报表中非常常见。%Y表示四位年份%m表示两位月份%d表示两位日期%H表示24小时制的小时。把这几个格式化符号组合起来你就能把时间切到你想要的任意粒度上。SQL Server里对应的写法是FORMAT()函数或者CAST()到DATE类型SELECT FORMAT(order_time, yyyy-MM-dd) AS order_day, SUM(order_amount) AS total_amount FROM orders GROUP BY FORMAT(order_time, yyyy-MM-dd);注意一点FORMAT()函数在SQL Server里虽然好用但性能开销比CONVERT()大不少。如果表里有几十万行以上数据建议优先用CAST(order_time AS DATE)做分组既直观又高效。真实项目里我见过因为滥用FORMAT()导致查询从几百毫秒变十几秒的案例优化空间非常大。4. 不同数据库下的日期函数差异语法对照与选择逻辑4.1 MySQL体系函数丰富但命名与其他库差异明显MySQL的日期函数体系在四大数据库里可以说是最“亲民”的因为它把常用操作都封装成了语义化很强的函数名NOW()、CURDATE()、DATE_ADD()、DATE_SUB()、DATEDIFF()、DATE_FORMAT()、STR_TO_DATE()。这些名字一看就知道是干什么的学习成本很低。但它的劣势你也需要正视MySQL的函数在其他数据库里几乎不能直接通用。比如DATE_FORMAT()的格式符号是%Y-%m-%d到了SQL Server、Oracle里却是yyyy-MM-dd或YYYY-MM-DD的写法。这意味着如果你负责维护多套数据库环境备忘笔记必须做得足够细致否则“同样的格式化逻辑换库重写”这一步就会频繁出错。MySQL里还有一个值得单独强调的是STR_TO_DATE()函数它负责把字符串解析成日期类型SELECT STR_TO_DATE(2024/01/15 14:30:00, %Y/%m/%d %H:%i:%s);这个函数在导入外部数据、ETL清洗环节中特别常用。外部系统导出的CSV里经常有各种非标准格式的日期字符串靠STR_TO_DATE()可以统一转换成日期类型再进行后续计算。4.2 SQL Server体系DATEPART与DATEADD的核心地位SQL ServerT-SQL的日期函数和MySQL差异极大。如果你写惯了MySQL刚切到SQL Server时最容易不适应的是同样是提取年份MySQL是YEAR(date)SQL Server是DATEPART(YEAR, date)同样是日期加减MySQL是DATE_ADD(date, INTERVAL 1 DAY)SQL Server是DATEADD(DAY, 1, date)。这种差异背后并没有深刻的原理纯粹就是两家产品设计历史的路径不同。所以我的建议是你在心里给每种数据库建一个独立的“函数抽屉”写之前先确认当前在哪个抽屉里不要混着记忆。SQL Server里还有一个函数需要特别提醒就是GETDATE()返回的是服务器本地时间而且包含毫秒。如果你做时间比较时不需要毫秒最好先通过CAST做一次类型转换避免边界值对不上SELECT * FROM orders WHERE order_time CAST(GETDATE() AS DATE);这段SQL的意思是把当前时间截断到天再和订单时间比较常用于“查询今天的所有订单”这种场景。4.3 Oracle体系TO_CHAR与TO_DATE的格式化艺术Oracle的日期处理风格是高度依赖格式模板的。它的核心函数就是TO_CHAR()日期转字符串和TO_DATE()字符串转日期所有格式化需求都靠这两个函数扛起来。-- Oracle把日期转成指定格式字符串 SELECT TO_CHAR(SYSDATE, YYYY-MM-DD HH24:MI:SS) FROM dual; -- Oracle把字符串转成日期 SELECT TO_DATE(2024-01-15 14:30:00, YYYY-MM-DD HH24:MI:SS) FROM dual;Oracle格式模板里需要特别留意的坑是HH24和HH的区别。HH24表示24小时制HH表示12小时制。如果你要处理下午2点这种时间用HH会把14:30显示成02:30这在很多报表场景里是致命错误。另外Oracle的SYSDATE只返回服务器本地时间SYSTIMESTAMP则返回带时区信息的完整时间戳。如果你的系统有跨时区业务优先使用SYSTIMESTAMP并且配合AT TIME ZONE做时区转换这样才不会出现“中国这边下午3点美国那边显示的却是凌晨3点”的混乱。4.4 PostgreSQL体系INTERVAL与DATE_TRUNC的灵活组合PostgreSQL在日期函数设计上是公认的“优雅典范”。它的核心优势有两个一个是INTERVAL类型极其灵活另一个是DATE_TRUNC函数强大直观。INTERVAL不只是用在加减法里它本身就是一个独立的数据类型可以直接存储一段时长SELECT order_time, order_time INTERVAL 1 day AS next_day, order_time - INTERVAL 2 hours AS minus_2h FROM orders;DATE_TRUNC和MySQL里的DATE_FORMAT相比最大的优点是它返回的仍然是时间戳类型而不是字符串。这就意味着你截断完可以直接做比较运算不需要再做一次类型转换逻辑链路更短、更不容易出错-- 按小时统计最近24小时的请求量 SELECT DATE_TRUNC(hour, request_time) AS request_hour, COUNT(*) AS request_count FROM user_requests WHERE request_time NOW() - INTERVAL 24 hours GROUP BY DATE_TRUNC(hour, request_time) ORDER BY request_hour;5. 时区与格式化看似简单却频繁翻车的两个深坑5.1 时区问题为什么你查的“今天”和别人查的“今天”对不上时区处理是最容易出“看起来没问题、实际全错”的领域。举个具体场景你的数据库服务器部署在阿里云上海节点服务器时区是Asia/Shanghai订单表里的order_time存储的是北京时间。这一切看起来都没问题。但后来业务出海加拿大的同事通过一个海外节点的应用服务器写入数据应用服务器时区是UTC驱动连接数据库时还自动做了时区转换结果原本应写入北京时间的订单落库变成了UTC时间整个报表数据全都偏了8小时。处理这种问题我个人的经验是遵循三条铁律第一数据落库之前统一时区。不管应用服务器在哪个地区写入数据库前都先把时间转换成UTC存储展示层再根据用户时区做转换。这样数据库里的数据永远是“一个基准时间线”不会乱。第二数据库连接参数里明确指定时区。MySQL的连接串里加上connectionTimeZoneSERVER或forceConnectionTimeZoneToSessiontrue取决于连接驱动版本之类参数让驱动不偷偷做时区换算。Java的JDBC连接如果你不设置serverTimezone旧版本驱动经常默认成UTC这就是很多“晚上跑的定时任务算出来的日期差一天”的元凶。第三查询对比时统一口径。如果存的是UTC查询“今天”就必须先把“今天的零点零分”换算成UTC再查。MySQL里可以用CONVERT_TZ()做显式转换-- 把北京时间转成UTC查询 SELECT * FROM orders WHERE order_time CONVERT_TZ(2024-01-15 00:00:00, 08:00, 00:00);注意CONVERT_TZ()函数在MySQL里依赖时区表数据。如果你的MySQL没有加载过mysql时区表这个函数会直接返回NULL。解决办法是在服务器上执行mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql。5.2 格式化符号的差异%Y、%m、%H和YYYY、MM、HH不是一回事格式化符号的差异是跨数据库开发时最容易掉进去的坑。我在第四节里已经提到了MySQL的%Y-%m-%d %H:%i:%s和SQL Server、Oracle的yyyy-MM-dd HH:mm:ss这两种体系的差异。这里我需要再补一刀即使是同一套体系不同数据库对YYYY和yyyy的处理也可能不一样。Oracle里YYYY表示“年份”但在做日期解析时RRRR和YYYY有细微差别涉及到两位年份转四位年份时的世纪推断规则。SQL Server里yyyy和YYYY在FORMAT()函数里通常是等价的但在某些区域语言设置下输出可能有差异。为了避免这类问题我的做法很简单格式化逻辑尽量收敛到数据库统一的函数里不要散落在各段SQL里各写一套。如果项目里有很多地方都要把日期格式化成yyyy-MM-dd可以直接封装成视图或者SQL函数统一调用避免每个人各写各的、格式七零八落。5.3 索引失效的隐患不要在索引列上套函数这个坑我放在时区和格式化这一节里说是因为它和“日期列使用”的关联度太高了。很多人为了查询方便喜欢在WHERE条件里对日期列调用函数比如下面这样-- 错误示例对索引列使用DATE_FORMAT导致索引失效 SELECT * FROM orders WHERE DATE_FORMAT(order_time, %Y-%m-%d) 2024-01-15;这条SQL虽然能跑出正确结果但它会让数据库放弃索引扫描走全表扫描。原因很简单索引里存的是order_time的原始值你把它套上DATE_FORMAT()之后数据库无法直接利用B树索引去快速定位只能把每一行的原始值都强行计算一遍格式化再和字符串去比等于把整张表“摸”了一遍。正确的写法是直接对原始日期列做范围比较-- 正确示例直接用日期范围查询可以命中索引 SELECT * FROM orders WHERE order_time 2024-01-15 00:00:00 AND order_time 2024-01-16 00:00:00;这个写法的意义在于你不查“格式化之后的字符串等于什么”而是查“原始时间值落在哪个区间内”索引就能派上用场了。这种左闭右开区间 start AND end的写法应当成为你写日期范围查询的默认习惯无论MySQL、SQL Server还是Oracle都适用。6. 回到真实业务从订单统计到用户留存分析的完整SQL实战6.1 场景一统计“近30天”订单量并按天分组这是最经典的日期函数组合场景。我直接给出一段完整的MySQL写法并在注释里说明每个部分的作用SELECT DATE_FORMAT(order_time, %Y-%m-%d) AS order_day, COUNT(DISTINCT order_id) AS order_count, SUM(order_amount) AS total_amount FROM orders WHERE order_time DATE_SUB(CURDATE(), INTERVAL 29 DAY) AND order_time DATE_ADD(CURDATE(), INTERVAL 1 DAY) GROUP BY DATE_FORMAT(order_time, %Y-%m-%d) ORDER BY order_day;这段SQL里有个细节值得单独说为什么是INTERVAL 29 DAY而不是INTERVAL 30 DAY因为“近30天”包含今天在内如果从今天往前推30天再取到今天结束实际上是31天的时间窗口。业务方嘴上说的“近30天”你最好先在需求确认阶段就问清楚“是包含今天的滚动30天还是昨天的30天窗口”这种需求定义不清晰导致的数据口径冲突在跨部门协作里非常常见。6.2 场景二查询“上月”数据自动规避月初月末的边界问题每逢月初就会有同事来问“怎么取上个月的数据最稳”网上很多答案教你用MONTH(NOW()) - 1去算但这样一旦现在是1月MONTH(NOW()) - 1等于0就出bug了。正确做法是使用日期运算把“上个月”的完整区间算出来-- MySQL取上个月1号零点到本月1号零点 SELECT * FROM orders WHERE order_time DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), %Y-%m-01) AND order_time DATE_FORMAT(CURDATE(), %Y-%m-01);这个思路的精髓在于你用“本月1号”减去一个月得到“上月1号”再从“本月1号”作为右边界。无论现在是1月底还是10月底这段SQL都成立不会因为月份天数不同而出错。把这个“区间构造法”想明白了以后“查询本周”“查询本季度”“查询今年”都能用同样的套路推出来。6.3 场景三计算两个时间点的间隔天数与业务含义留存分析里最常做的就是“计算每个用户首次下单和最近一次下单间隔了多少天”。在MySQL里如果只需要整天的差值用DATEDIFF()就够SELECT user_id, MIN(order_time) AS first_order_time, MAX(order_time) AS last_order_time, DATEDIFF(MAX(order_time), MIN(order_time)) AS days_between FROM orders GROUP BY user_id;但如果你需要更细的粒度比如“从首次下单到最近一次下单精确到小时”的间隔就得用TIMESTAMPDIFF()SELECT user_id, TIMESTAMPDIFF(HOUR, MIN(order_time), MAX(order_time)) AS hours_between FROM orders GROUP BY user_id;这里真正的业务难点不是函数怎么用而是你定义“间隔”的语义是什么。是按自然日算按24小时算是否排除下单当天这些口径差异会直接影响留存率、复购率等核心运营指标建议在写SQL之前先和业务方把口径对齐。6.4 场景四按周和季度汇总处理周起始日带来的统计差异按周和季度做汇总时最容易出现的现象是“同一份数据不同人跑出来的周报曲线对不上”。根源通常在于A同事认为周一是一周的开始B同事认为周日是一周的开始数据库的默认设置又各不相同。MySQL里可以使用YEARWEEK()函数但它的第二个参数可以指定周起始日-- 第二个参数为1代表周一为一周的开始 SELECT YEARWEEK(order_time, 1) AS order_week, SUM(order_amount) AS total_amount FROM orders GROUP BY YEARWEEK(order_time, 1) ORDER BY order_week;PostgreSQL里则可以用DATE_TRUNC(week, order_time)它默认周一为一周的开始语义清晰SELECT DATE_TRUNC(week, order_time) AS order_week_start, SUM(order_amount) AS total_amount FROM orders GROUP BY DATE_TRUNC(week, order_time) ORDER BY order_week_start;至于季度汇总Oracle里直接用TO_CHAR(order_time, Q)加上TO_CHAR(order_time, YYYY)就能组合出“2024-Q1”这种展示格式其他数据库可以参考前面速查表里的对应写法来替换。7. 慢查询与日期条件一个DBA视角的性能优化经验7.1 大表日期查询为什么会慢日期条件在日常查询里太常见了表和表之间做关联时也老是绕不开时间范围。一个查询一旦涉及日期字段性能瓶颈通常出在三个地方第一WHERE条件里的日期列被函数包裹导致索引失效这个我在5.3节已经说过了。需要再次强调的是这几乎是慢SQL排查榜单里出现率最高的问题没有之一。第二你使用的是LIKE模糊匹配日期字符串。比如有些遗留系统把时间存成了VARCHAR查询时写成WHERE time_str LIKE 2024-01%来模拟前缀匹配。这种方式在数据量小的时候无所谓一旦数据量上百万这条查询基本就是灾难。第三天真的写法是把日期范围拆成了时间点判断且中间的OR条件太多导致优化器无法高效利用索引。比如WHERE order_time 2024-01-15 00:00:00 OR order_time 2024-01-15 01:00:00这种写法应改成范围查询。7.2 日期范围查询的最优写法建议我这些年帮团队review慢SQL总结出一套日期范围查询的“安全牌”写法几乎可以无脑套用-- 最优写法左闭右开区间直接在原始列上做比较 SELECT * FROM orders WHERE order_time 2024-01-15 00:00:00 AND order_time 2024-01-16 00:00:00这套写法的好处有三个一是order_time上的索引可以用上二是查询结果精确地覆盖了整个1月15日三是逻辑直白后续接手代码的人一眼就能看懂。如果你的场景需要动态计算时间起点也尽量把计算过程放在等号右侧的“常量位置”而不是套在列上-- 动态起点列不被污染 SELECT * FROM orders WHERE order_time DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND order_time DATE_ADD(CURDATE(), INTERVAL 1 DAY)“列不被污染”是整个日期查询优化的核心心法。列始终以一个干净的原始值出现在比较符左侧所有日期计算都在右侧完成数据库就能发挥索引的最大价值。7.3 排查慢日志里的日期慢查询时的三个检查点如果你现在手上有一个慢查询要排查我建议按下面的顺序去定位EXPLAIN出来看type列是不是ALL全表扫描如果是优先检查WHERE条件里有没有对日期列套函数。看key列用到的索引是哪个如果预期要命中的索引没出现在key里大概率是索引没用上。对比过滤后的行数。rows如果接近全表行数说明条件本身选择性太差即使走索引也可能慢这时要思考是否要增加联合索引比如把(user_id, order_time)作为复合索引。我自己干过一件印象很深的事一张千万级的订单表查询“某个用户最近一笔订单”时因为order_time上单独有索引而条件是user_id ? ORDER BY order_time DESC LIMIT 1结果走了全表排序耗时800多毫秒。后来加了一个(user_id, order_time)联合索引同样的查询直接变成几毫秒。日期函数本身没有错错的是索引设计和查询条件之间的匹配关系没有对上。8. 从日期函数到日期思维一些沉淀下来的实战建议前面几节把常用函数、数据库差异、时区、格式化、性能优化都过了一遍。最后这段算是我的个人总结谈不上什么大道理就是一些踩坑之后的体感积累。第一写日期查询前先明确“日期”和“时间”是不是一个概念。很多业务字段名义上是日期实际上存的是完整时间戳。你拿去做精确匹配必然出问题。统一思维模式凡是带时间的字段一律用区间匹配没有例外。第二日期格式化符号要有一个“自己的对照表”。我不建议你每次都在网上临时查建议把自己常用的%Y-%m-%d %H:%i:%s和YYYY-MM-DD HH24:MI:SS两套体系各自抄一份放笔记里顺手添上你手头数据库的特殊细节比如MySQL的%i代表分钟、SQL Server的FORMAT()性能偏弱这些内容都是时间沉淀出来的。第三所有时间范围判断统一用“左闭右开”区间。也就是 start AND end而不是 start AND end。前者天然避免了“23:59:59”这种边界值的争论。关于要不要真的去匹配23:59:59晚一分钟的数据算不算当天这类问题的讨论在需求评审会上没有意义直接用左闭右开把问题消灭在源头。第四留心数据库服务器时区和应用服务器时区的一致性。我遇到过太多“昨天都好好的今天突然不对了”的案例最后查出来是某个中间件更新把应用服务器时区给改了。建议你在项目部署手册里明确规定所有应用服务器一律使用UTC作为内部时间标准日志里记录UTC展示层再转本地时间这种约定一劳永逸。最后再分享一个小技巧。如果你经常做报表可以预先把常用的日期区间写成一个可复用的SQL片段或视图比如“今天”“昨天”“本周”“上月”的起止时间。这样业务方临时要数的时候你直接拼接查询条件就行不用每次从零思考应该用哪个函数响应速度会快非常多。日期函数永远是工具真正值钱的是你对时间口径的理解和把控。