BigQueryのDATETIME_DIFF関数の罠

BigQueryでDATETIME型の差を求めるためにはDATETIME_DIFF関数を使います。
しかし、この関数は直感に反する動きをすることがあるため注意が必要です。

以下のSQLを実行すると、結果は1になります。2つのDATETIMEの間の差は2分だけなのに、不思議ですね。

select datetime_diff(datetime '2020-12-01 01:01:00' , datetime '2020-12-01 00:59:00', hour)

一方で上記と同じく2分の差である以下のDATETIMEを渡しても、結果が0になることもあります。

select datetime_diff(datetime '2020-12-01 01:05:00' , datetime '2020-12-01 01:03:00', hour)

差を出したあとの端数の処理(四捨五入・切り上げ・切り捨て)などが関係しているような気がしますが、ちょっと謎です。

答え

この関数は2つの日付の差ではなく、「差に近しいもの」を返す関数だった。

公式ドキュメントには以下のように書かれています。

DATETIME_DIFF counts the number of part boundaries in this range of DATETIMEs.

the number of part boundariesという部分がポイントです。
これを雑に意訳するならば、「指定された期間的に切りの良い時点の数」です。
これでもまだ良くわからない人が多いと思うので、具体例を挙げて説明します。

今回の2つの例では、partは第3引数で指定されているhourです。
なので、この場合のpart boundariesは以下の時刻です。

...
2020-11-30 23:00:00
2020-12-01 00:00:00
2020-12-01 01:00:00
2020-12-01 02:00:00
2020-12-01 03:00:00
2020-12-01 04:00:00
...

1つの目の例では2つのDATETIMEの間に上記の時刻は1つ(2020-12-01 01:00:00)含まれるため、結果は1になりました。
2つめの例では2つのDATETIMEの間に上記の時刻は1つも含まれていないので結果は0です。

このようにDATETIME_DIFFは差を計算するのではなく、「差に近い何か」を計算するため注意が必要です。
この挙動を避けるためには第３引数のparをsecondなどの細かい粒度の単位にして、DATETIME_DIFFの外側に丸め処理を書くのが良いです。

select round(datetime_diff(datetime '2020-12-01 01:05:00' , datetime '2020-12-01 01:03:00', second) / 60 / 60)

ちなみに

TIMESTAMP型の差を求めるための関数である TIMESTAMP_DIFF関数にはこのような罠は存在せず、以下の2つはどちらとも0を返します。

select timestamp_diff(timestamp '2020-12-01 01:01:00' , timestamp '2020-12-01 00:59:00', hour)

select timestamp_diff(timestamp '2020-12-01 01:03:00' , timestamp '2020-12-01 01:01:00', hour)