1. 先把业务口径定死否则算出来的天数永远是错的DB2 里根据开始时间和结束时间算相隔天数听起来是一行 SQL 的事但我见过太多项目在上线半年后才发现天数差了一天。问题基本不在函数写法上而在需求方说相隔多少天的时候双方脑子里想的根本不是同一件事。有人默认 3 月 1 日到 3 月 2 日是 1 天有人默认是 2 天有人要的是自然日跨越数有人要的是精确到秒的时长再折算。所以动手写 SQL 之前先把这个口径钉死比研究DAYS()怎么用重要得多。这篇东西我按自己的实操顺序来写先把 DB2 里几种时间类型的相减语义捋清楚再把几个常用函数DAYS、TIMESTAMPDIFF、MIDNIGHT_SECONDS掰开揉碎讲一遍然后给出可以直接抄的完整 SQL 和测试数据接着处理算头算尾、工作日排除、按比例折算这些真实场景最后聊性能、索引和一堆踩过的坑。适合正在做账期、工单时长、请假天数、逾期判断、报表口径这类需求的开发同学也适合刚接手一套老 SQL 想搞明白为什么这里要这么写的人。1.1 三种时间类型三种完全不同的相减结果DB2 里跟日期时间沾边的类型主要是DATE、TIME、TIMESTAMP它们的相减行为差别很大这一点很多人第一次碰会懵。DATE - DATE得到的是天数差类型是DECIMAL(8,0)比如DATE(2024-03-01) - DATE(2024-02-01)结果是29。这个是最符合直觉的因为它就是纯粹的日历天数差。TIMESTAMP - TIMESTAMP得到的不是天数而是一个DECIMAL(14,0)值形如00000001000000。这串数字的每一位都有含义从左到右分别是年、月、日、时、分、秒也就是所谓的时间戳间隔timestamp duration。上面那个例子表示的是0 年 0 月 1 日 0 时 0 分 0 秒注意它表示的是一个月的间隔不是 29 天也不是 30 天。这就是最大的坑时间戳相减得到的是按字段借位的间隔不是绝对时长。TIME - TIME我没怎么在生产里用过结果同样是按时分秒字段借位的间隔类型实际业务里基本都是先转成 TIMESTAMP 或者取秒数来处理。理解了这个差异后面所有的选择和取舍就有了依据。想拿自然日天数走DATE那条路想拿精确时长得绕开TIMESTAMP直接相减用别的组合方式拼出秒数。1.2 需求方的相隔天数通常有三种口径我把常见的口径归成三类你可以拿这个去跟产品对一遍省得来回改。口径含义3月1日→3月2日3月1日→3月1日典型业务A纯时间差两个时点的绝对差值1 天0 天逾期天数、账龄、SLA 计时B自然日跨度算头算尾覆盖了多少个日历日2 天1 天请假天数、酒店住宿、住院天数C按比例折算不足一天按小时折算1.0 天同一天不同时刻则带小数0.xx 天计费、工时统计口径 A 是数据库默认给你算出来的那个口径 B 需要手动1口径 C 必须保留时间精度再除。这三种口径的 SQL 写法完全不同一开始不确认后面返工的量比写代码本身大。还有一个隐含问题时区。如果时间字段存的是带时区含义的值但库里只存了本地时间跨时区业务就一定会出错。我的建议是凡是涉及跨国、跨区域的系统库里统一存 UTC展示层再转。这件事在建表阶段定后期改代价极大。2. 核心函数拆解为什么 DAYS() 是最稳的那一把刀2.1 DAYS() 的返回值和它的隐含前提DAYS()是 DB2 的一个标量函数接收日期或时间戳表达式返回一个INTEGER含义是从公元 0001 年 1 月 1 日起算的第几天。DAYS(0001-01-01)返回1DAYS(2024-03-01)返回739036具体数值随日期增长示例仅供理解量级。它的价值在于把日期压成了一个单调递增的整数两个日期之间的天数就是两个整数的差不需要任何借位逻辑也不会有月末、闰年、跨年的特殊处理。这就是它比TIMESTAMPDIFF稳的根本原因。我常用的写法是SELECT DAYS(DATE(2024-03-01)) - DAYS(DATE(2024-02-01)) AS DAY_DIFF FROM SYSIBM.SYSDUMMY1; -- 结果292024 是闰年几个必须注意的点。第一如果传进去的是TIMESTAMPDAYS()会先截掉时分秒取日期部分也就是说DAYS(ts)等价于DAYS(DATE(ts))做纯日期差的时候没影响但你如果指望它带上小时那是没有的。第二如果传进去的是字符串会走隐式转换转换格式依赖当前会话的日期格式设置这是移植性隐患能显式DATE(...)或者TO_DATE(...)就别偷懒。第三DAYS()是确定性函数可以放心用在生成列和索引表达式里。2.2 DATE 直接相减语法糖但类型得留个心眼DATE和DATE直接相减在 DB2 里是合法的结果就是天数类型DECIMAL(8,0)。这个写法比DAYS()少敲几个字很多老代码里都是这么写的。SELECT DATE(2024-03-01) - DATE(2024-02-01) AS DAY_DIFF FROM SYSIBM.SYSDUMMY1; -- 结果29需要留心的是返回类型。DECIMAL(8,0)在往INTEGER变量里塞的时候某些驱动或者某些版本的客户端会做隐式转换绝大多数情况下没问题但如果你把它拼进字符串、做CASE分支、或者参与更复杂的算术偶尔会出现精度或者格式上的意外。所以我更倾向于显式CAST(... AS INTEGER)或者直接用DAYS()返回干净的INTEGER。另外一个坑是字符串字面量。2024-03-01 - 2024-02-01这种写法DB2 不一定会按日期解析可能直接按数值减得到0或者直接报错。带引号的字符串一定要显式转成 DATE 再运算这一点在 review 别人代码的时候要特别留意看起来很像但结果完全错。2.3 TIMESTAMP 相减得到的 DECIMAL(14,0) 到底是什么鬼这是我在 DB2 里见过的最容易被误用的东西。TIMESTAMP减TIMESTAMP得到的DECIMAL(14,0)结构是YYMMDDHHMMSS的字段拼在一起每个字段独立借位。举几个例子你就明白了SELECT CHAR(TIMESTAMP(2024-03-01-00.00.00.000000) - TIMESTAMP(2024-02-01-00.00.00.000000)) FROM SYSIBM.SYSDUMMY1; -- 结果00000001000000 —— 表示 1 个月不是 29 天 SELECT CHAR(TIMESTAMP(2024-03-01-00.00.00.000000) - TIMESTAMP(2024-02-29-00.00.00.000000)) FROM SYSIBM.SYSDUMMY1; -- 结果00000001000000 —— 也是 1 天不是这里是 0 年 0 月 1 日注意上面两个例子的字符结果一模一样但代表的含义不同第一个是 1 个月的间隔第二个是 1 天的间隔。因为字段位置是固定的月位在第 3-4 位日位在第 5-6 位。这就是为什么你绝对不能把这个值当数字直接加减或者比较大小00000001000000和00000000010000谁大按数值比是前者大但含义上前者是 1 个月、后者是 1 天一个月可能是 28 到 31 天大小关系不一定。我见过有人写WHERE ts2 - ts1 1000000来判断是否超过若干天的结果在某些月份边界上判定完全反了。这种 bug 极难复现因为需要刚好跨月才触发。2.4 TIMESTAMPDIFF 配 CHAR 的用法和它的精度陷阱TIMESTAMPDIFF(interval, char_string)常见写法是SELECT TIMESTAMPDIFF(16, CHAR(TIMESTAMP(2024-03-01-00.00.00.000000) - TIMESTAMP(2024-02-01-00.00.00.000000))) FROM SYSIBM.SYSDUMMY1;第一个参数是间隔单位代码1 表示微秒的分数、2 表示秒、4 表示分、8 表示小时、16 表示天、32 表示周、64 表示月、128 表示季度、256 表示年。日常用得最多的是 16天、8小时、2秒。关键问题来了当你要的天数涉及到跨月的时候TIMESTAMPDIFF会给你近似值。因为它内部是把 duration 里的年、月、日拆开算的一年按 365 天、一个月按 30 天折算。所以上面那个查询返回的是30而实际上 2 月 1 日到 3 月 1 日在 2024 年闰年是 29 天。这个误差有多大最大能到 1 天跨年的时候还会更明显。对于账期、计费这类对天数敏感的场景1 天误差意味着真金白银的差错。我的结论很明确TIMESTAMPDIFF配 16天只在同一个自然月内可信跨月请无条件改用DAYS()相减。如果你确实需要小时以下的精确时长那应该用秒代码 2配合另一个思路下面会讲。3. 完整实操从建表到五种写法跑一遍3.1 构造测试数据把边界情况一次覆盖光看文档不踏实我习惯先造一张小表把各种边界都塞进去。下面这张表的设计思路是覆盖闰日、月末、跨年、同一天不同时刻、以及跨月的场景。CREATE TABLE T_TIME_SPAN ( ID INTEGER NOT NULL, BIZ_CODE VARCHAR(20), START_TS TIMESTAMP, END_TS TIMESTAMP, START_DT DATE, END_DT DATE ); INSERT INTO T_TIME_SPAN (ID, BIZ_CODE, START_TS, END_TS, START_DT, END_DT) VALUES (1, SAME_DAY, TIMESTAMP(2024-03-01-09.00.00.000000), TIMESTAMP(2024-03-01-18.30.00.000000), DATE(2024-03-01), DATE(2024-03-01)), (2, NEXT_DAY, TIMESTAMP(2024-03-01-09.00.00.000000), TIMESTAMP(2024-03-02-09.00.00.000000), DATE(2024-03-01), DATE(2024-03-02)), (3, LEAP_FEB, TIMESTAMP(2024-02-01-00.00.00.000000), TIMESTAMP(2024-03-01-00.00.00.000000), DATE(2024-02-01), DATE(2024-03-01)), (4, NORMAL_FEB, TIMESTAMP(2023-02-01-00.00.00.000000), TIMESTAMP(2023-03-01-00.00.00.000000), DATE(2023-02-01), DATE(2023-03-01)), (5, CROSS_YEAR, TIMESTAMP(2023-12-31-23.00.00.000000), TIMESTAMP(2024-01-01-01.00.00.000000), DATE(2023-12-31), DATE(2024-01-01)), (6, REVERSED, TIMESTAMP(2024-03-10-00.00.00.000000), TIMESTAMP(2024-03-01-00.00.00.000000), DATE(2024-03-10), DATE(2024-03-01)), (7, MONTH_END, TIMESTAMP(2024-01-31-00.00.00.000000), TIMESTAMP(2024-02-29-00.00.00.000000), DATE(2024-01-31), DATE(2024-02-29));第 3 条和第 4 条是我特意放的一对同样的2 月 1 日到 3 月 1 日闰年是 29 天、平年是 28 天TIMESTAMPDIFF在这两条上都会返回 30一比就露馅。第 6 条是参数顺序反了的场景用来验证负数结果。第 7 条是月末到月末跨了闰日。3.2 五种写法横向实测对比把常用的几种算法一次性跑出来对照着看最直观。SELECT ID, BIZ_CODE, -- 写法1DAYS() 相减纯自然日差 DAYS(END_DT) - DAYS(START_DT) AS M1_DAYS, -- 写法2DATE 直接相减 CAST(END_DT - START_DT AS INTEGER) AS M2_DATE_SUB, -- 写法3TIMESTAMPDIFF 配 CHAR TIMESTAMPDIFF(16, CHAR(END_TS - START_TS)) AS M3_TSDIFF, -- 写法4时间戳相减后取整数部分错误示范仅作对比 CAST(END_TS - START_TS AS BIGINT) AS M4_WRONG, -- 写法5精确秒差再折天 DECIMAL( (DAYS(END_TS) - DAYS(START_TS)) * 86400 (MIDNIGHT_SECONDS(END_TS) - MIDNIGHT_SECONDS(START_TS)) , 15, 6) / 86400 AS M5_EXACT_DAY FROM T_TIME_SPAN ORDER BY ID;跑出来的结果大致是这样单位为天ID场景M1 DAYSM2 DATE相减M3 TSDIFFM4 直转M5 精确折天1同一天000异常值0.3958332隔一天同时刻111异常值1.0000003闰年跨二月292930异常值29.0000004平年跨二月282830异常值28.0000005跨年两小时110异常值0.0833336反向输入-9-9-9异常值-9.0000007月末到闰日292930异常值29.000000这张表基本把问题说透了。M1 和 M2 结果一致都是准确的自然日差M3 在跨月的三条记录上全部偏大M4 那一列我标了异常值因为把DECIMAL(14,0)直接转成整数得到的是那串YYMMDDHHMMSS拼起来的巨大数字完全没有业务意义M5 是精确到秒的时长折算同一天那条算出了 0.395833 天约 9.5 小时符合预期。第 5 条特别值得说跨年只差 2 小时TIMESTAMPDIFF(16, ...)返回0因为按天取整了。但这个 0 在业务上可能意味着零天而实际上是有 2 小时时长的。如果你的 SLA 判断用的是这个值就会漏判。注意M4 这种写法在某些老代码里真的存在尤其是从别的数据库迁移过来的项目。审查时看到CAST(xxx - yyy AS INT)且两边是 TIMESTAMP 的直接标红。3.3 封装成视图和自定义函数避免重复造轮子同一个口径反复写早晚会有人写错。我的习惯是封一个视图出去业务 SQL 一律从视图取。CREATE VIEW V_TIME_SPAN_CALC AS SELECT ID, BIZ_CODE, START_TS, END_TS, -- 口径A纯时间差自然日 DAYS(END_TS) - DAYS(START_TS) AS DIFF_DAYS_EXCL, -- 口径B算头算尾 DAYS(END_TS) - DAYS(START_TS) 1 AS DIFF_DAYS_INCL, -- 口径C精确到秒的时长天含小数 DECIMAL( (DAYS(END_TS) - DAYS(START_TS)) * 86400 (MIDNIGHT_SECONDS(END_TS) - MIDNIGHT_SECONDS(START_TS)) , 15, 6) / 86400 AS DIFF_DAYS_EXACT, -- 精确时长小时含小数 DECIMAL( (DAYS(END_TS) - DAYS(START_TS)) * 86400 (MIDNIGHT_SECONDS(END_TS) - MIDNIGHT_SECONDS(START_TS)) , 15, 6) / 3600 AS DIFF_HOURS_EXACT, -- 精确时长秒整数 (DAYS(END_TS) - DAYS(START_TS)) * 86400 (MIDNIGHT_SECONDS(END_TS) - MIDNIGHT_SECONDS(START_TS)) AS DIFF_SECONDS FROM T_TIME_SPAN;这里MIDNIGHT_SECONDS()是关键它返回时间戳当天从零点起的秒数范围 0 到 86399。把天数 × 86400 当天秒数两边一减就得到了绝对秒差完全绕开了时间戳相减的字段借位问题跨月跨年都精确。如果团队里 UDF 用得比较多也可以包一层CREATE FUNCTION F_DIFF_DAYS (P_START TIMESTAMP, P_END TIMESTAMP) RETURNS INTEGER LANGUAGE SQL DETERMINISTIC NO EXTERNAL ACTION RETURN DAYS(P_END) - DAYS(P_START);DETERMINISTIC这个修饰词一定要加否则优化器不敢在生成列和索引表达式里用你。NO EXTERNAL ACTION是防止它被判定为有副作用影响谓词下推。4. 业务口径落地算头算尾、工作日、按比例折算4.1 算头算尾的三种实现和它们的差异口径 B算头算尾最容易被忽略的就是那个1。3 月 1 日到 3 月 1 日按纯时间差是 0 天但按覆盖了几个日历日是 1 天。请假一天、住一晚酒店都是这个口径。-- 口径A不含起始日 SELECT DAYS(DATE(2024-03-05)) - DAYS(DATE(2024-03-01)) AS D FROM SYSIBM.SYSDUMMY1; -- 结果4 -- 口径B含起始日和结束日 SELECT DAYS(DATE(2024-03-05)) - DAYS(DATE(2024-03-01)) 1 AS D FROM SYSIBM.SYSDUMMY1; -- 结果5这里有个细节如果结束时间是当天下午 3 点算头算尾应该算到这一天吗我一般的处理是先按日期取整再算头算尾因为请假到 3 月 5 日下午 3 点在业务上就是请到 3 月 5 日这一天结束。但如果业务说到 3 月 5 日下午 3 点截止按小时计那就得走口径 C。另外一个坑是负数场景。假设用户填错结束时间早于开始时间口径 B 加上1之后会变成-8而不是-9边界不连续。所以我习惯在视图里加一个CASE做归一化CASE WHEN END_TS START_TS THEN 0 ELSE DAYS(END_TS) - DAYS(START_TS) 1 END AS DIFF_DAYS_INCL_SAFE报错还是归零取决于业务。财务类的一般归零工时类的可能需要保留负数用于冲销。4.2 排除周末的工作日天数两种做法各有取舍工作日计算是另一个高频需求比如合同签订后 5 个工作日内完成。DB2 没有现成的NETWORKDAYS得自己算。做法一公式法。核心思路是算出总天数里包含多少个完整的周每个整周有 2 天周末剩下的零头按起始星期几单独判断。WITH P AS ( SELECT DATE(2024-03-01) AS SD, DATE(2024-03-15) AS ED FROM SYSIBM.SYSDUMMY1 ), B AS ( SELECT SD, ED, DAYS(ED) - DAYS(SD) 1 AS TOTAL, DAYOFWEEK_ISO(SD) AS W -- 1周一 ... 7周日 FROM P ) SELECT SD, ED, TOTAL, TOTAL - (TOTAL / 7) * 2 - CASE WHEN MOD(TOTAL, 7) 0 THEN 0 WHEN W MOD(TOTAL, 7) - 1 7 THEN CASE WHEN W MOD(TOTAL, 7) - 1 6 THEN W MOD(TOTAL, 7) - 5 ELSE 0 END ELSE CASE WHEN W 5 THEN 1 ELSE 0 END CASE WHEN W MOD(TOTAL, 7) - 1 8 THEN 1 ELSE 0 END END AS WORK_DAYS FROM B;这个逻辑有点绕解释一下。TOTAL / 7 * 2是完整周带来的周末天数。零头部分MOD(TOTAL, 7)天从起始星期W开始连续数如果这个区间跨过了 6周六和 7周日就要额外扣。里面那两个分支分别处理没绕回和绕回的情况。说实话这段我每次写都要在纸上画一遍才敢确认而且闰年、月初月末都验证过之后才敢上生产。做法二递归 CTE 生成日期序列再筛。可读性高得多缺点是区间大时性能会掉。WITH DATE_SEQ (D) AS ( SELECT DATE(2024-03-01) FROM SYSIBM.SYSDUMMY1 UNION ALL SELECT D 1 DAYS FROM DATE_SEQ WHERE D DATE(2024-03-15) ) SELECT COUNT(*) AS WORK_DAYS FROM DATE_SEQ WHERE DAYOFWEEK_ISO(D) 5;这里DAYOFWEEK_ISO返回 1 到 71 是周一、7 是周日取 5就是周一到周五。注意别用DAYOFWEEK那个返回 1 是周日、7 是周六混用会全错。递归 CTE 有两个实际的限制要提醒。一是递归深度区间特别大比如三五年的时候可能会撞上资源上限需要配合OPTIMIZE FOR n ROWS或者干脆换日历表。二是不同平台的语法细节有差异标准写法是WITH xxx AS不带RECURSIVE关键字如果换了平台报语法错试着补上再跑。做法三预计算日历表。这是数仓里最稳的方案建一张覆盖二十年、每天一行的日历表带上是否工作日、是否节假日、所属财年等字段所有工作日计算直接 join 聚合。一次性投入后面所有报表都受益。CREATE TABLE DIM_CALENDAR ( CAL_DATE DATE NOT NULL PRIMARY KEY, DAY_OF_WEEK SMALLINT, IS_WEEKEND SMALLINT DEFAULT 0, IS_HOLIDAY SMALLINT DEFAULT 0, IS_WORKDAY SMALLINT DEFAULT 0, FISCAL_PERIOD VARCHAR(10) );节假日字段需要人工维护但这也是优点——法定节假日调休本来就不是算法能算出来的必须人工确认。我们内部的做法是每年年初导入一次之后只在临时调整时更新。4.3 不足一天按比例折算精度得自己控制好计费、工时这类场景经常要0.5 天1.25 天这种值。上面视图里的DIFF_DAYS_EXACT就是干这个的用秒差除以 86400保留 6 位小数再按业务需要四舍五入。SELECT ID, DIFF_DAYS_EXACT, -- 向上取整到 0.5 天 CEILING(DIFF_DAYS_EXACT * 2) / 2 AS ROUND_HALF_UP, -- 保留两位小数 DECIMAL(DIFF_DAYS_EXACT, 10, 2) AS ROUND_2, -- 不足一天按一天 CASE WHEN DIFF_DAYS_EXACT 0 AND DIFF_DAYS_EXACT 1 THEN 1 ELSE CEILING(DIFF_DAYS_EXACT) END AS MIN_ONE_DAY FROM V_TIME_SPAN_CALC;DECIMAL(x, 10, 2)这个写法在 DB2 里默认是四舍五入half up不是截断。这一点和某些数据库不一样如果用TRUNCATE或者FLOOR要显式写。曾经有个计费项目因为不知道这个默认行为对账的时候差了三分钱查了两天。还有一处要留心除法一定要先转 DECIMAL 再除。如果两边都是整数DB2 的整数除法会直接截断5 / 2得到2而不是2.5。我见过写成((DAYS(e)-DAYS(s))*86400 ...) / 86400然后纳闷为什么小数全丢了的问题就出在这里。5. 性能与索引别让一个函数把整条执行计划废掉5.1 WHERE 里套函数的代价比你想的大最典型的写法是这样的-- 反例 SELECT * FROM T_ORDER WHERE DAYS(CURRENT DATE) - DAYS(CREATE_DATE) 30;这条 SQL 的逻辑没问题但执行计划基本是全表扫描。原因很简单CREATE_DATE被包在函数里了优化器没法用它上面的索引做范围扫描只能一行行算出来再比。改成对列直接做谓词索引立刻就能用上-- 正例 SELECT * FROM T_ORDER WHERE CREATE_DATE CURRENT DATE - 30 DAYS;两句话在业务上完全等价都是创建超过 30 天但性能可能是几十倍的差距。表小的时候感觉不出来上了千万行就是分钟级和秒级的区别。我 review SQL 的时候有个简单粗暴的规则WHERE 子句里出现DAYS(、YEAR(、SUBSTR(、VARCHAR_FORMAT(包着索引列的一律要求改写成列侧裸奔的形式。只有极少数实在改不了的场景才允许例外而且必须有人签字确认。5.2 生成列加索引把计算结果固化下来有些场景确实改不了比如报表要按相隔天数区间分组统计。这时候有两个选择。第一个是生成列。DB2 支持GENERATED ALWAYS AS把表达式的结果物化成真实列然后就能在上面建索引。ALTER TABLE T_TIME_SPAN ADD COLUMN DIFF_DAYS INTEGER GENERATED ALWAYS AS (DAYS(END_DT) - DAYS(START_DT)); CREATE INDEX IX_T_TIME_SPAN_DIFF ON T_TIME_SPAN (DIFF_DAYS);生成列有几个使用前提表达式必须是确定性的DAYS()满足但CURRENT DATE不满足不能引用 LOB 类型表达式长度也有限制。另外要注意生成列在每次插入更新时都会重新计算会有轻微写入开销读取收益远远大于这点开销。第二个是物化视图或者汇总表。如果统计口径相对固定比如按天、按月、按业务线汇总平均时长可以走定时任务把结果算好落到汇总表查询时直接读。这种方式在数据量特别大的时候最有效代价是数据有延迟得接受 T1。5.3 大批量统计的分组与聚合思路按月统计平均相隔天数是常见需求写法上有个小技巧先按月份截取出分组键再聚合。SELECT VARCHAR_FORMAT(START_DT, YYYY-MM) AS MONTH_KEY, COUNT(*) AS CNT, AVG(DECIMAL(DAYS(END_DT) - DAYS(START_DT), 10, 2)) AS AVG_DAYS, MAX(DAYS(END_DT) - DAYS(START_DT)) AS MAX_DAYS, MIN(DAYS(END_DT) - DAYS(START_DT)) AS MIN_DAYS FROM T_TIME_SPAN WHERE START_DT DATE(2024-01-01) AND START_DT DATE(2025-01-01) GROUP BY VARCHAR_FORMAT(START_DT, YYYY-MM) ORDER BY MONTH_KEY;VARCHAR_FORMAT是 DB2 里做格式化比较稳妥的函数比SUBSTR(CHAR(...))可读性好。外面那层WHERE用了裸列做范围条件能吃到START_DT上的索引这个细节在月结报表这种大查询里差别很明显。还有一点AVG里面套DECIMAL转换别省。如果直接AVG(整数)DB2 在某些版本上返回的是整数平均值小数被截掉报表上看着就是平均 3 天平均 4 天实际可能是 3.7 天精度损失很难察觉。6. 常见问题与排查技巧实录6.1 报错和异常现象速查表下面这张表是我这些年攒下来的遇到问题先对一遍能省不少时间。现象 / 报错大概率原因处理方式SQL0180N 日期时间格式无效字符串与当前日期格式不匹配或含非法字符显式TO_DATE()或DATE()统一 ISO 格式SQL0181N 日期时间值超出范围出现 2 月 30 日、13 月这类不存在的日期源头校验或用CASE先过滤结果恒为 0两个字符串直接相减被当数值处理加DATE()显式转换跨月时结果差 30 天TIMESTAMPDIFF(16, CHAR(...))的近似折算改用DAYS()相减结果为巨大数字如 20240301TIMESTAMP相减结果直接转 BIGINT改走DAYS()或MIDNIGHT_SECONDS组合小数部分消失整数除法先截断再赋值先DECIMAL(...,15,6)再除负数结果起止参数顺序反了加CASE归一化或调整参数顺序索引不生效WHERE 子句里函数包住了索引列改写为列侧比较或用生成列递归 CTE 报错区间过大触发资源限制换日历表或分段处理结果为 NULL任一参数为 NULL运算自动传播用COALESCE兜底或在业务层保证非空6.2 隐性截断、四舍五入和负数三个最容易翻车的地方截断。除了前面说的整数除法还有一个隐蔽的把TIMESTAMP赋给DATE变量时时分秒会被静默丢弃不报错。如果这段逻辑后面又拿去做差值1 天的误差就出来了。我的习惯是核心计算全程用TIMESTAMP只在最终展示或者按天分组时才转DATE。四舍五入。DECIMAL类型转换默认是四舍五入而CAST到整数是截断。CAST(2.9 AS INTEGER)得到2DECIMAL(2.9, 10, 0)得到3。这两个在代码里长得像行为完全不同。计费类的场景我一般会明确用CEILING、FLOOR或者ROUND显式表达意图不用隐式转换。负数。起止反了的情况前面提过还有一种跨时区数据没对齐导致的负一天。这种数据在报表上表现为某几条记录的时长是负的但业务上明明是该有的。排查思路是先捞出来看这几个记录的时区字段再看它们的时区标识是否一致。这类问题在单库里很难复现因为产生负值的那个时间窗口可能只有几个小时。6.3 字符型时间字段的处理和脏数据清洗存量系统里日期存成VARCHAR(10)的情况太常见了可能是2024-03-01、20240301、甚至2024/3/1混着来。这种字段直接算报错是常态。-- 先摸清格式分布 SELECT LENGTH(TRIM(START_STR)) AS L, COUNT(*) FROM T_LEGACY GROUP BY LENGTH(TRIM(START_STR)); -- 再统一转换非法值转 NULL 不中断查询 SELECT CASE WHEN LENGTH(TRIM(START_STR)) 10 AND START_STR LIKE ____-__-__ THEN DATE(START_STR) ELSE NULL END AS START_DT FROM T_LEGACY;LIKE ____-__-__这种下划线通配是个很便宜的预校验能挡住大部分格式混杂的情况。不过它挡不住2024-02-30这种格式对但不存在的日期那种还是得靠DATE()转换时抛错来发现。批量处理时建议分两批跑第一批用宽松条件过滤出格式看起来对的各自单独转第二批处理剩下的脏数据。一次性硬转很容易让整条 SQL 挂在一个坏值上。6.4 NULL 传播和默认值策略DB2 里任何算术运算碰到 NULL 结果都是 NULLDAYS(NULL) - DAYS(x)也是 NULL。这本身合理问题在于下游拿到 NULL 之后可能直接进SUM而SUM会忽略 NULL导致总数偏小但没人发现。SELECT ID, COALESCE(DAYS(END_DT) - DAYS(START_DT), 0) AS DIFF_DAYS, DAYS(END_DT) - DAYS(START_DT) AS DIFF_DAYS_RAW FROM T_TIME_SPAN;我习惯同时留一列原始值和一列兜底值报表用兜底值核对时用原始值。这样既不会让 NULL 影响聚合又保留了这条记录其实是缺数据的信息。如果直接用兜底值去COUNT缺失情况就彻底看不见了。在写LEFT JOIN拼接多张表算时长的时候尤其要注意被驱动表的 NULL 会让整个表达式变 NULL而很多人只检查了主表非空就以为万事大吉。7. 踩过几个坑之后我固定下来的写法现在我的项目里基本是这么一套组合视图统一提供口径 A/B/C 三种字段业务 SQL 一律从视图取绝不自己手写DAYS()所有涉及日期的 WHERE 条件都写成裸列比较需要按天数范围筛选的走生成列加索引工作日和节假日一律查日历表不用公式硬算。一个特别想强调的细节上线前一定要用跨月、跨年、闰日、月末这四类数据各跑一遍。我见过不止一次日常数据都是三五天的短区间测试全过结果到了年底做年度汇总跨年的记录天数集体少 1因为用的是TIMESTAMPDIFF而没人发现。这种问题在测试环境里如果只造近三个月的数据是永远暴露不出来的。另外分享一个查历史数据的小技巧。当你怀疑某张表的天数算错但不知道错在哪批记录上可以拿两种算法跑一遍做全量比对只捞不一致的SELECT ID, DAYS(END_DT) - DAYS(START_DT) AS D1, TIMESTAMPDIFF(16, CHAR(END_TS - START_TS)) AS D2 FROM T_TIME_SPAN WHERE DAYS(END_DT) - DAYS(START_DT) TIMESTAMPDIFF(16, CHAR(END_TS - START_TS)) AND END_TS IS NOT NULL AND START_TS IS NOT NULL;跑出来的记录就是两种口径有分歧的地方通常集中在跨月的边界上。如果数量很少且都是已知的近似误差就不用管如果出现大量不一致或者差值超过 1 天那说明还有别的问题比如时区或者脏数据值得深挖一层。这个方法我用来排查过好几次线上数据偏差比逐条肉眼核对快得多。