BigQueryの2021.7.27のリリースノートで、INTERVAL型というものがPreview公開されたらしいので試してみました。
Release notes | BigQuery | Google Cloud
Intervalというと間隔という意味ですが、ここではどのように使われるのか、実際にSQLを実行しながら検証してみようと思います。
INTERVAL型を作成する
公式ドキュメントには、期間、時間を以下のようなフォーマットで表すと書いてあります。 https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#interval_type
[sign]Y-M [sign]D [sign]H:M:S[.F]
これ以上の情報が見当たらなかったので、いろいろ試してみました。そもそも、今までもINTERVALって予約後にあるじゃん…ということで以下のSQL。日付から指定した日数を加減しています。
SELECT CURRENT_DATE('Asia/Tokyo') AS today, -- 今日 DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 1 DAY) AS tomorrow, -- 明日 DATE_ADD(CURRENT_DATE('Asia/Tokyo'), INTERVAL 2 YEAR) AS two_years_ago, -- 2年前
ではここのINTERVAL以下をSELECTしてみると…
SELECT CURRENT_DATE('Asia/Tokyo') AS today, -- 今日 INTERVAL 1 DAY AS tomorrow, -- 明日 INTERVAL 2 YEAR AS two_years_ago, -- 2年前
結果
テーブルに保存して確認します。SELECTした結果がINTERVAL型として保存されました。
CAST([string] AS INTERVAL)でも変換できます。
SELECT CAST('2-4 9 1:2:999' AS INTERVAL)
結果
INTERVAL型で日付の加減算をしてみる
日付の加減算は、一番最初のSQLのように、DATE_ADD([日付], INTERVAL [期間])、DATE_SUB([日付], INTERVAL [期間])、のように書いていましたが、
INTERVAL型を用いると、 +-演算子で計算ができます。
-- 2021/07/28実行 SELECT CURRENT_DATE('Asia/Tokyo') + intervalue AS plus, CURRENT_DATE('Asia/Tokyo') - intervalue AS minus, FROM ( SELECT INTERVAL 1 DAY AS intervalue UNION ALL -- 明日 SELECT INTERVAL -1 DAY AS intervalue UNION ALL -- 昨日 SELECT INTERVAL 2 YEAR AS intervaluee UNION ALL -- 2年前 SELECT CAST('1-2 3 18:1:55' AS INTERVAL) AS intervaluee -- 1年と2ヶ月と3日と18時1分55秒後 )
結果
INTERVAL型、いつ使うの？
単純に日付に指定日数を加減したいのであれば、今まで通り、DATE_ADD([日付], INTERVAL [期間])、DATE_SUB([日付], INTERVAL [期間])、を使えば良いと思います。
では、例えば、「2年と4ヶ月と26日後の22:00、屋上でお待ちしております」というラブレターをもらったらどうしましょう？？
まさか、以下のような長いSQLを書いて計算なんかできませんよね？
-- 2021/07/28実行 SELECT PARSE_DATETIME('%Y%m%d%H%M%S', FORMAT_DATE('%Y%m%d220000', DATE_ADD(DATE_ADD(DATE_ADD(CURRENT_DATE('Asia/Tokyo'), INTERVAL 2 YEAR), INTERVAL 4 MONTH), INTERVAL 26 DAY))),
ここはINTERVAL型を使って、スマートに日付計算しましょう！
-- 2021/07/28実行 SELECT CURRENT_DATE('Asia/Tokyo') + CAST('2-4 26 22:00:00' AS INTERVAL)
ご覧のように、より短いSQLで待ち合わせの時間を計算することができました。余った時間は心の準備に当てましょう。
まとめ
今回はBigQueryのPreview公開された、INTERVAL型について検証しました。日時の間隔値をテーブルに持たせ、動的に日時の計算もスマートにできるので便利ですが、使い所を検討するのはなかなか難しいなと感じました。
BigQuery、頻繁に新しい機能が追加されているので、リリースノートで面白い機能を見つけては、また本ブログを通して紹介していきたいと思います！
