BigQuery中跨零点时间范围的TIMESTAMP行匹配问题

问题原因 #

当时间范围跨越00:00时，TIME函数提取的时间值会出现起始时间（如23:00:00）大于结束时间（如01:00:00）的情况，此时BETWEEN的区间判断逻辑会失效——因为TIME('00:00:00')既不大于TIME('23:00:00')也不小于TIME('01:00:00')，导致符合条件的行被错误过滤。

解决方案 #

利用你提到的「时间范围不超过24小时」的前提，分两种场景处理：

• 若起始时间的TIME值 ≤ 结束时间的TIME值（未跨零点）：直接使用BETWEEN做区间匹配
• 若起始时间的TIME值 > 结束时间的TIME值（跨零点）：判断时间列的TIME值 ≥ 起始TIME 或者 ≤ 结束TIME

具体实现代码 #

方式一：简洁逻辑判断

假设起始时间戳为start_ts，结束时间戳为end_ts，目标时间列为ts_col：

WHERE 
  IF(
    TIME(start_ts) <= TIME(end_ts),
    TIME(ts_col) BETWEEN TIME(start_ts) AND TIME(end_ts),
    TIME(ts_col) >= TIME(start_ts) OR TIME(ts_col) <= TIME(end_ts)
  )

方式二：转换为当日秒数判断

把时间转换为从00:00开始的总秒数，用数值比较替代时间比较，逻辑更直观：

WHERE
  IF(
    TIME_DIFF(TIME(start_ts), TIME('00:00:00'), SECOND) <= TIME_DIFF(TIME(end_ts), TIME('00:00:00'), SECOND),
    TIME_DIFF(TIME(ts_col), TIME('00:00:00'), SECOND) BETWEEN TIME_DIFF(TIME(start_ts), TIME('00:00:00'), SECOND) AND TIME_DIFF(TIME(end_ts), TIME('00:00:00'), SECOND),
    TIME_DIFF(TIME(ts_col), TIME('00:00:00'), SECOND) >= TIME_DIFF(TIME(start_ts), TIME('00:00:00'), SECOND) OR TIME_DIFF(TIME(ts_col), TIME('00:00:00'), SECOND) <= TIME_DIFF(TIME(end_ts), TIME('00:00:00'), SECOND)
  )

示例验证 #

针对你提供的测试场景，用以下语句验证会返回预期的true：

SELECT 
  TIME(TIMESTAMP('2022-12-08 00:00:00 UTC')) AS test_time,
  IF(
    TIME(TIMESTAMP('2022-12-07 23:00:00 UTC')) <= TIME(TIMESTAMP('2022-12-08 01:00:00 UTC')),
    TIME(TIMESTAMP('2022-12-08 00:00:00 UTC')) BETWEEN TIME(TIMESTAMP('2022-12-07 23:00:00 UTC')) AND TIME(TIMESTAMP('2022-12-08 01:00:00 UTC')),
    TIME(TIMESTAMP('2022-12-08 00:00:00 UTC')) >= TIME(TIMESTAMP('2022-12-07 23:00:00 UTC')) OR TIME(TIMESTAMP('2022-12-08 00:00:00 UTC')) <= TIME(TIMESTAMP('2022-12-08 01:00:00 UTC'))
  ) AS is_match

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