case-kの備忘録

備忘録です。

BigQueryで標準時(UTC・EST)をJSTに変換する

時系列データでタイムスタンプがJSTでないことはまあまああるかと思います。BigQueryでJSTに変換する方法を記事にしました。
UTC - > JSTとEST -> JST変換をします。データは以下の形式ならそのままクエリ実行できると思います。
「2018-05-12 21:02:20」
GBQにはこのデータを入れてください。
gbq/sample at master · case-k-git/gbq · GitHub

SELECT
   date_time as date_time_utc,
   FORMAT_TIMESTAMP("%Y-%m-%d %H:%M:%S", TIMESTAMP(d.date_time) ,"Asia/Tokyo") AS date_time_jst
FROM
  data_set.date_sample as d
LIMIT
  10

もし、データがUTCではなくてESTの場合、まずESTをUTCに変換したのち、JSTに変換して上げる必要があります。
5時間のINTERVALを加えてESTをUTCに変換します。

SELECT
 date_time as date_time_est,
 FORMAT_TIMESTAMP("%Y-%m-%d %H:%M:%S", TIMESTAMP_ADD(TIMESTAMP(d.date_time),INTERVAL 5 HOUR),"Asia/Tokyo") AS date_time_jst,
FROM
  dataset.houston_extdata.date_sample as d
LIMIT
  10

f:id:casekblog:20190219180518p:plain:w300

以上となります。