【BigQuery】DATETIME-DIFF関数とは？使い方や注意点を紹介

DATETIME型の日時データの差を取るときはDATETIME_DIFF関数を使います。
DATETIME_DIFF関数はいくつか表示形式を設定でき種類によって使い方が変わります。

今回はDATETIME_DIFF関数の使い方とその注意点をご紹介します。

DATETIME_DIFF関数の基本形
DATETIME_DIFF関数を使い差分を表示する
まとめ

DATETIME_DIFF関数の基本形

DATETIME関数はこのように書きます。

DATETIME_DIFF(DATETIME型の日時データ, DATETIME型日時データ, 出力形式)

使用できる出力形式はこちらをご確認ください。

Datetime functions | BigQuery | Google Cloud

業務であれば「DAY」などがよく使われると思いました。
ここからは具体的な日時データを使って差分を求めていきます。

DATETIME_DIFF関数を使い差分を表示する

DATETIME_DIFF関数を使うときに気をつけたいポイントをまとめました。

1. 第2引数が第1引数よりも遅い時間だとマイナスが付く

SELECT
  DATETIME_DIFF('2023-04-02T09:00:00', '2023-04-03T09:00:00', DAY) AS `結果1`,
  DATETIME_DIFF('2023-04-02T09:00:00', '2023-04-01T09:00:00', DAY) AS `結果2`

結果1は数値が「マイナス(-)」で結果2は「プラス(+)」となりました。
ここでのポイントは第2引数が第1引数よりも遅い時間だとマイナス（-）が付くことです。

DATETIME_DIFF関数を使うときは基本的に第1引数のほうが遅い時間になるようにしましょう。

2. 「DAY」をカウントする基準は日付変更線を超えたかどうか

SELECT
  DATETIME_DIFF('2023-04-01T21:00:00', '2023-04-01T09:00:00', DAY) AS `結果1`,
  DATETIME_DIFF('2023-04-02T21:00:00', '2023-04-01T09:00:00', DAY) AS `結果2`

結果1は「0」ですが、結果2は「1」となりました。
日付変更線を超えて初めて数えられるんですね。

3. 「WEEK」をカウントする基準は日曜日になったかどうか

SELECT
  DATETIME_DIFF('2023-07-22T09:00:00', '2023-07-21T09:00:00', WEEK) AS `結果1_WEEK`, -- 2023年7月21日は金曜日、2023年7月22日は土曜日
  DATETIME_DIFF('2023-07-23T09:00:00', '2023-07-22T09:00:00', WEEK) AS `結果2_WEEK`, -- 2023年7月23日は日曜日
  DATETIME_DIFF('2023-07-22T09:00:00', '2023-07-21T09:00:00', DAY) AS `結果3_DAY`,
  DATETIME_DIFF('2023-07-23T09:00:00', '2023-07-22T09:00:00', DAY) AS `結果4_DAY`

結果1は金曜日と土曜日で差分を取り、結果2は土曜日と日曜日で差分を取りました。
どちらも表示形式を「WEEK」にしたにも関わらず、結果1は「0」で結果2は「1」でした。

これは「WEEK」のカウント基準が日曜日のためです。
土曜日から日曜日になったときに数えるようですね。