做SQL查询久了你会发现一个规律十张业务表里至少有八张带着日期字段。订单有下单时间用户有注册时间日志有写入时间几乎所有的数据分析、报表统计、数据清洗最后都绕不开跟日期打交道。而“写SQL时最常用的日期函数”这个话题看着基础实际上坑特别多。我见过不少人因为日期格式转换踩坑、因为日期比较写错导致索引失效、因为函数选型不当让慢查询雪上加霜。这篇文章就把我在实际工作中反复用到的日期函数用法、场景和坑一次性说清楚不管你是刚入门的新手还是写了一两年SQL的工程师都能从中找到能直接拿去用的东西。1. 为什么日期函数是SQL查询的重灾区1.1 日期数据的真实样子比你想象的乱很多人觉得日期字段不就是“2024-01-15”嘛能有多复杂等你真正接手一套跑了五六年的业务库你就知道日期字段能乱成什么样。有的是字符串存成“2024/01/15”有的是时间戳存成1705305600这种有的精确到毫秒存成2024-01-15 10:23:45.123还有的干脆存成“20240115”这种八位数字。你光是把这些格式统一起来就得费不少功夫。更重要的是业务系统不同模块的日期格式还经常不一致。比如订单表用DATETIME用户表用VARCHAR日志表用BIGINT时间戳。当你做跨表关联或者汇总统计时日期函数就成了唯一能把这些格式拧到一块的工具。所以与其问“为什么日期函数重要”不如问“没有日期函数我怎么处理这些乱七八糟的日期格式”。1.2 日期函数解决了哪三类核心问题实际工作中日期函数主要解决三类问题你可以对照自己的业务看看是不是这么回事第一类格式化与解析。把“20240115”变成“2024-01-15”把字符串变成日期类型或者反过来把日期按指定格式输出到报表里。这类需求在数据导出、接口对接、报表展示时特别多。比如你导出数据给业务方对方要求日期必须是“2024年1月15日”这种中文格式你就得靠格式化函数处理。第二类日期计算与偏移。计算两个日期之间差了多少天、多少月给定一个日期推算出它7天前是哪天、下个月的第一天是哪天判断某个日期是星期几、是当年的第几周。这类函数在周期报表、账期计算、会员生命周期分析里用得非常频繁。第三类日期截断与聚合。把精确到秒的时间戳截断到天、月、季度、年再配合GROUP BY做聚合统计。这就是“按天统计订单量”“按月统计销售额”“按季度统计新增用户”的核心逻辑。没有日期截断函数你就得自己写一堆CASE WHEN去拼分组逻辑又丑又容易错。1.3 不同数据库的日期函数差异必须心中有数搞SQL的人最容易忽略的一件事日期函数在不同数据库里名字和用法差异很大。SQL Server里有DATEADDMySQL里叫DATE_ADDSQL Server用DATEDIFF算天数差MySQL对应的是DATEDIFF但算月份差又得用TIMESTAMPDIFFOracle里格式化用TO_CHARSQL Server用FORMAT或CONVERTMySQL用DATE_FORMAT。这意味着你今天在MySQL上写好的日期逻辑换到SQL Server或者PostgreSQL上很可能直接报语法错误。所以不要问我“哪个数据库的日期函数最好用”先搞清楚你现在用的是哪个数据库再去查对应的函数文档。后文我会按主流数据库分别讲方便你对号入座。2. 主流数据库日期函数速查与核心逻辑2.1 SQL ServerDATEADD、DATEDIFF、DATEPART三件套SQL Server是很多企业级应用的首选数据库它的日期函数体系比较独立重点记住三件套就够了。DATEADD用来做日期加减。比如DATEADD(DAY, 7, GETDATE())就是当前时间加7天DATEADD(MONTH, -1, GETDATE())是当前时间减一个月。注意单位参数是第一个别写反了。单位可以是YEAR、QUARTER、MONTH、WEEK、DAY、HOUR、MINUTE、SECOND基本覆盖所有时间粒度。DATEDIFF用来算两个时间点之间的差值。DATEDIFF(DAY, 开始日期, 结束日期)返回天数差DATEDIFF(MONTH, 开始日期, 结束日期)返回月份差。这里有个细节容易忽略DATEDIFF是按“跨越了多少个边界”算的不是按整整24小时算的。比如2024-01-01 23:59:59和2024-01-02 00:00:01虽然实际差了2秒但DATEDIFF(DAY, ...)结果是1天。这在某些精确计算场景是个大坑后面我会细说。DATEPART用来提取日期的一部分。DATEPART(YEAR, 日期)拿年份DATEPART(WEEK, 日期)拿到当年的第几周DATEPART(WEEKDAY, 日期)拿星期几。SQL Server里默认一周从周日开始所以DATEPART(WEEKDAY, 2024-01-15)返回2周一如果你希望一周从周一开始计算要用SET DATEFIRST调整这个细节在周报统计时非常关键。另外SQL Server还有一个容易被性能问题缠上的函数FORMAT。它写法优雅FORMAT(GETDATE(), yyyy-MM-dd)就能格式化日期中文年月日也能搞定。但FORMAT底层走的是.NET的格式化逻辑性能比CONVERT差了不止一个数量级。我在后面讲到索引和性能的部分会专门说这个坑。常用示例-- 当前日期时间 SELECT GETDATE(); -- 2024-01-15 10:23:45.123 -- 取当天零点 SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0); -- 上个月第一天 SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0); -- 当前是几号 SELECT DATEPART(DAY, GETDATE()); -- 字符串转日期 SELECT CONVERT(DATETIME, 2024-01-15 10:00:00, 120);2.2 MySQLDATE_FORMAT、DATE_ADD、DATEDIFF组合拳MySQL在互联网场景用得最多日期函数也相对友好。几个核心函数我逐个讲。**NOW()和CURDATE()**分别返回当前日期时间和当前日期。CURDATE()返回的只有日期部分是“2024-01-15”这种格式。如果只需要当前日期用CURDATE()比NOW()更语义化还能避免后续误用到时间部分。DATE_FORMAT是MySQL里最灵活的格式化函数。DATE_FORMAT(NOW(), %Y-%m-%d %H:%i:%s)能按你指定的格式输出日期。注意百分号加字母这套格式跟SQL Server的格式码完全不同。常用的格式码%Y四位年份%y两位年份%m两位月份%c数字月份不补零%d两位日%e数字日不补零%H24小时制%i分钟%s秒%W星期名%a缩写星期名%M月份名。这个函数在报表输出中出场率极高。DATE_ADD和DATE_SUB做日期加减。DATE_ADD(NOW(), INTERVAL 7 DAY)是加7天DATE_ADD(NOW(), INTERVAL 1 MONTH)是加一个月。INTERVAL后面跟的数字和单位可以组合支持MICROSECOND、SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEAR等。MySQL没有单独的DATESUB而是通过DATE_ADD传负数实现比如DATE_ADD(NOW(), INTERVAL -1 DAY)。DATEDIFF在MySQL里只用来算天数差DATEDIFF(2024-01-15, 2024-01-10)返回5。如果你想精确计算两个时间戳之间差了几个小时、几分钟要用TIMESTAMPDIFF(HOUR, 开始, 结束)。TIMESTAMPDIFF比DATEDIFF强大得多第一个参数可以是SECOND、MINUTE、HOUR、DAY、MONTH、YEAR而且是按实际长度计算的不会出现SQL Server那种“跨边界就算一天”的问题。DATE_TRUNC的MySQL替代法是DATE()函数。比如DATE(NOW())就是截断到天。要截断到月可以配合DATE_FORMAT实现比如DATE_FORMAT(NOW(), %Y-%m-01)就是当月的第一天。要截断到周用YEARWEEK()函数。LAST_DAY返回某个月的最后一天。SELECT LAST_DAY(2024-02-05)返回2024-02-29处理闰年特别省心不用自己判断2月有28天还是29天。常用示例-- 当前日期时间 SELECT NOW(); -- 2024-01-15 10:23:45 -- 今天的日期 SELECT CURDATE(); -- 2024-01-15 -- 格式化为 2024年01月15日 SELECT DATE_FORMAT(NOW(), %Y年%m月%d日); -- 最近7天的订单 SELECT * FROM orders WHERE order_time DATE_SUB(CURDATE(), INTERVAL 7 DAY); -- 两个时间戳之间差了多少小时 SELECT TIMESTAMPDIFF(HOUR, 2024-01-15 08:00:00, 2024-01-15 18:30:00); -- 结果是10 -- 判断某个日期所在月的最后一天 SELECT LAST_DAY(2024-02-05); -- 2024-02-292.3 PostgreSQL与Oracle各具特色的日期处理方案PostgreSQL的日期处理能力非常强它甚至支持直接用日期加减整数。比如CURRENT_DATE 7就是7天后的日期CURRENT_DATE - 1就是昨天。这种运算符重载的设计让PostgreSQL的日期计算写起来特别自然。它还有DATE_TRUNC函数DATE_TRUNC(month, CURRENT_DATE)返回当月第一天DATE_TRUNC(week, CURRENT_DATE)返回本周起始日在做时间序列分析时非常好用。EXTRACT可以从时间戳里提取年、月、日、时、分、秒、季度、周EXTRACT(YEAR FROM TIMESTAMP 2024-01-15 10:00:00)返回2024。AGE函数用来计算年龄AGE(2024-01-15, 1990-05-20)会返回33年8月5日这种格式做用户年龄统计比人工算省事太多。格式化用TO_CHAR解析字符串用TO_DATE。Oracle作为老牌商业数据库日期体系相对封闭但功能完备。核心是TO_DATE和TO_CHAR一个负责字符串转日期一个负责日期转字符串。Oracle的日期格式符用单字母或双字母组合比如YYYY-MM-DD HH24:MI:SS跟MySQL的%Y%m%d风格完全不同。然后是ADD_MONTHS专门用来加月份ADD_MONTHS(DATE 2024-01-31, 1)结果是2024-02-29Oracle会自动处理月末对齐这一点比很多数据库做得聪明。MONTHS_BETWEEN返回两个日期之间相差的月份数可以带小数。EXTRACT和PostgreSQL类似EXTRACT(YEAR FROM SYSDATE)取年份。还有个TRUNC函数TRUNC(SYSDATE)截断到天TRUNC(SYSDATE, MM)返回月初TRUNC(SYSDATE, WW)返回周初功能上类似PostgreSQL的DATE_TRUNC但参数风格不同。Oracle默认日期格式是DD-MON-YY比如15-JAN-24这个格式特别容易让人踩坑因为你直接用字符串跟日期比较时Oracle会按这个默认格式解析字符串导致很多看起来没问题的SQL在Oracle上报“ORA-01843: not a valid month”错误。2.4 一张表理清日期格式化的标准写法功能描述SQL ServerMySQLPostgreSQLOracle当前日期时间GETDATE()NOW()CURRENT_TIMESTAMPSYSDATE当前日期CAST(GETDATE() AS DATE)CURDATE()CURRENT_DATETRUNC(SYSDATE)日期加减天数DATEADD(DAY, 7, GETDATE())DATE_ADD(NOW(), INTERVAL 7 DAY)CURRENT_DATE 7SYSDATE 7日期加减月份DATEADD(MONTH, 2, GETDATE())DATE_ADD(NOW(), INTERVAL 2 MONTH)CURRENT_DATE INTERVAL 2 monthsADD_MONTHS(SYSDATE, 2)两个日期差天DATEDIFF(DAY, 日期1, 日期2)DATEDIFF(日期1, 日期2)日期2 - 日期1日期2 - 日期1提取年份DATEPART(YEAR, 日期)YEAR(日期)EXTRACT(YEAR FROM 日期)EXTRACT(YEAR FROM 日期)格式化CONVERT(VARCHAR, 日期, 120)DATE_FORMAT(日期, %Y-%m-%d)TO_CHAR(日期, YYYY-MM-DD)TO_CHAR(日期, YYYY-MM-DD)当月最后一天EOMONTH(日期)LAST_DAY(日期)DATE_TRUNC(month, 日期) INTERVAL 1 month - 1 dayLAST_DAY(日期)这张表你可以保存下来以后在不同数据库之间切换时对照着查比我上面啰嗦的几千字直观得多。3. 高频实操搞定5个真实业务场景3.1 按天/周/月/季度分组统计这是报表需求里的王者场景。随便一个运营提需求就是“给我看下这个月每天的订单数”。常规写法是GROUP BY日期字段但直接把DATETIME字段放GROUP BY里会精确到时分秒导致本来想按天聚合结果同一秒内十几条订单被拆成好几组。正确做法是先把日期截断到天再分组。MySQL下用DATE_FORMAT(order_time, %Y-%m-%d)SQL Server用CONVERT(VARCHAR(10), order_time, 120)PostgreSQL用DATE_TRUNC(day, order_time)或直接CAST(order_time AS DATE)Oracle用TRUNC(order_time)。如果是按周统计就要考虑“一周从哪天开始”的问题。MySQL里YEARWEEK(order_time, 1)表示周一开始的一周YEARWEEK(order_time, 0)是周日开始。SQL Server里DATEPART(WEEK, order_time)的结果受DATEFIRST影响你得确认数据库默认的每周起始日是什么。PostgreSQL的DATE_TRUNC(week, 日期)默认从周一开始。按季度统计MySQL用QUARTER()函数SQL Server用DATEPART(QUARTER, 日期)PostgreSQL和Oracle用EXTRACT(QUARTER FROM 日期)。一个典型的月报SQL长这样MySQL版SELECT DATE_FORMAT(order_time, %Y-%m) AS order_month, COUNT(*) AS order_count, SUM(order_amount) AS total_amount FROM orders WHERE order_time 2024-01-01 AND order_time 2024-04-01 GROUP BY DATE_FORMAT(order_time, %Y-%m) ORDER BY order_month;这里要注意WHERE的写法。我见过很多新手喜欢写WHERE DATE_FORMAT(order_time, %Y-%m) 2024-01看起来没毛病实际性能很差。因为你在日期字段上套了函数之后索引基本就废了后面讲性能的部分我再展开。3.2 计算同比环比运营会看去年同期数据、上个月数据这就是同比环比。核心逻辑是用日期加减函数定位到比较基准期然后JOIN或子查询对比。以“本月销售额环比上月”为例在MySQL下可以这样写SELECT DATE_FORMAT(now_month.order_time, %Y-%m) AS month, now_month.total_amount AS current_amount, last_month.total_amount AS previous_amount, ROUND((now_month.total_amount - last_month.total_amount) / last_month.total_amount * 100, 2) AS mom_ratio FROM (SELECT DATE_ADD(CURDATE(), INTERVAL 1 - DAY(CURDATE()) DAY) AS month_start, SUM(amount) AS total_amount FROM orders WHERE order_time DATE_FORMAT(CURDATE(), %Y-%m-01) AND order_time DATE_ADD(DATE_FORMAT(CURDATE(), %Y-%m-01), INTERVAL 1 MONTH) GROUP BY DATE_FORMAT(order_time, %Y-%m)) now_month LEFT JOIN (SELECT SUM(amount) AS total_amount FROM orders WHERE order_time DATE_ADD(DATE_FORMAT(CURDATE(), %Y-%m-01), INTERVAL -1 MONTH) AND order_time DATE_FORMAT(CURDATE(), %Y-%m-01) GROUP BY DATE_FORMAT(order_time, %Y-%m)) last_month ON 1 1;这个SQL有几个关键点值得你仔细看。第一用DATE_FORMAT(CURDATE(), %Y-%m-01)拿到当月第一天的日期再配合INTERVAL加减拿到上个月的第一天。这样写的好处是不管今天几号我统计的都是完整月份的数据不会把“今天以前”和“整月”搞混。第二LEFT JOIN的条件写成了ON 1 1因为两个子查询都只返回一行这种写法可以让两个聚合结果直接横向比较。第三过滤条件都写成了起始日期大于等于、结束日期小于下月月初的开闭区间不会漏掉边界数据也不会重复统计。同比运算逻辑完全一样无非是把“往前减1个月”改成“往前减12个月”。3.3 日期区间查询的正确打开方式最常见的需求是“查最近7天的订单”“查本月的注册用户”“查上一季度的退款单”。这里有个核心原则能用日期范围比较就别在日期字段上包函数。举个例子。想查昨天一整天的订单最直觉的写法是-- 错误示范MySQL SELECT * FROM orders WHERE DATE(order_time) DATE_SUB(CURDATE(), INTERVAL 1 DAY);这写法看着干净但如果你在order_time上有索引ORDER_TIME的索引大概率失效。因为MySQL要对每行的order_time先执行DATE()函数才能跟后面的日期比较。数据量一大这条SQL就是全表扫描的命。正确写法是区间比较-- 正确示范MySQL SELECT * FROM orders WHERE order_time DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND order_time CURDATE();这样写order_time上的索引直接被利用走得是索引范围扫描性能差出几十倍不止。SQL Server下也有同样的问题。很多人在SQL Server里写WHERE CONVERT(VARCHAR(10), order_time, 120) 2024-01-14一样让索引失效。正确写法是中间变量或者参数化写法DECLARE start_date DATETIME 2024-01-14 00:00:00; DECLARE end_date DATETIME 2024-01-15 00:00:00; SELECT * FROM orders WHERE order_time start_date AND order_time end_date;有人说我用的JDBC、MyBatis没法声明变量。那也可以直接在SQL里写条件意思一样。这里再提醒一个SQL Server特有细节。DATETIME类型的精度约为3.33毫秒你用order_time 2024-01-15去查“1月14日一整天”的订单看起来没问题实际上会漏掉2024-01-15 00:00:00.000这个时刻到2024-01-15 00:00:00.003之间产生的订单。所以SQL Server里最稳妥的区间写法是用“大于等于起始日零点”和“小于结束日次日零点”组合也就是我上面示例写的方式。而在MySQL的DATETIME类型下精度到秒直接写DATE(order_time) 某天的效率虽然差但不会漏数据。至于DATETIME2类型精度到100纳秒但也建议统一用开闭区间写法省心。3.4 生日提醒与年龄段统计系统里经常要算“今天是谁的生日”或者“本月有多少会员过生日”。这种场景别直接去比较完整的出生日期因为年份肯定不等。正确思路是提取月和日来比较。MySQL下用MONTH()和DAY()函数SQL Server下用DATEPART(MONTH, ...)和DATEPART(DAY, ...)。-- MySQL查询本月过生日的会员 SELECT * FROM users WHERE MONTH(birthday) MONTH(CURDATE()) AND DAY(birthday) DAY(CURDATE());如果只查今天过生日的直接MONTH(birthday) MONTH(CURDATE()) AND DAY(birthday) DAY(CURDATE())就行。这里有个坑查“今天过生日”时如果某人是2月29日出生的平年2月没有29号业务上怎么处理要提前和产品对齐。有的产品要求按2月28日算有的要求按3月1日算别自己拍脑袋决定。年龄段统计更常见通常是按年龄段分组看用户分布。这里推荐你把年龄段映射和日期计算分开写别在一个SELECT里堆一堆CASE WHEN。先把每个人的年龄算出来再套年龄段代码可读性会好很多。MySQL下算年龄可以用TIMESTAMPDIFF(YEAR, birthday, CURDATE())它会自动处理生日没过的情况比你自己拿年份相减再判断月份靠谱。3.5 处理字符串日期与时间戳互转接口对接和数据处理时经常遇到字符串日期和时间戳互转的需求。MySQL下把字符串转日期用STR_TO_DATE(2024-01-15 10:00:00, %Y-%m-%d %H:%i:%s)把时间戳转日期用FROM_UNIXTIME(1705305600)把日期转时间戳用UNIX_TIMESTAMP(2024-01-15 10:00:00)。SQL Server里字符串转日期用CONVERT(DATETIME, 2024-01-15 10:00:00, 120)其中120代表ODBC标准格式yyyy-mm-dd hh:mi:ss。如果把时间戳转日期SQL Server更常见的做法是DATEADD(SECOND, 1705305600, 1970-01-01)。PostgreSQL里直接用::date、::timestamp做类型转换比如2024-01-15::date或者TO_TIMESTAMP(1705305600)函数。Oracle里字符串转日期必须用TO_DATE(2024-01-15, YYYY-MM-DD)直接写2024-01-15让Oracle隐式转换是个坏习惯分分钟给你抛ORA-01861错误。互转过程中最容易出问题的是时间戳的精度。有的系统时间戳是秒级有的是毫秒级。秒级时间戳是10位毫秒级是13位。如果你拿13位毫秒级时间戳直接转得到的日期会是1970年附近离实际日期差了十万八千里。我在实际对接第三方接口时栽过一次对方文档里没写清楚是毫秒转出来的日期全是1970年排查了半天才发现是精度问题。所以拿到时间戳字段先确认位数10位是秒13位是毫秒16位是微秒。4. 性能隐患日期函数写不对索引白建4.1 函数包裹字段索引立马失效前面已经提到好几次在索引列上套函数会导致索引失效。这是个老生常谈的问题但很多人栽过的坑恰恰就是“知道”和“做到”之间的距离。举例说明。你在create_time上建了索引下面这两种写法性能天差地别-- 索引失效写法对索引字段套了函数 SELECT * FROM orders WHERE DATE_FORMAT(create_time, %Y-%m-%d) 2024-01-15; -- 索引充分利用写法直接对索引字段做范围比较 SELECT * FROM orders WHERE create_time 2024-01-15 00:00:00 AND create_time 2024-01-16 00:00:00;在MySQL的InnoDB引擎下第二种写法会走create_time索引的范围扫描查询计划里能看到range类型处理几千条数据可能只需要几毫秒。第一种写法是全表扫描数据量上百万后一次查询可能要几百毫秒甚至更久。SQL Server里同理WHERE CONVERT(VARCHAR(10), create_time, 120) 2024-01-15也是无法利用索引的写法。PostgreSQL里WHERE date_trunc(day, create_time) 2024-01-15同样问题。有个判断索引是否失效的粗暴方法看看WHERE条件里的字段如果字段两侧是“函数(字段)”那索引一定用不上如果字段是完整裸露的两边是大于、小于、等于那索引大概率能用上。4.2 FORMAT这类函数为什么是性能毒药SQL Server的FORMAT函数我用过一次就再不敢在大表上用。它看起来太方便了FORMAT(GETDATE(), yyyy-MM-dd)直接输出2024-01-15FORMAT(GETDATE(), yyyy年MM月dd日)输出2024年01月15日中文格式随便写。但FORMAT背后是CLR公共语言运行时每一行都要启动一次.NET格式化逻辑。在百万行级的数据上做格式化查询时间可能是CONVERT方案的几十倍。我有一次在一个500万行的表上跑报表业务方要看格式化后的日期字段我用FORMAT写的SQL跑了三分钟换成CONVERT(VARCHAR, 日期, 120)写法后三秒内出结果。这个对比足够说明问题。MySQL的DATE_FORMAT虽然性能比SQL Server的FORMAT好但在大表上同样不建议放在WHERE条件里能用范围查询就用范围查询把格式化放到SELECT输出层去做影响面会小很多。4.3 日期范围查询的黄金法则开闭区间我在前面反复强调区间查询的写法这里再系统讲一下。判断一个日期属于“某一天”的标准写法永远推荐“大于等于起始日零点小于次日零点”的开闭区间。用“大于等于0点且小于等于23:59:59”的写法看起来更直观但有两个隐患。第一DATETIME类型的精度问题。SQL Server的DATETIME精度是3.33毫秒你写成 2024-01-15 23:59:59对于精确到2024-01-15 23:59:59.997的数据能查出来但23:59:59.998到23:59:59.999的动作会被漏掉。MySQL的DATETIME精度到秒 23:59:59没有这个问题但DATETIME2就不行了。第二语义不清晰。小于次日零点哪怕后续有人把数据库精度升级也不会影响结果。小于等于当天23:59:59则隐含了对精度的假设。习惯了“大于等于起始日零点小于次日子零点”的写法之后你写日期过滤条件基本不会再犯边界错误。4.4 隐式类型转换日期查询的隐形杀手还有一种索引失效是隐式类型转换造成的。比如你的create_time字段是VARCHAR类型存的是2024-01-15 10:23:45这种字符串你在WHERE里写create_time 2024-01-14数据库需要把日期字符串和普通字符串做比较或者反过来把普通字符串当成日期来比较。MySQL在这种情况下如果字段是字符串但比较值是日期格式会尝试把字段值转成日期再比较一转换索引又废了。反过来如果字段是DATETIME你写WHERE create_time 2024-01-15也会发生隐式转换——把字符串转成DATETIME。这个转换如果发生在字段上同样导致索引失效。所以写SQL时尽量显式写清楚类型不要依赖数据库帮你做类型转换。宁可多写一遍TO_DATE/CONVERT/STR_TO_DATE把等号左边的字段保持裸露状态。5. 我踩过的坑日期函数实战问答与避坑清单5.1 日期格式转换老是报错怎么排查最常见的报错是MySQL的“Incorrect datetime value”和SQL Server的“Conversion failed when converting date and/or time from character string”Oracle则是ORA-01843/OORA-01861。这类问题十有八九是字符串格式和日期格式不匹配。我的排查套路是三步。第一步把出问题的字符串原样打印出来肉眼检查是什么格式——是2024/01/15、20240115还是15-Jan-2024。第二步找到你正在用的数据库对应的格式符规则MySQL用%Y-%m-%dSQL Server用120样式Oracle用YYYY-MM-DD。第三步检查字符串里有没有“意外字符”。我遇到过最气人的是字符串里混了全角空格、制表符、还有不可见字符肉眼根本看不出来。遇到这种先用REPLACE函数把看不见的字符清掉比如REPLACE(date_str, CHAR(9), )去掉制表符再转日期。另外千万警惕一个经典问题月份简写在英文系统下解析有一套规则月份名是中文在Oracle里又得改NLS设置。遇到问题先问自己“这个字符串的格式数据库认识不认识”别一上来就怀疑函数写错了。5.2 时区问题怎么处理时区问题在日志分析和国际化业务里特别明显。数据库存的是UTC时间业务方要看北京时间差8个小时处理不好报表就是错的。我的经验是遵循“入库统一、展示转换”的原则。数据入库时统一存UTC时间所有的时间戳字段约定好语义查询展示时再用日期函数统一转换。MySQL下用CONVERT_TZ(时间, 00:00, 08:00)转时区PostgreSQL可以直接用AT TIME ZONE语法SQL Server里则用AT TIME ZONE需要SQL Server 2016以上格式是时间AT TIME ZONE UTC AT TIME ZONE China Standard Time。还有个小细节取当天数据的开始和结束要结合时区算。你在中国写“今天0点到明天0点”不能拿UTC的CURDATE()去算否则你查到的“今天”实际上是UTC的今天比北京时间晚8个小时。正确做法是先把当前时间转到目标时区再截断到天然后做区间比较。5.3 日期为空时的NPE式崩溃日期字段为NULL是常态尤其LEFT JOIN关联出不来数据时日期字段全是NULL。你在日期字段上做DATE_FORMAT、DATEPART、EXTRACT会得到NULL结果但如果你在NULL上做比较、聚合结果会出乎意料。最典型的是“统计某月有订单的用户”场景。用户没下单时LEFT JOIN后的下单时间为NULL你要是写WHERE MONTH(order_time) 1这个用户就被排除了但业务想要的可能是“所有用户都统计进来没下单的显示0”。所以写日期筛选前先想清楚NULL要不要过滤要的话用IS NULL / IS NOT NULL显式处理别靠日期函数间接过滤。聚合函数也会被NULL坑。比如你想看用户最后一次下单日期MAX(order_time)没问题NULL会自然忽略。但如果业务上要求“没有下单显示1970年”那就要用COALESCE(MAX(order_time), 1970-01-01)显式兜底。5.4 闰年、月末和平年的边界边界问题的典型代表是“上月同日”“下月同日”。比如今天是1月31日你写DATE_ADD(CURDATE(), INTERVAL 1 MONTH)期望得到2月28日还是3月2日不同数据库行为不完全一样。MySQL的DATE_ADD往月加的时候如果目标月份没有当前日会顺延到目标月份的最后一天。所以1月31日加一个月结果是2月28日平年或2月29日闰年。SQL Server的DATEADD(MONTH, 1, 2024-01-31)则不一样它返回的是2024-02-29同样是“尽量往月末对齐”但和MySQL的行为在细节上仍有差异。PostgreSQL里2024-01-31::date INTERVAL 1 month返回2024-02-29。Oracle的ADD_MONTHS(2024-01-31, 1)也返回2024-02-29。所以涉及月末日期计算的业务逻辑必须和产品确认清楚周期计算是基于“自然月”还是基于“固定天数”。如果是从每月1号开始算30天一个周期那就用INTERVAL DAY别用MONTH否则1月的时间周期和2月的周期会差出几天。还有闰年的坑判断2月29日时DATE_SUB、DATE_ADD都要小心。我遇到过线上系统因为没人处理闰年在2月底跑批直接报错的事故。后来我养成了一个习惯写跨月日期计算的SQL先在某个闰年日期上快速验证一遍看行为是否符合预期。5.5 SQL注入与日期参数安全问题写日期查询时最容易被忽略的是SQL注入风险。日期参数通常通过前端传进来比如用户选个日期范围如果直接拼接字符串SQL等于把数据库大门敞开。安全的做法是使用参数化查询。Java的JDBC用PreparedStatement的setDate、setTimestamp方法Python的SQLAlchemy传datetime对象MyBatis里用#{}占位符而不是${}拼接。这样既避免了SQL注入风险也避免了隐式类型转换带来的索引问题一举两得。我见过有人写“WHERE create_time CONVERT(DATETIME, 2024-01-01) AND create_time {endDate}”这种代码前端传入endDate拼进去。攻击者如果在endDate上动点手脚整条SQL就危险了。记住一条底线任何SQL参数一律参数化绑定日期参数的绑定尤其要注意类型绑定成时间戳不要绑定成格式化字符串。5.6 日期函数在慢SQL优化里的使用建议最后聊聊日期函数和慢SQL结合的场景。做慢SQL治理时我几乎每次都遇到日期条件的写法问题。排查步骤大概是先看执行计划确认WHERE条件是否走了索引。其次看条件里有没有对日期字段做函数包装。再看有没有隐式类型转换。最后看是否用了排斥性的写法比如WHERE order_time 2024-01-15这种也是直接放弃索引的写法。如果一定要在WHERE里对日期做函数运算比如业务就是“按星期过滤”或“按月份过滤”那可以退而求其次把日期列冗余出一个“日期字符串”字段单独建索引查询时直接对字符串做等值匹配。比如orders表有个create_date_str字段存的是2024-01-15字符串查询时WHERE create_date_str 2024-01-15完美的索引匹配。这种冗余字段在报表库里很常见算是一种以空间换时间的经典方案。另外日期统计类的SQL尽量用批处理方式跑。别让报表SQL在业务高峰期直接查生产库的大表日期字段。能放到离线数仓或者从库的绝对不要在主库上做。6. 一个提高效率的收尾技巧写了上面这么多最后再分享一个小技巧是我最近实践下来觉得特别有用的习惯在数据量大的场景下日期字段一律用DATETIME类型存储别用VARCHAR也别用BIGINT时间戳。DATETIME类型配合日期函数几乎不会出现类型转换问题索引也友好计算也灵活。字符串日期看着直观但排序、比较、聚合操作都比DATETIME麻烦而且隐式转换的坑十个里有八个是从字符串日期来的。如果历史表里已经用字符串存了日期也别急着重构存储先在后加一个真实的日期列用一条UPDATE语句把旧数据解析进去再在新列上建索引。之后所有查询都走新列老列保留一段时间做兼容。这样既不影响存量逻辑又能让新的统计查询跑得快。我个人在实际操作中的体会是日期函数本身不难难的是在不同场景下选择合适的函数、合适的写法、合适的时机去用。把常用的那几个函数练到条件反射把边界情况的处理逻辑刻进脑子里写日期相关的SQL就不会再让你头疼了。希望这篇文章里的踩坑经验和示例代码能帮你少走几步弯路。
SQL日期函数实战指南：格式化、日期计算与性能优化
做SQL查询久了你会发现一个规律十张业务表里至少有八张带着日期字段。订单有下单时间用户有注册时间日志有写入时间几乎所有的数据分析、报表统计、数据清洗最后都绕不开跟日期打交道。而“写SQL时最常用的日期函数”这个话题看着基础实际上坑特别多。我见过不少人因为日期格式转换踩坑、因为日期比较写错导致索引失效、因为函数选型不当让慢查询雪上加霜。这篇文章就把我在实际工作中反复用到的日期函数用法、场景和坑一次性说清楚不管你是刚入门的新手还是写了一两年SQL的工程师都能从中找到能直接拿去用的东西。1. 为什么日期函数是SQL查询的重灾区1.1 日期数据的真实样子比你想象的乱很多人觉得日期字段不就是“2024-01-15”嘛能有多复杂等你真正接手一套跑了五六年的业务库你就知道日期字段能乱成什么样。有的是字符串存成“2024/01/15”有的是时间戳存成1705305600这种有的精确到毫秒存成2024-01-15 10:23:45.123还有的干脆存成“20240115”这种八位数字。你光是把这些格式统一起来就得费不少功夫。更重要的是业务系统不同模块的日期格式还经常不一致。比如订单表用DATETIME用户表用VARCHAR日志表用BIGINT时间戳。当你做跨表关联或者汇总统计时日期函数就成了唯一能把这些格式拧到一块的工具。所以与其问“为什么日期函数重要”不如问“没有日期函数我怎么处理这些乱七八糟的日期格式”。1.2 日期函数解决了哪三类核心问题实际工作中日期函数主要解决三类问题你可以对照自己的业务看看是不是这么回事第一类格式化与解析。把“20240115”变成“2024-01-15”把字符串变成日期类型或者反过来把日期按指定格式输出到报表里。这类需求在数据导出、接口对接、报表展示时特别多。比如你导出数据给业务方对方要求日期必须是“2024年1月15日”这种中文格式你就得靠格式化函数处理。第二类日期计算与偏移。计算两个日期之间差了多少天、多少月给定一个日期推算出它7天前是哪天、下个月的第一天是哪天判断某个日期是星期几、是当年的第几周。这类函数在周期报表、账期计算、会员生命周期分析里用得非常频繁。第三类日期截断与聚合。把精确到秒的时间戳截断到天、月、季度、年再配合GROUP BY做聚合统计。这就是“按天统计订单量”“按月统计销售额”“按季度统计新增用户”的核心逻辑。没有日期截断函数你就得自己写一堆CASE WHEN去拼分组逻辑又丑又容易错。1.3 不同数据库的日期函数差异必须心中有数搞SQL的人最容易忽略的一件事日期函数在不同数据库里名字和用法差异很大。SQL Server里有DATEADDMySQL里叫DATE_ADDSQL Server用DATEDIFF算天数差MySQL对应的是DATEDIFF但算月份差又得用TIMESTAMPDIFFOracle里格式化用TO_CHARSQL Server用FORMAT或CONVERTMySQL用DATE_FORMAT。这意味着你今天在MySQL上写好的日期逻辑换到SQL Server或者PostgreSQL上很可能直接报语法错误。所以不要问我“哪个数据库的日期函数最好用”先搞清楚你现在用的是哪个数据库再去查对应的函数文档。后文我会按主流数据库分别讲方便你对号入座。2. 主流数据库日期函数速查与核心逻辑2.1 SQL ServerDATEADD、DATEDIFF、DATEPART三件套SQL Server是很多企业级应用的首选数据库它的日期函数体系比较独立重点记住三件套就够了。DATEADD用来做日期加减。比如DATEADD(DAY, 7, GETDATE())就是当前时间加7天DATEADD(MONTH, -1, GETDATE())是当前时间减一个月。注意单位参数是第一个别写反了。单位可以是YEAR、QUARTER、MONTH、WEEK、DAY、HOUR、MINUTE、SECOND基本覆盖所有时间粒度。DATEDIFF用来算两个时间点之间的差值。DATEDIFF(DAY, 开始日期, 结束日期)返回天数差DATEDIFF(MONTH, 开始日期, 结束日期)返回月份差。这里有个细节容易忽略DATEDIFF是按“跨越了多少个边界”算的不是按整整24小时算的。比如2024-01-01 23:59:59和2024-01-02 00:00:01虽然实际差了2秒但DATEDIFF(DAY, ...)结果是1天。这在某些精确计算场景是个大坑后面我会细说。DATEPART用来提取日期的一部分。DATEPART(YEAR, 日期)拿年份DATEPART(WEEK, 日期)拿到当年的第几周DATEPART(WEEKDAY, 日期)拿星期几。SQL Server里默认一周从周日开始所以DATEPART(WEEKDAY, 2024-01-15)返回2周一如果你希望一周从周一开始计算要用SET DATEFIRST调整这个细节在周报统计时非常关键。另外SQL Server还有一个容易被性能问题缠上的函数FORMAT。它写法优雅FORMAT(GETDATE(), yyyy-MM-dd)就能格式化日期中文年月日也能搞定。但FORMAT底层走的是.NET的格式化逻辑性能比CONVERT差了不止一个数量级。我在后面讲到索引和性能的部分会专门说这个坑。常用示例-- 当前日期时间 SELECT GETDATE(); -- 2024-01-15 10:23:45.123 -- 取当天零点 SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0); -- 上个月第一天 SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0); -- 当前是几号 SELECT DATEPART(DAY, GETDATE()); -- 字符串转日期 SELECT CONVERT(DATETIME, 2024-01-15 10:00:00, 120);2.2 MySQLDATE_FORMAT、DATE_ADD、DATEDIFF组合拳MySQL在互联网场景用得最多日期函数也相对友好。几个核心函数我逐个讲。**NOW()和CURDATE()**分别返回当前日期时间和当前日期。CURDATE()返回的只有日期部分是“2024-01-15”这种格式。如果只需要当前日期用CURDATE()比NOW()更语义化还能避免后续误用到时间部分。DATE_FORMAT是MySQL里最灵活的格式化函数。DATE_FORMAT(NOW(), %Y-%m-%d %H:%i:%s)能按你指定的格式输出日期。注意百分号加字母这套格式跟SQL Server的格式码完全不同。常用的格式码%Y四位年份%y两位年份%m两位月份%c数字月份不补零%d两位日%e数字日不补零%H24小时制%i分钟%s秒%W星期名%a缩写星期名%M月份名。这个函数在报表输出中出场率极高。DATE_ADD和DATE_SUB做日期加减。DATE_ADD(NOW(), INTERVAL 7 DAY)是加7天DATE_ADD(NOW(), INTERVAL 1 MONTH)是加一个月。INTERVAL后面跟的数字和单位可以组合支持MICROSECOND、SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEAR等。MySQL没有单独的DATESUB而是通过DATE_ADD传负数实现比如DATE_ADD(NOW(), INTERVAL -1 DAY)。DATEDIFF在MySQL里只用来算天数差DATEDIFF(2024-01-15, 2024-01-10)返回5。如果你想精确计算两个时间戳之间差了几个小时、几分钟要用TIMESTAMPDIFF(HOUR, 开始, 结束)。TIMESTAMPDIFF比DATEDIFF强大得多第一个参数可以是SECOND、MINUTE、HOUR、DAY、MONTH、YEAR而且是按实际长度计算的不会出现SQL Server那种“跨边界就算一天”的问题。DATE_TRUNC的MySQL替代法是DATE()函数。比如DATE(NOW())就是截断到天。要截断到月可以配合DATE_FORMAT实现比如DATE_FORMAT(NOW(), %Y-%m-01)就是当月的第一天。要截断到周用YEARWEEK()函数。LAST_DAY返回某个月的最后一天。SELECT LAST_DAY(2024-02-05)返回2024-02-29处理闰年特别省心不用自己判断2月有28天还是29天。常用示例-- 当前日期时间 SELECT NOW(); -- 2024-01-15 10:23:45 -- 今天的日期 SELECT CURDATE(); -- 2024-01-15 -- 格式化为 2024年01月15日 SELECT DATE_FORMAT(NOW(), %Y年%m月%d日); -- 最近7天的订单 SELECT * FROM orders WHERE order_time DATE_SUB(CURDATE(), INTERVAL 7 DAY); -- 两个时间戳之间差了多少小时 SELECT TIMESTAMPDIFF(HOUR, 2024-01-15 08:00:00, 2024-01-15 18:30:00); -- 结果是10 -- 判断某个日期所在月的最后一天 SELECT LAST_DAY(2024-02-05); -- 2024-02-292.3 PostgreSQL与Oracle各具特色的日期处理方案PostgreSQL的日期处理能力非常强它甚至支持直接用日期加减整数。比如CURRENT_DATE 7就是7天后的日期CURRENT_DATE - 1就是昨天。这种运算符重载的设计让PostgreSQL的日期计算写起来特别自然。它还有DATE_TRUNC函数DATE_TRUNC(month, CURRENT_DATE)返回当月第一天DATE_TRUNC(week, CURRENT_DATE)返回本周起始日在做时间序列分析时非常好用。EXTRACT可以从时间戳里提取年、月、日、时、分、秒、季度、周EXTRACT(YEAR FROM TIMESTAMP 2024-01-15 10:00:00)返回2024。AGE函数用来计算年龄AGE(2024-01-15, 1990-05-20)会返回33年8月5日这种格式做用户年龄统计比人工算省事太多。格式化用TO_CHAR解析字符串用TO_DATE。Oracle作为老牌商业数据库日期体系相对封闭但功能完备。核心是TO_DATE和TO_CHAR一个负责字符串转日期一个负责日期转字符串。Oracle的日期格式符用单字母或双字母组合比如YYYY-MM-DD HH24:MI:SS跟MySQL的%Y%m%d风格完全不同。然后是ADD_MONTHS专门用来加月份ADD_MONTHS(DATE 2024-01-31, 1)结果是2024-02-29Oracle会自动处理月末对齐这一点比很多数据库做得聪明。MONTHS_BETWEEN返回两个日期之间相差的月份数可以带小数。EXTRACT和PostgreSQL类似EXTRACT(YEAR FROM SYSDATE)取年份。还有个TRUNC函数TRUNC(SYSDATE)截断到天TRUNC(SYSDATE, MM)返回月初TRUNC(SYSDATE, WW)返回周初功能上类似PostgreSQL的DATE_TRUNC但参数风格不同。Oracle默认日期格式是DD-MON-YY比如15-JAN-24这个格式特别容易让人踩坑因为你直接用字符串跟日期比较时Oracle会按这个默认格式解析字符串导致很多看起来没问题的SQL在Oracle上报“ORA-01843: not a valid month”错误。2.4 一张表理清日期格式化的标准写法功能描述SQL ServerMySQLPostgreSQLOracle当前日期时间GETDATE()NOW()CURRENT_TIMESTAMPSYSDATE当前日期CAST(GETDATE() AS DATE)CURDATE()CURRENT_DATETRUNC(SYSDATE)日期加减天数DATEADD(DAY, 7, GETDATE())DATE_ADD(NOW(), INTERVAL 7 DAY)CURRENT_DATE 7SYSDATE 7日期加减月份DATEADD(MONTH, 2, GETDATE())DATE_ADD(NOW(), INTERVAL 2 MONTH)CURRENT_DATE INTERVAL 2 monthsADD_MONTHS(SYSDATE, 2)两个日期差天DATEDIFF(DAY, 日期1, 日期2)DATEDIFF(日期1, 日期2)日期2 - 日期1日期2 - 日期1提取年份DATEPART(YEAR, 日期)YEAR(日期)EXTRACT(YEAR FROM 日期)EXTRACT(YEAR FROM 日期)格式化CONVERT(VARCHAR, 日期, 120)DATE_FORMAT(日期, %Y-%m-%d)TO_CHAR(日期, YYYY-MM-DD)TO_CHAR(日期, YYYY-MM-DD)当月最后一天EOMONTH(日期)LAST_DAY(日期)DATE_TRUNC(month, 日期) INTERVAL 1 month - 1 dayLAST_DAY(日期)这张表你可以保存下来以后在不同数据库之间切换时对照着查比我上面啰嗦的几千字直观得多。3. 高频实操搞定5个真实业务场景3.1 按天/周/月/季度分组统计这是报表需求里的王者场景。随便一个运营提需求就是“给我看下这个月每天的订单数”。常规写法是GROUP BY日期字段但直接把DATETIME字段放GROUP BY里会精确到时分秒导致本来想按天聚合结果同一秒内十几条订单被拆成好几组。正确做法是先把日期截断到天再分组。MySQL下用DATE_FORMAT(order_time, %Y-%m-%d)SQL Server用CONVERT(VARCHAR(10), order_time, 120)PostgreSQL用DATE_TRUNC(day, order_time)或直接CAST(order_time AS DATE)Oracle用TRUNC(order_time)。如果是按周统计就要考虑“一周从哪天开始”的问题。MySQL里YEARWEEK(order_time, 1)表示周一开始的一周YEARWEEK(order_time, 0)是周日开始。SQL Server里DATEPART(WEEK, order_time)的结果受DATEFIRST影响你得确认数据库默认的每周起始日是什么。PostgreSQL的DATE_TRUNC(week, 日期)默认从周一开始。按季度统计MySQL用QUARTER()函数SQL Server用DATEPART(QUARTER, 日期)PostgreSQL和Oracle用EXTRACT(QUARTER FROM 日期)。一个典型的月报SQL长这样MySQL版SELECT DATE_FORMAT(order_time, %Y-%m) AS order_month, COUNT(*) AS order_count, SUM(order_amount) AS total_amount FROM orders WHERE order_time 2024-01-01 AND order_time 2024-04-01 GROUP BY DATE_FORMAT(order_time, %Y-%m) ORDER BY order_month;这里要注意WHERE的写法。我见过很多新手喜欢写WHERE DATE_FORMAT(order_time, %Y-%m) 2024-01看起来没毛病实际性能很差。因为你在日期字段上套了函数之后索引基本就废了后面讲性能的部分我再展开。3.2 计算同比环比运营会看去年同期数据、上个月数据这就是同比环比。核心逻辑是用日期加减函数定位到比较基准期然后JOIN或子查询对比。以“本月销售额环比上月”为例在MySQL下可以这样写SELECT DATE_FORMAT(now_month.order_time, %Y-%m) AS month, now_month.total_amount AS current_amount, last_month.total_amount AS previous_amount, ROUND((now_month.total_amount - last_month.total_amount) / last_month.total_amount * 100, 2) AS mom_ratio FROM (SELECT DATE_ADD(CURDATE(), INTERVAL 1 - DAY(CURDATE()) DAY) AS month_start, SUM(amount) AS total_amount FROM orders WHERE order_time DATE_FORMAT(CURDATE(), %Y-%m-01) AND order_time DATE_ADD(DATE_FORMAT(CURDATE(), %Y-%m-01), INTERVAL 1 MONTH) GROUP BY DATE_FORMAT(order_time, %Y-%m)) now_month LEFT JOIN (SELECT SUM(amount) AS total_amount FROM orders WHERE order_time DATE_ADD(DATE_FORMAT(CURDATE(), %Y-%m-01), INTERVAL -1 MONTH) AND order_time DATE_FORMAT(CURDATE(), %Y-%m-01) GROUP BY DATE_FORMAT(order_time, %Y-%m)) last_month ON 1 1;这个SQL有几个关键点值得你仔细看。第一用DATE_FORMAT(CURDATE(), %Y-%m-01)拿到当月第一天的日期再配合INTERVAL加减拿到上个月的第一天。这样写的好处是不管今天几号我统计的都是完整月份的数据不会把“今天以前”和“整月”搞混。第二LEFT JOIN的条件写成了ON 1 1因为两个子查询都只返回一行这种写法可以让两个聚合结果直接横向比较。第三过滤条件都写成了起始日期大于等于、结束日期小于下月月初的开闭区间不会漏掉边界数据也不会重复统计。同比运算逻辑完全一样无非是把“往前减1个月”改成“往前减12个月”。3.3 日期区间查询的正确打开方式最常见的需求是“查最近7天的订单”“查本月的注册用户”“查上一季度的退款单”。这里有个核心原则能用日期范围比较就别在日期字段上包函数。举个例子。想查昨天一整天的订单最直觉的写法是-- 错误示范MySQL SELECT * FROM orders WHERE DATE(order_time) DATE_SUB(CURDATE(), INTERVAL 1 DAY);这写法看着干净但如果你在order_time上有索引ORDER_TIME的索引大概率失效。因为MySQL要对每行的order_time先执行DATE()函数才能跟后面的日期比较。数据量一大这条SQL就是全表扫描的命。正确写法是区间比较-- 正确示范MySQL SELECT * FROM orders WHERE order_time DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND order_time CURDATE();这样写order_time上的索引直接被利用走得是索引范围扫描性能差出几十倍不止。SQL Server下也有同样的问题。很多人在SQL Server里写WHERE CONVERT(VARCHAR(10), order_time, 120) 2024-01-14一样让索引失效。正确写法是中间变量或者参数化写法DECLARE start_date DATETIME 2024-01-14 00:00:00; DECLARE end_date DATETIME 2024-01-15 00:00:00; SELECT * FROM orders WHERE order_time start_date AND order_time end_date;有人说我用的JDBC、MyBatis没法声明变量。那也可以直接在SQL里写条件意思一样。这里再提醒一个SQL Server特有细节。DATETIME类型的精度约为3.33毫秒你用order_time 2024-01-15去查“1月14日一整天”的订单看起来没问题实际上会漏掉2024-01-15 00:00:00.000这个时刻到2024-01-15 00:00:00.003之间产生的订单。所以SQL Server里最稳妥的区间写法是用“大于等于起始日零点”和“小于结束日次日零点”组合也就是我上面示例写的方式。而在MySQL的DATETIME类型下精度到秒直接写DATE(order_time) 某天的效率虽然差但不会漏数据。至于DATETIME2类型精度到100纳秒但也建议统一用开闭区间写法省心。3.4 生日提醒与年龄段统计系统里经常要算“今天是谁的生日”或者“本月有多少会员过生日”。这种场景别直接去比较完整的出生日期因为年份肯定不等。正确思路是提取月和日来比较。MySQL下用MONTH()和DAY()函数SQL Server下用DATEPART(MONTH, ...)和DATEPART(DAY, ...)。-- MySQL查询本月过生日的会员 SELECT * FROM users WHERE MONTH(birthday) MONTH(CURDATE()) AND DAY(birthday) DAY(CURDATE());如果只查今天过生日的直接MONTH(birthday) MONTH(CURDATE()) AND DAY(birthday) DAY(CURDATE())就行。这里有个坑查“今天过生日”时如果某人是2月29日出生的平年2月没有29号业务上怎么处理要提前和产品对齐。有的产品要求按2月28日算有的要求按3月1日算别自己拍脑袋决定。年龄段统计更常见通常是按年龄段分组看用户分布。这里推荐你把年龄段映射和日期计算分开写别在一个SELECT里堆一堆CASE WHEN。先把每个人的年龄算出来再套年龄段代码可读性会好很多。MySQL下算年龄可以用TIMESTAMPDIFF(YEAR, birthday, CURDATE())它会自动处理生日没过的情况比你自己拿年份相减再判断月份靠谱。3.5 处理字符串日期与时间戳互转接口对接和数据处理时经常遇到字符串日期和时间戳互转的需求。MySQL下把字符串转日期用STR_TO_DATE(2024-01-15 10:00:00, %Y-%m-%d %H:%i:%s)把时间戳转日期用FROM_UNIXTIME(1705305600)把日期转时间戳用UNIX_TIMESTAMP(2024-01-15 10:00:00)。SQL Server里字符串转日期用CONVERT(DATETIME, 2024-01-15 10:00:00, 120)其中120代表ODBC标准格式yyyy-mm-dd hh:mi:ss。如果把时间戳转日期SQL Server更常见的做法是DATEADD(SECOND, 1705305600, 1970-01-01)。PostgreSQL里直接用::date、::timestamp做类型转换比如2024-01-15::date或者TO_TIMESTAMP(1705305600)函数。Oracle里字符串转日期必须用TO_DATE(2024-01-15, YYYY-MM-DD)直接写2024-01-15让Oracle隐式转换是个坏习惯分分钟给你抛ORA-01861错误。互转过程中最容易出问题的是时间戳的精度。有的系统时间戳是秒级有的是毫秒级。秒级时间戳是10位毫秒级是13位。如果你拿13位毫秒级时间戳直接转得到的日期会是1970年附近离实际日期差了十万八千里。我在实际对接第三方接口时栽过一次对方文档里没写清楚是毫秒转出来的日期全是1970年排查了半天才发现是精度问题。所以拿到时间戳字段先确认位数10位是秒13位是毫秒16位是微秒。4. 性能隐患日期函数写不对索引白建4.1 函数包裹字段索引立马失效前面已经提到好几次在索引列上套函数会导致索引失效。这是个老生常谈的问题但很多人栽过的坑恰恰就是“知道”和“做到”之间的距离。举例说明。你在create_time上建了索引下面这两种写法性能天差地别-- 索引失效写法对索引字段套了函数 SELECT * FROM orders WHERE DATE_FORMAT(create_time, %Y-%m-%d) 2024-01-15; -- 索引充分利用写法直接对索引字段做范围比较 SELECT * FROM orders WHERE create_time 2024-01-15 00:00:00 AND create_time 2024-01-16 00:00:00;在MySQL的InnoDB引擎下第二种写法会走create_time索引的范围扫描查询计划里能看到range类型处理几千条数据可能只需要几毫秒。第一种写法是全表扫描数据量上百万后一次查询可能要几百毫秒甚至更久。SQL Server里同理WHERE CONVERT(VARCHAR(10), create_time, 120) 2024-01-15也是无法利用索引的写法。PostgreSQL里WHERE date_trunc(day, create_time) 2024-01-15同样问题。有个判断索引是否失效的粗暴方法看看WHERE条件里的字段如果字段两侧是“函数(字段)”那索引一定用不上如果字段是完整裸露的两边是大于、小于、等于那索引大概率能用上。4.2 FORMAT这类函数为什么是性能毒药SQL Server的FORMAT函数我用过一次就再不敢在大表上用。它看起来太方便了FORMAT(GETDATE(), yyyy-MM-dd)直接输出2024-01-15FORMAT(GETDATE(), yyyy年MM月dd日)输出2024年01月15日中文格式随便写。但FORMAT背后是CLR公共语言运行时每一行都要启动一次.NET格式化逻辑。在百万行级的数据上做格式化查询时间可能是CONVERT方案的几十倍。我有一次在一个500万行的表上跑报表业务方要看格式化后的日期字段我用FORMAT写的SQL跑了三分钟换成CONVERT(VARCHAR, 日期, 120)写法后三秒内出结果。这个对比足够说明问题。MySQL的DATE_FORMAT虽然性能比SQL Server的FORMAT好但在大表上同样不建议放在WHERE条件里能用范围查询就用范围查询把格式化放到SELECT输出层去做影响面会小很多。4.3 日期范围查询的黄金法则开闭区间我在前面反复强调区间查询的写法这里再系统讲一下。判断一个日期属于“某一天”的标准写法永远推荐“大于等于起始日零点小于次日零点”的开闭区间。用“大于等于0点且小于等于23:59:59”的写法看起来更直观但有两个隐患。第一DATETIME类型的精度问题。SQL Server的DATETIME精度是3.33毫秒你写成 2024-01-15 23:59:59对于精确到2024-01-15 23:59:59.997的数据能查出来但23:59:59.998到23:59:59.999的动作会被漏掉。MySQL的DATETIME精度到秒 23:59:59没有这个问题但DATETIME2就不行了。第二语义不清晰。小于次日零点哪怕后续有人把数据库精度升级也不会影响结果。小于等于当天23:59:59则隐含了对精度的假设。习惯了“大于等于起始日零点小于次日子零点”的写法之后你写日期过滤条件基本不会再犯边界错误。4.4 隐式类型转换日期查询的隐形杀手还有一种索引失效是隐式类型转换造成的。比如你的create_time字段是VARCHAR类型存的是2024-01-15 10:23:45这种字符串你在WHERE里写create_time 2024-01-14数据库需要把日期字符串和普通字符串做比较或者反过来把普通字符串当成日期来比较。MySQL在这种情况下如果字段是字符串但比较值是日期格式会尝试把字段值转成日期再比较一转换索引又废了。反过来如果字段是DATETIME你写WHERE create_time 2024-01-15也会发生隐式转换——把字符串转成DATETIME。这个转换如果发生在字段上同样导致索引失效。所以写SQL时尽量显式写清楚类型不要依赖数据库帮你做类型转换。宁可多写一遍TO_DATE/CONVERT/STR_TO_DATE把等号左边的字段保持裸露状态。5. 我踩过的坑日期函数实战问答与避坑清单5.1 日期格式转换老是报错怎么排查最常见的报错是MySQL的“Incorrect datetime value”和SQL Server的“Conversion failed when converting date and/or time from character string”Oracle则是ORA-01843/OORA-01861。这类问题十有八九是字符串格式和日期格式不匹配。我的排查套路是三步。第一步把出问题的字符串原样打印出来肉眼检查是什么格式——是2024/01/15、20240115还是15-Jan-2024。第二步找到你正在用的数据库对应的格式符规则MySQL用%Y-%m-%dSQL Server用120样式Oracle用YYYY-MM-DD。第三步检查字符串里有没有“意外字符”。我遇到过最气人的是字符串里混了全角空格、制表符、还有不可见字符肉眼根本看不出来。遇到这种先用REPLACE函数把看不见的字符清掉比如REPLACE(date_str, CHAR(9), )去掉制表符再转日期。另外千万警惕一个经典问题月份简写在英文系统下解析有一套规则月份名是中文在Oracle里又得改NLS设置。遇到问题先问自己“这个字符串的格式数据库认识不认识”别一上来就怀疑函数写错了。5.2 时区问题怎么处理时区问题在日志分析和国际化业务里特别明显。数据库存的是UTC时间业务方要看北京时间差8个小时处理不好报表就是错的。我的经验是遵循“入库统一、展示转换”的原则。数据入库时统一存UTC时间所有的时间戳字段约定好语义查询展示时再用日期函数统一转换。MySQL下用CONVERT_TZ(时间, 00:00, 08:00)转时区PostgreSQL可以直接用AT TIME ZONE语法SQL Server里则用AT TIME ZONE需要SQL Server 2016以上格式是时间AT TIME ZONE UTC AT TIME ZONE China Standard Time。还有个小细节取当天数据的开始和结束要结合时区算。你在中国写“今天0点到明天0点”不能拿UTC的CURDATE()去算否则你查到的“今天”实际上是UTC的今天比北京时间晚8个小时。正确做法是先把当前时间转到目标时区再截断到天然后做区间比较。5.3 日期为空时的NPE式崩溃日期字段为NULL是常态尤其LEFT JOIN关联出不来数据时日期字段全是NULL。你在日期字段上做DATE_FORMAT、DATEPART、EXTRACT会得到NULL结果但如果你在NULL上做比较、聚合结果会出乎意料。最典型的是“统计某月有订单的用户”场景。用户没下单时LEFT JOIN后的下单时间为NULL你要是写WHERE MONTH(order_time) 1这个用户就被排除了但业务想要的可能是“所有用户都统计进来没下单的显示0”。所以写日期筛选前先想清楚NULL要不要过滤要的话用IS NULL / IS NOT NULL显式处理别靠日期函数间接过滤。聚合函数也会被NULL坑。比如你想看用户最后一次下单日期MAX(order_time)没问题NULL会自然忽略。但如果业务上要求“没有下单显示1970年”那就要用COALESCE(MAX(order_time), 1970-01-01)显式兜底。5.4 闰年、月末和平年的边界边界问题的典型代表是“上月同日”“下月同日”。比如今天是1月31日你写DATE_ADD(CURDATE(), INTERVAL 1 MONTH)期望得到2月28日还是3月2日不同数据库行为不完全一样。MySQL的DATE_ADD往月加的时候如果目标月份没有当前日会顺延到目标月份的最后一天。所以1月31日加一个月结果是2月28日平年或2月29日闰年。SQL Server的DATEADD(MONTH, 1, 2024-01-31)则不一样它返回的是2024-02-29同样是“尽量往月末对齐”但和MySQL的行为在细节上仍有差异。PostgreSQL里2024-01-31::date INTERVAL 1 month返回2024-02-29。Oracle的ADD_MONTHS(2024-01-31, 1)也返回2024-02-29。所以涉及月末日期计算的业务逻辑必须和产品确认清楚周期计算是基于“自然月”还是基于“固定天数”。如果是从每月1号开始算30天一个周期那就用INTERVAL DAY别用MONTH否则1月的时间周期和2月的周期会差出几天。还有闰年的坑判断2月29日时DATE_SUB、DATE_ADD都要小心。我遇到过线上系统因为没人处理闰年在2月底跑批直接报错的事故。后来我养成了一个习惯写跨月日期计算的SQL先在某个闰年日期上快速验证一遍看行为是否符合预期。5.5 SQL注入与日期参数安全问题写日期查询时最容易被忽略的是SQL注入风险。日期参数通常通过前端传进来比如用户选个日期范围如果直接拼接字符串SQL等于把数据库大门敞开。安全的做法是使用参数化查询。Java的JDBC用PreparedStatement的setDate、setTimestamp方法Python的SQLAlchemy传datetime对象MyBatis里用#{}占位符而不是${}拼接。这样既避免了SQL注入风险也避免了隐式类型转换带来的索引问题一举两得。我见过有人写“WHERE create_time CONVERT(DATETIME, 2024-01-01) AND create_time {endDate}”这种代码前端传入endDate拼进去。攻击者如果在endDate上动点手脚整条SQL就危险了。记住一条底线任何SQL参数一律参数化绑定日期参数的绑定尤其要注意类型绑定成时间戳不要绑定成格式化字符串。5.6 日期函数在慢SQL优化里的使用建议最后聊聊日期函数和慢SQL结合的场景。做慢SQL治理时我几乎每次都遇到日期条件的写法问题。排查步骤大概是先看执行计划确认WHERE条件是否走了索引。其次看条件里有没有对日期字段做函数包装。再看有没有隐式类型转换。最后看是否用了排斥性的写法比如WHERE order_time 2024-01-15这种也是直接放弃索引的写法。如果一定要在WHERE里对日期做函数运算比如业务就是“按星期过滤”或“按月份过滤”那可以退而求其次把日期列冗余出一个“日期字符串”字段单独建索引查询时直接对字符串做等值匹配。比如orders表有个create_date_str字段存的是2024-01-15字符串查询时WHERE create_date_str 2024-01-15完美的索引匹配。这种冗余字段在报表库里很常见算是一种以空间换时间的经典方案。另外日期统计类的SQL尽量用批处理方式跑。别让报表SQL在业务高峰期直接查生产库的大表日期字段。能放到离线数仓或者从库的绝对不要在主库上做。6. 一个提高效率的收尾技巧写了上面这么多最后再分享一个小技巧是我最近实践下来觉得特别有用的习惯在数据量大的场景下日期字段一律用DATETIME类型存储别用VARCHAR也别用BIGINT时间戳。DATETIME类型配合日期函数几乎不会出现类型转换问题索引也友好计算也灵活。字符串日期看着直观但排序、比较、聚合操作都比DATETIME麻烦而且隐式转换的坑十个里有八个是从字符串日期来的。如果历史表里已经用字符串存了日期也别急着重构存储先在后加一个真实的日期列用一条UPDATE语句把旧数据解析进去再在新列上建索引。之后所有查询都走新列老列保留一段时间做兼容。这样既不影响存量逻辑又能让新的统计查询跑得快。我个人在实际操作中的体会是日期函数本身不难难的是在不同场景下选择合适的函数、合适的写法、合适的时机去用。把常用的那几个函数练到条件反射把边界情况的处理逻辑刻进脑子里写日期相关的SQL就不会再让你头疼了。希望这篇文章里的踩坑经验和示例代码能帮你少走几步弯路。