【BigQuery】TIMESTAMP()orFORMAT_TIMESTAMP()でUTCからJSTへの変換

背景

BigQueryについて

インタラクティブ クエリの実行する時、ConsoleでBigQueryウェブUIを使用し、構文エラーなどをその場ですぐ検知されるのはメリットの一つである。

発生された問題

• BigQueryでJST形式のTIMESTAMP型のカラムにデータが格納されており、それらのデータを関数計算して、別のテーブルカラムとして作成すると、デフォルトでUTC形式に変換されて新しいテーブルに挿入されてしまう場合。

• FORMAT_TIMESTAMP( )を用いて、BigQueryウェブUIに叩いたら、構文エラーはなかったが、クリエ結果は無効のvalueが返された。

以下のようにクエリしようとしてました。

実行してみると、このようになってます。

目標

元テーブルのデータをJSTに変換し、最新の日付データ(最大値)を比較してDATEとして抽出すること。

FORMAT_TIMESTAMP( )

説明

資料リスト

1. 標準SQL関数と演算子
2. FORMAT_TIMESTAMP( )

タイムスタンプを指定された format_string に従ってフォーマットします。

FORMAT_TIMESTAMP(format_string, timestamp[, time_zone])

戻りデータの型
STRING

ここまで、調べてきたら、エラーの原因は明らかになったようですね。
MAX(STRING)としてしまったのか？！まさか！！！
それでは、string_expressionからtimestamp_expressionへ変換しましょう。

TIMESTAMP( )

説明

資料リスト

1. 標準SQL関数と演算子
2. TIMESTAMP( )

TIMESTAMP(
  string_expression[, timezone] |
  date_expression[, timezone] |
  datetime_expression[, timezone]
)

なるほど、TIMESTAMP( )を使えばと良さそうですね。
早速、BigQueryウェブUIに叩いてみましょう。

エラーだった2020-01-29 00:00:00 UTC+9はうまくJSTに変換されて、DATE型の2020-01-28として抽出されました。

まとめの一言

複数の関数、演算子を組み合わせてクエリする時や型変換を頻繁に行われる時など、頭が混乱になってしまうのが自分には普通？日常的？なことで、一個一個の関数を整理するかまた誰かに確認していただくかはそのようなミスを抑えられる解決法ではないでしょうか。