BigQuery中TIMESTAMP_DIFF与TIMESTAMP_SUB计算结果不一致问题咨询

两种时间判断方法的差异原因 #

1. 核心语义逻辑不同 #

你给出的两个表达式本身判断的就不是同一个条件，这是结果不一致的最主要原因：

• 第一种写法：TIMESTAMP_SUB(finished_at, INTERVAL 24 HOUR) < created_at
  等价于 finished_at < created_at + INTERVAL 24 HOUR，判定为真的条件是两个事件的时间间隔严格小于24小时，会精确计算到秒甚至毫秒，只要总时长没到86400秒就返回真。
• 第二种写法：TIMESTAMP_DIFF(finished_at, created_at, HOUR) > 24
  会先计算两个时间戳之间的完整小时数差值，分钟、秒的部分会被直接截断，判定为真的条件是间隔的完整小时数超过24，也就是总时长至少要达到25小时才会返回真。

举个实际例子验证：
假设created_at = '2024-05-01 10:00:00'，finished_at = '2024-05-02 11:20:00'，两个事件的实际间隔是25小时20分钟：

• 第一种写法计算得：TIMESTAMP_SUB(finished_at, 24 HOUR) = '2024-05-01 11:20:00'，大于created_at，所以返回false
• 第二种写法计算得：TIMESTAMP_DIFF(finished_at, created_at, HOUR) = 25，25>24，所以返回true

如果你需要判断「时间间隔是否超过24小时」，正确的对齐写法应该是：

• 对应第一种逻辑：TIMESTAMP_SUB(finished_at, INTERVAL 24 HOUR) >= created_at
• 对应第二种逻辑：TIMESTAMP_DIFF(finished_at, created_at, SECOND) >= 86400
  两种写法调整后返回结果会完全一致。

2. 夏令时/时区场景的额外差异 #

如果你的数据库使用带夏令时规则的时区，还会出现额外的不一致情况：
TIMESTAMP_SUB加减24小时是按物理时间的绝对长度计算，而TIMESTAMP_DIFF按小时计算时会适配时区的时间跳变规则。比如夏令时切换当天可能只有23小时，或者存在25小时，此时两种计算方式的差值会和常规场景不同。

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