时间计算大概是SQL里最绕的一部分。字符串处理起码逻辑是直白的但时间这东西背后有日历、时区、闰年、工作日还有各种数据库自己的脾气。我见过不少项目功能都做完了最后卡在时间统计口径上指标对不上、报表延迟、月底和年初的SQL跑出来结果不对排查到半夜发现是函数用错了。这篇文章就把我在实际项目里真正用得上的时间计算函数、选型思路和踩坑记录梳理一遍不堆概念只讲怎么落地。1. 先把最常见的几个函数盘清楚1.1 不同数据库的时间函数差异是头号坑先提醒一句SQL里时间函数是方言重灾区同一个需求在三个数据库里可能是三种写法。我在同一套系统里同时对接过MySQL、PostgreSQL和SQL Server的数据源印象最深的是一个取当前时间的需求MySQLNOW()返回YYYY-MM-DD HH:MM:SS还有CURDATE()只取日期CURTIME()只取时间。PostgreSQLNOW()同样能用但更推荐CURRENT_TIMESTAMP类型是timestamptz自带时区信息。SQL ServerGETDATE()只到毫秒SYSDATETIME()精度更高。这还只是最基础的一句。真正麻烦的是日期格式化、取年月日、计算差值这类操作每个数据库都有自己的函数名和返回规则。所以第一件事不是背函数而是明确你用的数据库版本再去查对应的文档。我个人的习惯是能不用方言特性就不用尽量用SQL标准里通用的写法比如EXTRACT(YEAR FROM date_col)在不少数据库里都能跑但YEAR(date_col)就基本是MySQL专属。如果你要写跨库兼容的查询这个意识从第一天就要有。1.2 取日期组成部分YEAR、MONTH、DAY还是EXTRACT取一个日期里的年、月、日、小时这些是第二高频的需求。最常见的场景是按月份统计注册人数、查看某天的小时分布。这里有两套思路专有函数MySQL的YEAR()、MONTH()、DAY()SQL Server的DATEPART()PostgreSQL的EXTRACT()。通用写法EXTRACT(YEAR FROM date_field)。实操中怎么选如果你只维护一个数据库用专有函数最顺手语义清晰还好记。如果要写兼容脚本或数据迁移逻辑那优先EXTRACT。另一个容易被忽略的点DAY()在MySQL里是返回日1号到31号但在SQL Server里DAY()也是这个意思。而如果你想要这一天是这一年的第几天那MySQL要用DAYOFYEAR()SQL Server要用DATEPART(DAYOFYEAR, date_col)。同一个中文翻译第几天函数完全不同不用的时候完全不会想到这个坑。1.3 格式化函数DATE_FORMAT与TO_CHAR的取舍格式化时间本质是把时间类型转成你想要的字符串或者从字符串解析出时间。两个最常用的MySQLDATE_FORMAT(date_col, %Y-%m-%d)格式符用%前缀。PostgreSQL / OracleTO_CHAR(date_col, YYYY-MM-DD)格式符用YYYY这种。SQL ServerFORMAT(date_col, yyyy-MM-dd)。这里最容易翻车的点是格式符写错。MySQL的%Y是四位年份%y是两位年份PostgreSQL的YYYY是四位YY也是四位新版里不推荐YY。我在培训时反复强调不要凭记忆写格式符每次写之前去查一眼文档这个操作五分钟就能避免一次半小时的排错。还有一点格式化函数不只是输出用它也是日期转字符串、字符串转日期的桥梁。比如你从Excel导入的日期是2024-01-05 14:30:00这种文本要让它参与时间和比较就得用STR_TO_DATE或TO_DATE把它转回真正的日期类型否则字符串和日期的比较规则会让你怀疑人生。2. 日期与时间计算的核心操作2.1 日期加减DATE_ADD与INTERVAL的本质日期加减很多新手喜欢用86400秒来硬算这其实是最容易引入时区bug的做法。正确姿势是用数据库提供的日期加减函数。MySQL的写法SELECT DATE_ADD(2024-01-31, INTERVAL 1 MONTH); -- 返回 2024-02-29注意自动处理了闰年和平年也可以反过来SELECT DATE_SUB(2024-03-01, INTERVAL 1 DAY); -- 返回 2024-02-29INTERVAL这个关键字的单位很丰富DAY、HOUR、MINUTE、SECOND、WEEK、MONTH、QUARTER、YEAR都可以。加一个月自动处理跨年加一周就是加七天这些动作数据库都帮你做完了不需要自己判断大小月。PostgreSQL的写法有点不一样INTERVAL本身就是一种数据类型SELECT 2024-01-31::date INTERVAL 1 month; -- 返回 2024-02-29SQL Server没有INTERVAL关键字用DATEADDSELECT DATEADD(MONTH, 1, 2024-01-31);你发现规律没有MySQL把间隔写在后面SQL Server把间隔单位写在前面、数量写中间。这种差异只能靠记没有捷径。2.2 时间差计算DATEDIFF的隐藏规则两个时间点之间差了多少天、多少小时、多少个月这大概是报表需求里最频繁的计算之一。但DATEDIFF这个函数在不同数据库里的返回值逻辑差异极大。MySQL的DATEDIFF只按天返回SELECT DATEDIFF(2024-02-01, 2024-01-31); -- 返回 1注意它是拿日期部分相减根本不看时间部分即使写成2024-02-01 23:59:59和2024-01-31 00:00:01结果依然是1。如果你想按小时差得先用TIMESTAMPDIFFSELECT TIMESTAMPDIFF(HOUR, 2024-01-31 00:00:00, 2024-02-01 12:00:00); -- 返回 36SQL Server的DATEDIFF是通用函数可以指定单位SELECT DATEDIFF(DAY, 2024-01-31, 2024-02-01); SELECT DATEDIFF(HOUR, 2024-01-31 00:00:00, 2024-02-01 12:00:00);PostgreSQL更直接两个日期相减就得到天数SELECT 2024-02-01::date - 2024-01-31::date; -- 返回 1你发现没有时间差这个概念在不同数据库里粒度不同MySQL的DATEDIFF只给天数要更细得换函数SQL Server的DATEDIFF全能但跨月份差天数时按过了几个边界计算比如DATEDIFF(MONTH, 2024-01-31, 2024-02-01)返回1因为跨了一个月的边界哪怕只差了1天。这带来的实际影响特别大统计用户活跃天数订单处理时长时用错函数会让数字偏差非常明显。2.3 月末与月初计算日期分桶的前置条件报表里有一个出现频率极高的需求求某一天所在月份的月初和月末。月初很好办MySQL可以这样SELECT DATE_FORMAT(2024-03-15, %Y-%m-01);但这样得到的是字符串不是日期类型。要拿到日期类型推荐用DATE_ADD配合逻辑SELECT DATE_ADD(2024-03-15, INTERVAL -DAY(2024-03-15) 1 DAY);意思是先减去当前是第几天再加一天就回到了当月1号。月末稍微麻烦一点因为不知道当月是28、29、30还是31天。比较稳妥的通用思路是取下个月的1号再减一天。SELECT DATE_SUB(DATE_ADD(DATE_ADD(2024-03-15, INTERVAL 1 MONTH), INTERVAL -DAY(DATE_ADD(2024-03-15, INTERVAL 1 MONTH)) 1 DAY), INTERVAL 1 DAY);看着很啰嗦但逻辑是稳的不会因为大小月出错。PostgreSQL写起来会短一些SELECT (DATE_TRUNC(MONTH, 2024-03-15::date) INTERVAL 1 month - INTERVAL 1 day)::date;DATE_TRUNC是个很值得展开的函数后面的分组部分我会再讲。它的作用是截断时间到指定精度比如截到月、截到小时是很多时间分桶操作的底层基础。3. 分组统计里的时间维度3.1 按天、周、月分组的标准姿势任何一张用户表、订单表、日志表几乎都会遇到按时间分组汇总的需求。最常见的三个分组粒度是天、周、月。按天分组最简单的写法是SELECT DATE(create_time) AS day, COUNT(*) AS cnt FROM orders GROUP BY DATE(create_time);这里把create_time中的时间部分去掉只保留日期相同日期的记录就归到一组了。按周分组很多人直接用WEEK()函数但要注意WEEK()有两个隐藏参数一个是模式影响一周从周日开始还是周一开始另一个是是否跨年连续。不同的模式会让同一批数据被分到不同的周报表口径可能因此不一致。按周分组更稳妥的做法之一是用YEARWEEK()或者DATE_TRUNC(WEEK, ...)返回的粒度要明确。MySQL里YEARWEEK(create_time, 1)表示年份周数配合周起始日为周一这样避免年报和周数对不上的情况。按月分组MySQL用DATE_FORMAT(create_time, %Y-%m)最简单PostgreSQL用TO_CHAR(create_time, YYYY-MM)SQL Server用FORMAT(create_time, yyyy-MM)。3.2 DATE_TRUNC的跨库等价物DATE_TRUNC是PostgreSQL里非常好用的函数作用是把时间截到指定精度SELECT DATE_TRUNC(MONTH, 2024-03-15 18:30:00); -- 返回 2024-03-01 00:00:00截到小时SELECT DATE_TRUNC(HOUR, 2024-03-15 18:30:00); -- 返回 2024-03-15 18:00:00这种操作对按小时统计请求量这种需求特别有用把一分钟级的日志归整到小时桶里。MySQL没有DATE_TRUNC但用DATE_FORMAT加拼接可以实现类似效果。SQL Server则用DATETRUNC新版本或DATEADD配合DATEDIFF组合写。如果你同时维护多个数据库建议在业务代码层封装一个时间维度转换函数统一输出天、周、月的起始时间不要让每张报表自己造轮子。这个建议看着简单真做了之后能省下大量对口径的时间。3.3 工作日与自然日的区分最近7天这个说法在实际业务里其实有歧义是自然日的最近7天还是工作日的最近7天很多报表默认用自然日但业务方讨论处理时效时说的其实是工作日。MySQL里判断是否为工作日比较硬核的做法是用DAYOFWEEKSELECT DAYOFWEEK(2024-06-01); -- 1是周日7是周六然后你可以把它纳入分组逻辑SELECT CASE WHEN DAYOFWEEK(date_col) IN (1, 7) THEN weekend ELSE weekday END AS day_type, COUNT(*) FROM orders GROUP BY day_type;但这没有考虑法定节假日。真正要处理节假日靠SQL内置函数是做不到的需要一张节假日维度表提前维护好再JOIN进去。这里我特别想强调不要试图在SQL里写一个函数去算是否节假日节假日规则每年都变而且各地不一样。一张维度表每年12月更新一次比任何函数都靠谱。4. 时间函数的实际应用场景4.1 留存率计算里的时间差用户留存是运营和数据分析最看重的指标之一。假设你有一张用户活跃日志表user_active_log字段是user_id和active_date要计算第N日留存率核心是靠两个时间函数日期差和按用户分组。第一步先算出每个用户的首次活跃日SELECT user_id, MIN(active_date) AS first_active_date FROM user_active_log GROUP BY user_id;然后把首次活跃日和后续活跃日做时间差SELECT u.user_id, u.first_active_date, l.active_date, DATEDIFF(l.active_date, u.first_active_date) AS day_diff FROM ( SELECT user_id, MIN(active_date) AS first_active_date FROM user_active_log GROUP BY user_id ) u JOIN user_active_log l ON u.user_id l.user_id;最后按day_diff分桶统计有多少用户在第1天、第7天、第30天还活跃。整个过程的数学逻辑很简单真正容易出错的在DATEDIFF的参数顺序上。MySQL的DATEDIFF(日期1, 日期2)返回的是日期1 - 日期2写反了就会出现负数留存率变成负留存排查起来特别迷惑。4.2 连续签到天数的SQL解法另一个常见需求是计算用户连续签到天数或者找出连续活跃N天以上的用户。这类问题的通用解法是日期 - 行号技巧。思路是这样的先把用户按活跃日期排序然后对每条记录算一个active_date - 行号的结果。如果日期是连续的这个结果会是同一个值一旦断签了这个值就变了。所以只要按用户和这个差值分组就是每一段连续活跃的区间。WITH t AS ( SELECT user_id, active_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY active_date) AS rn FROM user_active_log ) SELECT user_id, date_sub(active_date, INTERVAL rn DAY) AS grp, COUNT(*) AS continuous_days FROM t GROUP BY user_id, date_sub(active_date, INTERVAL rn DAY);注意ROW_NUMBER()是窗口函数MySQL 8.0及以上才支持老版本MySQL没有这个功能。如果你还在维护5.7的项目这个需求就得用变量模拟或者先把数据拉到应用层再算。4.3 按月汇总与同比环比同比环比是经营分析报表里老生常谈的需求。所谓同比就是和去年同一个月比环比就是和上个月比。要算这两个值第一个硬需求是把日期偏移到去年或上个月。MySQL里-- 上个月 SELECT DATE_FORMAT(DATE_SUB(2024-03-15, INTERVAL 1 MONTH), %Y-%m); -- 去年同月 SELECT DATE_FORMAT(DATE_SUB(2024-03-15, INTERVAL 1 YEAR), %Y-%m);第二个硬需求是定位上个月同一天、去年同期的日期。这里有个细节如果今天是3月31日那么上个月同一天到底应该是2月29日还是2月28日每个数据库的处理不一样MySQL的DATE_SUB会自动取月末最后一天PostgreSQL的 INTERVAL 1 month却可能报错或者返回不同结果。这种边界情况开发时根本不会想到直到某个月末跑批任务失败才会意识到这里有个大坑。对于同比环比报表我的建议是把月份维度单独拎出来处理不要在原日期上加一个月减一个月而是先算出一个month_key比如2024-03再直接用这个month_key去关联上一个周期的数据。这样虽然多了一步但逻辑清晰不会在边界月份出问题。5. 时区与夏令时问题5.1 时间戳存UTC还是本地时间很多项目踩过同一个坑数据库里存的时间明明是对的但查出来显示的时间总是差8个小时。这就是时区问题。从实践角度我强烈建议所有时间字段统一存UTC只在展示层转换为本地时间。这样做有几个好处服务器部署在哪个时区都不影响数据正确性。用户从不同时区访问看到的本地时间是应用层算出来的不会乱。跨区域数据合并时不需要先做时区对齐。MySQL里可以通过连接参数或SET time_zone设置会话时区这样NOW()返回的就是当前时区的时间。但要注意如果表里已经存了本地时间再改时区设置新旧数据混在一起会更乱。5.2 时区函数CONVERT_TZ的用法MySQL提供了一个专门的时区转换函数CONVERT_TZSELECT CONVERT_TZ(2024-03-15 12:00:00, 00:00, 08:00); -- 返回 2024-03-15 20:00:00这个函数的格式是CONVERT_TZ(时间, 源时区, 目标时区)时区既可以用08:00这样的偏移量也可以使用时区名前提是时区表已经加载。对于需要支持多时区的报表这个函数几乎是必须的。PostgreSQL更强大一些因为它的timestamptz类型本身就带时区信息。比较两个带时区的timestamp会自动处理时差在AT TIME ZONE子句里可以明确指定转换目标SELECT 2024-03-15 12:00:0000 AT TIME ZONE Asia/Shanghai;5.3 夏令时的处理思路夏令时会让时间计算变得极其讨厌因为它导致有些日子是23小时有些日子是25小时。如果用DATEDIFF按天数算基本没有影响但如果按小时算就会出偏差。处理夏令时的思路通常是不处理。另一个思路是使用UTC时间存储和计算因为UTC没有夏令时业务逻辑里的营业日自然日全部转为UTC的日期来对比。只在展示层用本地时间格式化。这个方案在实践中最省心也是我目前首选的做法。6. 常见问题与排查技巧实录6.1 为什么取出来的日期总是差一天这是我被问得最多的问题之一。排查步骤基本固定先看字段类型是DATETIME还是DATE。如果是DATETIME但你在前端只显示日期部分会不会是前端格式化把时区给改了。更常见的是插入数据时使用了NOW()但数据库连接设置了时区应用服务器的时区不一致导致存进去的UTC时间和预期差8小时。排查时第一步查数据库当前时区SELECT global.time_zone, session.time_zone;第二步查连接串里有没有useUnicodetruecharacterEncodingutf8serverTimezoneAsia/Shanghai这类参数。很多JDBC连接串里的serverTimezone才是真正决定时间怎么存取的地方。6.2 日期字符串比较的坑当你把一个日期字段和字符串比较时数据库通常会隐式转换。但转换规则不可控容易出问题SELECT * FROM orders WHERE create_date 2024-03-15;这里create_date如果是DATETIME字符串2024-03-15会被转成2024-03-15 00:00:00那么当天中间产生的记录都匹配不上。正确写法是SELECT * FROM orders WHERE create_date 2024-03-15 AND create_date 2024-03-16;或者用DATE(create_date) 2024-03-15。但注意用DATE(create_date)会导致索引失效数据量大时性能会明显退化。相比之下范围查询既正确又能走索引。6.3 函数作用于索引列会让查询变慢这一点值得单独列出来。很多人习惯在SQL里写WHERE DATE_FORMAT(create_time, %Y-%m-%d) 2024-03-15而不是WHERE create_time 2024-03-15 AND create_time 2024-03-16第一个写法虽然结果对了但数据库无法使用create_time上的索引只能全表扫描。第二种写法用范围条件索引能正常工作在千万级数据表上性能差距可能是几十倍。这不是时间函数怎么用的问题而是时间函数不能乱用在索引列上的问题。建议把这种做法当成默认规范要对日期字段做筛选时优先写成范围条件。这算是做大数据量查询时最基本也最有效的一条优化手段。6.4 月末、年初的边界条件怎么测时间函数的bug往往不会在平常日子里暴露而是在月末、年初、闰年这些边界条件下冒出来。我自己的排查经验是测试用例里至少包含以下日期2024-02-29闰年2024-12-31年末2024-01-01年初2024-03-313月31日加一个月是4月30日还是5月1日把这些边界日期跑一遍能发现很多平时注意不到的规则差异。尤其是涉及跨月和跨年加法的函数不实测几遍你永远不敢打保票。6.5 常见问题速查表场景MySQLPostgreSQLSQL Server当前时间NOW() / CURRENT_TIMESTAMPNOW() / CURRENT_TIMESTAMPGETDATE()取年份YEAR(date_col)EXTRACT(YEAR FROM date_col)DATEPART(YEAR, date_col)日期转字符串DATE_FORMAT(date_col, %Y-%m-%d)TO_CHAR(date_col, YYYY-MM-DD)FORMAT(date_col, yyyy-MM-dd)日期加一个月DATE_ADD(date_col, INTERVAL 1 MONTH)date_col INTERVAL 1 monthDATEADD(MONTH, 1, date_col)计算天数差DATEDIFF(date1, date2)date1 - date2DATEDIFF(DAY, date2, date1)截断到月DATE_FORMAT(date_col, %Y-%m-01)DATE_TRUNC(MONTH, date_col)DATETRUNC(MONTH, date_col)这个表只列了最常用的项。实际项目中建议每个组维护一份属于自己技术栈的时间函数速查卡新同事上手时能少走很多弯路。7. 时间窗口与滑动统计的实现细节7.1 用INTERVAL做滚动窗口日活、月活之外近30天活跃用户这类滚动窗口统计也很常见。一种做法是SELECT COUNT(DISTINCT user_id) FROM user_active_log WHERE active_date DATE_SUB(CURDATE(), INTERVAL 29 DAY) AND active_date DATE_SUB(CURDATE(), INTERVAL -1 DAY);注意边界写法这里用了和把今天排除在统计窗口外算的是从昨天开始往前数30天。如果业务口径里要包含今天只需要调整右侧边界。这种写法比BETWEEN更清晰也便于后续参数化。同时要注意近30天到底包不包括今天。不同产品经理对这个词的理解可能完全不同开发前先确认口径等代码写完再改就要多费一遍功夫。7.2 会话时间窗口按间隔分桶日志分析里经常需要把连续的流量切成一个个会话比如用户连续操作如果两次操作间隔超过30分钟就视为新会话。这种需求严格来说属于复杂数据处理但用窗口函数也能凑合做。核心思路对每个用户按时间排序然后判断当前时间和上一条时间差是否超过窗口阈值WITH logs AS ( SELECT user_id, op_time, LAG(op_time) OVER (PARTITION BY user_id ORDER BY op_time) AS prev_time FROM user_operation_log ) SELECT user_id, op_time, CASE WHEN prev_time IS NULL OR TIMESTAMPDIFF(MINUTE, prev_time, op_time) 30 THEN 1 ELSE 0 END AS is_new_session FROM logs;这里的LAG()就是取前一行的时间TIMESTAMPDIFF计算两次时间的分钟差。跑出来的结果再配合SUM() OVER就能给每一次操作打上会话编号。窗口函数在处理某行与相邻行之间的关系时特别有用配合时间函数能解决很多业务上的时序问题。7.3 定时任务里的时间触发条件写定时任务跑批时也大量用到时间函数比如每天凌晨把昨天产生的数据进行汇总。这里的常用写法是WHERE create_time DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND create_time CURDATE()这种写法就会精确限定在昨天零点到今天零点之间不管任务几点启动都不会跑错数据。反过来如果你用NOW()去扣24小时那在跨天执行时数据范围就不是昨天一个自然日而是过去的24小时两种口径结果会不一样。跑批任务里的时间口径问题比普通查询更容易被忽视因为错误往往在几天后才被发现那时数据已经污染了。建议所有定时任务统一用一个时间口径服务或统一SQL模板不要每个任务各写各的。8. 几个值得养成的编码习惯8.1 明确日期字段的语义类型在读任何一张业务表之前先确认它的日期字段到底是什么类型是DATE、DATETIME、TIMESTAMP还是干脆就是字符串不同类型的处理函数完全不同。DATE只有日期没有时间。DATETIME有日期和时间不含时区。TIMESTAMP有日期和时间且有时区尤其PostgreSQL的timestamptz。如果字段是字符串那还得先确定格式是否统一。很多系统早期用VARCHAR存日期到后期要按时间排序、聚合时才发现字符串排序和日期排序结果完全不一样那时候再改字段类型成本就很大了。所以我遇到新建表的需求时第一件事就是追着问日期字段的设计方案。如果源数据是文本格式也要确保在入库时就完成解析与转换而不是先用VARCHAR顶着以后再统一改。8.2 写SQL前先问三个问题现在自己写时间相关SQL时已经形成了一个固定习惯写之前先问自己三个问题这个日期字段的时区口径是什么UTC还是本地时间我要的结果粒度是自然日、工作日还是业务自定义日边界是否包含了比如近7天是否包含今天月初是否含1号当天。这三个问题里的任意一个没想清楚都有可能埋下后患。时间函数不难难的是口径统一。函数只是工具把口径定义清楚了选哪个函数其实很快就能确定。8.3 封装时间工具层如果项目里有多个模块都在用时间函数做同类型的计算别到处复制SQL片段建议封装一个时间工具层。可以是数据库视图、存储过程、报表通用逻辑模块甚至就是一组经过验证的SQL模板。比如月末最后一天这个计算在公司内部往往有几十张报表都需要。与其每张报表各自写一段复杂SQL不如统一封装成一个函数或视图这样改一处全局生效。这里既减少重复劳动也统一了口径。9. 实操中的一点体会说了这么多最后分享一个最实际的感受SQL里的时间计算函数单独看每一个都很简单难点全在组合和边界。真正让人头痛的不是DATE_ADD怎么写而是到底该不该包含今天这个月有几天用户所在的时区到底影响什么这些业务层面的问题。我的建议是平时做项目时专门维护一份自己踩过坑的文档记录每个函数在特定场景下的表现尤其是不同数据库的差异。等到下次遇到类似需求先查自己的笔记比重新试一遍快得多。如果你刚开始接触这块也别焦虑时间函数不是靠背能学会的遇到一个实际问题解决一个一个季度下来常见的坑基本都能趟一遍。遇到不可思议的结果时先去想边界条件再去查数据库版本差异大概率能找到原因。