Runner in the High

技術のことをかくこころみ

BigQueryのDATE関数とタイムゾーン周りの挙動

よく分からんところがあるので整理した。

SELECT 
  date("2020-7-1T23:59:59+0900", "Asia/Tokyo") as tzDateWithTzString, 
  date("2020-7-1T23:59:59", "Asia/Tokyo") as dateWithTzString,
  date("2020-7-1T23:59:59+0900") as tzDate,
  date("2020-7-1T23:59:59") as date_

上記のSQLを実行すると以下の結果になる

tzDateWithTzString dateWithTzString tzDate date_
2020-07-01 2020-07-02 2020-07-01 2020-07-01

注目すべきはdateWithTzStringで、ISO8601形式における時差指定がない場合にはdate関数に指定されたタイムゾーン分の時差が時間に適用されている。

タイムスタンプの時差とタイムゾーン文字列の両方が指定されている場合の挙動

上の結果におけるtzDateはタイムスタンプ側でも+9時間の時差が指定されているが、同時に Asia/Tokyo というタイムゾーンも指定されている。

結果だけ見るとdateWithTzStringとは違いタイムゾーンの指定が無視され時差の適用が行われていないように見えるが、タイムスタンプにおける時差指定とタイムゾーンの時差が異なる場合を見てみると実際には適用が行われているらしいことが分かる。

以下はその例となるクエリ。

select 
  date("2020-7-1T23:59:59+0900", "Asia/Vladivostok") as date1,
  date("2020-7-1T23:59:59+0900", "Asia/Ulaanbaatar") as date2,
  date("2020-7-1T23:59:59+0900", "Asia/Tokyo") as date3

結果は以下になるが、タイムスタンプ側で時差指定がすべて+9時間であるにも関わらずタイムゾーンによって結果となる日付が変動していることが分かる。

date1 date2 date3
2020-07-02 2020-07-01 2020-07-01

この挙動を見るに、おそらく時差指定を含んだタイムスタンプ文字列とタイムゾーンの両方が指定されている場合の内部的な挙動はまずタイムスタンプ時間をUTCへ変換し、その上で改めてタイムゾーンの時差を適用するという動きになっているのではないかということが推測できる。

ウランバートルは時差が+8時間であるためタイムスタンプをUTC変換した上で時差適用しても 22:59:59 にしかならない。一方で Asia/Vladivostok は時差が+10時間なため、タイムスタンプがUTC変換された上で時差が適用されると翌日の 00:59:59 になる。