【BigQuery】TIMESTAMP型をDATETIME型に変換する方法

timestamp型の日時データを datetime型（日本時刻）に直してから使いたいときありますよね。

本日はBigQueryを使って、timestamp型（UTC）をdatetime型（JST）に直す方法をご紹介します。

【結論】DATETIME(カラム名, ‘Asia/Tokyo’)を使う

ここにポケモン図鑑のデータがありました。

発売日は timestamp型でUTC時間となっています。

ちなみに UTC とは Coordinated Universal Time の略で協定世界時のこと。セシウム原子の振動数を元に計算しているのだとか。

このUTC時間と日本時間には約9時間の時差があるので、発売日を日本時間にすると「1996-02-26 09:00:00 JST」になるでしょう。

bigquery でdatetime型の日本時間にするには DATETIME(カラム名, ‘Asia/Tokyo’) を使いましょう。

SELECT
  `図鑑番号`,
  `ポケモン名`,
  `タイプ1`,
  `タイプ2`,
  `HP`,
  `こうげき`,
  `ぼうぎょ`,
  `とくこう`,
  `とくぼう`,
  `すばやさ`,
  `合計`,
  DATETIME(`発売日`, 'Asia/Tokyo') AS `datetime`
FROM
  `sql-book-384011.sample.20230711_pokemon_index_1-3`

UTC時間から9時間足されていますね。そしてUTC表示も消えました。
簡単にtimestamp（UTC）からdatetime（JST）に変換できました。

【余談】 Asia/Tokyo を付けないとどうなる？

変換時にタイムゾーンの指定をしましたが、もし指定しなければどうなるでしょうか？

タイムゾーンを指定しない場合もやってみましょう。

タイムゾーンを指定しなかったときは時間の変化はありませんでした。
UTC表記は消えていますが、これは日本時間ではないので注意が必要ですね。

まとめ

timestamp型（UTC）からdatetime型（JST）に変換するには DATETIME(カラム名, ‘Asia/Tokyo’) を使いました。

変換できたかどうかは以下を確認しましょう！

• 元の時間と数値が変わっていること
• UTC表記が消えていること

タイムゾーンの指定も忘れてはいけませんね。
型変換はよく利用するので覚えておくと便利ですね。