BigQuery中Timestamp与Datetime差值计算疑似存在异常

你遇到的这个差异完全是BigQuery的预期设计，不是bug。我们拆解一下背后的原因：

1. TIMESTAMP_DIFF 和 DATETIME_DIFF 的核心差异 #

这两个函数的设计目标完全不同：

• TIMESTAMP_DIFF: 基于UTC绝对时间计算两个时间戳之间的完整单位间隔。比如day单位要求必须经过满24小时才算1天，会精确计算总时长后取整。
  你的例子里，2021-04-13T06:51:42 和 2021-04-05T06:56:24 的实际间隔是7天23小时55分18秒，不满8个完整的24小时周期，所以返回7。
• DATETIME_DIFF: 当参数是DATETIME类型（或无时区字符串）时，它基于日历日期的差值计算。对于day单位，它直接比较两个时间的日期部分，完全忽略时间部分的差异：
  你的例子中结束日期是2021-04-13，开始日期是2021-04-05，日期差为13-5=8，所以返回8。

2. 参数类型对DATETIME_DIFF的影响 #

你第二个查询返回7的原因，是因为传入的是TIMESTAMP类型参数：

• 当DATETIME_DIFF收到TIMESTAMP类型值时，BigQuery会自动按TIMESTAMP_DIFF的逻辑（绝对时间间隔）来计算，而不是用日历日期差，所以结果和TIMESTAMP_DIFF一致。
• 而当你传入无时区字符串（第四个查询）或显式DATETIME类型（第五个查询）时，它会切换回日历日期差的逻辑，返回8。

验证一下 #

你可以跑这个查询直观感受差异：

SELECT
  DATETIME_DIFF(DATETIME('2021-04-13T06:51:42'), DATETIME('2021-04-05T06:56:24'), DAY) AS datetime_calendar_day,
  DATETIME_DIFF(DATETIME('2021-04-13T06:51:42'), DATETIME('2021-04-05T06:56:24'), HOUR) AS datetime_exact_hour,
  TIMESTAMP_DIFF(TIMESTAMP('2021-04-13T06:51:42'), TIMESTAMP('2021-04-05T06:56:24'), HOUR) AS timestamp_exact_hour;

结果会是8, 191, 191——这说明DATETIME_DIFF只有DAY单位是按日历日期算，其他单位（比如小时）还是和TIMESTAMP_DIFF一样精确计算。

简单来说：如果要算绝对时间间隔用TIMESTAMP_DIFF，要算日历上的日期差用DATETIME_DIFF，根据业务需求选就行。

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