BigQueryのUTCのテーブルにJSTで日付の範囲指定をする

課題

• BigQueryはデフォルトではUTCでデータが保存されています。
• BigQueryにあるUTCのデータをJSTで範囲指定したいという状況が本記事で解く課題です。

結論

• JSTで文字列生成をしてUTCに変換する中間テーブル（CTE: Common Table Expressions）を作っておき，使用したいテーブルで中間テーブルを参照する．
• 日付と時間を使って指定する場合は，FORMAT_TIMESTAMP関数の第一引数を%F %Tに変更して指定する．

WITH
--
-- 開始時刻のUTCとJSTのTIMESTAMPを用意する
--
start_date_jst AS (
  SELECT
    FORMAT_TIMESTAMP('%F 00:00:00', TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY), 'Asia/Tokyo') AS start_date_jst
),
start_date_utc AS (
  SELECT
    PARSE_TIMESTAMP('%F %T', start_date_jst.start_date_jst, 'Asia/Tokyo') AS start_date_utc
  FROM
    start_date_jst
),
--
-- 終了時刻のUTCとJSTのTIMESTAMPを用意する
--
end_date_jst AS (
  SELECT
    FORMAT_TIMESTAMP('%F 00:00:00', CURRENT_TIMESTAMP(), 'Asia/Tokyo') AS end_date_jst
),
end_date_utc AS (
  SELECT
    PARSE_TIMESTAMP('%F %T', end_date_jst.end_date_jst, 'Asia/Tokyo') AS end_date_utc
  FROM
    end_date_jst
)
SELECT start_date_jst, start_date_utc, end_date_jst, end_date_utc
FROM start_date_jst, start_date_utc, end_date_jst, end_date_utc;

実行結果

start_date_jst	start_date_utc	end_date_jst	end_date_utc
2024-06-23 00:00:00	2024-06-22 15:00:00 UTC	2024-06-24 00:00:00	2024-06-23 15:00:00 UTC

各関数の解説

1. FORMAT_TIMESTAMP

• 指定されたフォーマットに従ってタイムスタンプを文字列に変換します。ここでは、UTCの日付を「YYYY-MM-DD 00:00:00」形式でJSTに変換しています。
• 公式ドキュメント: https://cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions#format_timestamp

2. TIMESTAMP_SUB

• 指定されたタイムスタンプから特定の時間間隔を減算します。ここでは、現在のタイムスタンプから1日間引いています。
• 公式ドキュメント: https://cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions#timestamp_sub

3. CURRENT_TIMESTAMP

• 現在のUTCタイムスタンプを返します。
• 公式ドキュメント: https://cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions#current_timestamp

4. PARSE_TIMESTAMP

• 文字列をタイムスタンプに解析します。ここでは、JST形式の日付文字列をUTCタイムスタンプに変換しています。
• 公式ドキュメント: https://cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions#parse_timestamp

SQLの構造

1. start_date_jst
   現在の日付から1日前の午前0時（JST）のタイムスタンプを文字列として生成します。

2. start_date_utc
   start_date_jstをUTCタイムスタンプに変換します。

3. end_date_jst
   現在の日付の午前0時（JST）のタイムスタンプを文字列として生成します。

4. end_date_utc
   end_date_jstをUTCタイムスタンプに変換します。

5. SELECT句
   これらのCTEを結合し、開始日と終了日のJSTおよびUTCタイムスタンプを出力します。