Bigqueryの時刻のタイムゾーンに関する細かいこと

タイムゾーンを色々変えて、クエリを走らせてみた

Bigqueryで時刻データを扱うとき、色々変換をしたら、結局どこのタイムゾーンで、何の時間なのかわからなくなってしまうことがあったので、まとめてみました。

select
--BQではディフォルトでUTC時間として認識される
timestamp('2019-03-06 08:01:17') as result_1,  

--特に書くことなし、もともとタイムゾーンがUTC時間なのでそのままである
timestamp('2019-03-06 08:01:17 UTC') as result_2,  

--タイムゾーンは日本時間なので、何も書かなければUTC時間に直される
timestamp('2019-03-06T08:01:17+09:00') as result_3,  

--下記のように書くと、これは日本時間ですよという命令になるので、-９時間してUTC時間表示に修正される
timestamp('2019-03-06 08:01:17', 'Asia/Tokyo') as result_4,  

--タイムゾーンはUTC時間で、日本時間に修正してくださいの命令は動きませんでした
--timestamp('2019-03-06 08:01:17 UTC','Asia/Tokyo') as result_5,  

--タイムゾーンは日本時間で、再度日本時間に修正してくださいの命令は動きませんでした
--timestamp('2019-03-06T08:01:17+09:00','Asia/Tokyo') as result_6,  

--日本時間タイムゾーンがついている時間を'%Y-%m-%d %H:%M:%S'形式の日本時間で表示
FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S',timestamp('2019-03-06 08:01:17', 'Asia/Tokyo'),'Asia/Tokyo') as result_7,  

--UTC時間と認識されている時間を'%Y-%m-%d %H:%M:%S'形式の日本時間で表示　
FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S',timestamp('2019-03-06 08:01:17'),'Asia/Tokyo') as result_8,  

--UTC時間の時間を'%Y-%m-%d %H:%M:%S'形式の日本時間で表示　
FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S',timestamp('2019-03-06 08:01:17 UTC'),'Asia/Tokyo') as result_9,  

--UTC時間と認識されているデータを'%Y-%m-%d %H:%M:%S'形式の日本時間で表示する。しかし、タイムゾーン情報がなくなりタイムスタンプ型に直すとUTC時間と認識されてしまう。
timestamp_add(timestamp(FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S',timestamp('2019-03-06 08:01:17'),'Asia/Tokyo')), interval 1 hour) as result_10

実行結果

result_1	2019-03-06 08:01:17 UTC
result_2	2019-03-06 08:01:17 UTC
result_3	2019-03-05 23:01:17 UTC
result_4	2019-03-05 23:01:17 UTC
result_7	2019-03-06 08:01:17
result_8	2019-03-06 17:01:17
result_9	2019-03-06 17:01:17
result_10	2019-03-06 18:01:17 UTC

まとめ

1

Bigqueryではタイムゾーン情報が付いていなれば、ディフォルトでタイムゾーンはUTCと認識される。

2

Bigqueryでタイムゾーン情報が付いていない場合、クエリでタイムゾーンをつけた後編集した方が間違わずに済む
このように書く↓
timestamp('2019-03-06 08:01:17', 'Asia/Tokyo')

3

FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S',…
のように書くとタイムゾーン情報がなくなるので、
変換しているうちに時間がずれることがある。
なるべくFORMAT_TIMESTAMPは使わない方が間違いが少ない