#タイムゾーンを色々変えて、クエリを走らせてみた
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は使わない方が間違いが少ない