SQL查询DB2分存日期时间列时跨天场景无法返回正确结果

问题根源 #

• 当设置的时间区间跨天且结束时间小于开始时间时，原有逻辑中的时间条件 PREDTI >= 开始时间 AND PREDTI <= 结束时间 是矛盾约束，不存在同时满足两个条件的时间值，因此查询无返回结果。
• 原有SQL存在大量重复的过滤条件，冗余度高容易出错，也不利于后续维护。

解决方案 #

方法1：分场景判断（兼容性好，逻辑直观） #

单独处理跨天和不跨天两种场景，不需要转换字段格式，适配旧版DB2语法，同时简化冗余的业务条件：

-- 可替换为实际传入的查询参数
DECLARE @BEGIN_DATE INT = 20210821, @END_DATE INT = 20210901;
DECLARE @BEGIN_TIME INT = 140000, @END_TIME INT = 030000;

SELECT *
FROM WM370BASD.PRTRAN00 A
WHERE 
-- 公共业务条件，减少重复代码
(
  (A.PRTXTP = '300' AND A.PRTXCD = '003')
  OR
  (A.PRTXTP = '200' AND A.PRTXCD = '001' AND A.PRMNOP IN ('Dir Putaway','Locate Cases','SKU Putaway'))
  OR
  (A.PRTXTP = '200' AND A.PRTXCD = '002' AND A.PRMNOP = 'Pull Cases-Bulk')
)
AND
-- 时间区间判断逻辑
(
  -- 场景1：结束时间≥开始时间，不跨天
  (@BEGIN_TIME <= @END_TIME 
    AND A.PREDDT BETWEEN @BEGIN_DATE AND @END_DATE
    AND A.PREDTI BETWEEN @BEGIN_TIME AND @END_TIME
  )
  OR
  -- 场景2：结束时间<开始时间，跨天
  (@BEGIN_TIME > @END_TIME
    AND (
      -- 开始日期：时间≥开始时间
      (A.PREDDT = @BEGIN_DATE AND A.PREDTI >= @BEGIN_TIME)
      -- 中间日期：全部符合条件
      OR (A.PREDDT > @BEGIN_DATE AND A.PREDDT < @END_DATE)
      -- 结束日期：时间≤结束时间
      OR (A.PREDDT = @END_DATE AND A.PREDTI <= @END_TIME)
    )
  )
)

方法2：数值组合判断（性能更高） #

如果数据量较大，可以把日期和时间拼接为14位的整数时间戳直接比较，避免字符串转换开销，性能更优：

-- 替换原有时间区间判断逻辑即可
AND
(
  (@BEGIN_TIME <= @END_TIME 
    AND (A.PREDDT * 1000000 + A.PREDTI) BETWEEN (@BEGIN_DATE * 1000000 + @BEGIN_TIME) AND (@END_DATE * 1000000 + @END_TIME)
  )
  OR
  (@BEGIN_TIME > @END_TIME
    AND (
      (A.PREDDT * 1000000 + A.PREDTI) >= (@BEGIN_DATE * 1000000 + @BEGIN_TIME)
      OR (A.PREDDT * 1000000 + A.PREDTI) <= (@END_DATE * 1000000 + @END_TIME)
    )
  )
)

注意事项 #

如果你通过SSMS链接服务器查询DB2，建议将上述逻辑写入OPENQUERY语句中，让过滤逻辑在DB2服务器侧执行，避免全表数据拉取到本地再过滤，大幅提升查询性能。

内容的提问来源于stack exchange，提问作者Samuel Dague