BigQueryの日付型の変換一覧

自分用メモ

まず

下記を忘れている場合は当ページ最下部のAppendixを先に読むこと

• timezoneを持つのはtimestampだけ
• timestamp("2023-01-01")とdatetime("2023-01-01")の違い

各種変換方法

全部で下記の20パターンある

1-1 STRING(UTCで記載) → TIMESTAMP
1-2 STRING(UTCで記載) → datetime(UTCで記載)
1-3 STRING(UTCで記載) → datetime(JSTで記載)
1-4 STRING(UTCで記載) → STRING(JSTで記載)

2-1 STRING(JSTで記載) → TIMESTAMP
2-2 STRING(JSTで記載) → datetime(UTCで記載)
2-3 STRING(JSTで記載) → datetime(JSTで記載)
2-4 STRING(JSTで記載) → STRING(UTCで記載)

3-1 TIMESTAMP → datetime(UTCで記載)
3-2 TIMESTAMP → datetime(JSTで記載)
3-3 TIMESTAMP → STRING(UTCで記載)
3-4 TIMESTAMP → STRING(JSTで記載)

4-1 datetime(UTCで記載) → TIMESTAMP
4-2 datetime(UTCで記載) → datetime(JSTで記載)
4-3 datetime(UTCで記載) → STRING(UTCで記載)
4-4 datetime(UTCで記載) → STRING(JSTで記載)

5-1 datetime(JSTで記載) → TIMESTAMP
5-2 datetime(JSTで記載) → datetime(UTCで記載)
5-3 datetime(JSTで記載) → STRING(UTCで記載)
5-4 datetime(JSTで記載) → STRING(JSTで記載)

1 STRING(UTCで記載) から変換

STRINGは"2023-10-01 00:00:00"だとする

1-1 STRING(UTCで記載) → TIMESTAMP

timestamp("2023-10-01 00:00:00")

1-2 STRING(UTCで記載) → datetime(UTCで記載)

--datetimeをUTCで扱うのはやめたほうがいいと思うが…
datetime("2023-10-01 00:00:00")

1-3 STRING(UTCで記載) → datetime(JSTで記載)

datetime("2023-10-01 00:00:00","Asia/Tokyo")

1-4 STRING(UTCで記載) → STRING(JSTで記載)

これは一度timestampかdatetimeに直す
2-4のことを考えると、timestampを選択するほうが良いと思う

FORMAT_TIMESTAMP("%Y-%m-%d %H:%M:%S",timestamp("2023-10-01 00:00:00"),"Asia/Tokyo")
or 
FORMAT_DATETIME("%Y-%m-%d %H:%M:%S",datetime("2023-10-01 00:00:00","Asia/Tokyo"))

2 STRING(JSTで記載) から変換

STRINGは "2023-10-01 00:00:00"だとする

2-1 STRING(JSTで記載) → TIMESTAMP

timestamp("2023-10-01 00:00:00","Asia/Tokyo")

2-2 STRING(JSTで記載) → datetime(UTCで記載)

考え直せ！

2-3 STRING(JSTで記載) → datetime(JSTで記載)

通常はdatetimeの第一引数にはUTCを入れ、timezoneを指定するんだが…
JST→JSTの場合はJSTをそのままdatetimeに食わせてOKということになる

datetime("2023-10-01 00:00:00")

気持ち悪ければ下記。処理的には無駄があるが、↑を見て初学者が勘違いすることは防げそう

datetime(timestamp("2023-10-01 00:00:00","Asia/Tokyo"),"Asia/Tokyo")

2-4 STRING(JSTで記載) → STRING(UTCで記載)

一度timestampに直す

FORMAT_TIMESTAMP("%Y-%m-%d %H:%M:%S",timestamp("2023-10-01 00:00:00","Asia/Tokyo"))

なお 1-4と違い、datetime経由にすると一段階余計な処理をすることになる

-- これは使うな
FORMAT_DATETIME("%Y-%m-%d %H:%M:%S",datetime(timestamp("2023-10-01 00:00:00","Asia/Tokyo"))

3 TIMESTAMPから変換

TIMESTAMPは ts とする

3-1 TIMESTAMP → datetime(UTCで記載)

おい！やめろ！考え直せ！TIMESTAMPのままでいいじゃないか！

3-2 TIMESTAMP → datetime(JSTで記載)

datetime(ts,"Asia/Tokyo")

3-3 TIMESTAMP → STRING(UTCで記載)

FORMAT_TIMESTAMP("%Y-%m-%d %H:%M:%S",ts)

3-4 TIMESTAMP → STRING(JSTで記載)

FORMAT_TIMESTAMP("%Y-%m-%d %H:%M:%S",ts,"Asia/Tokyo")

4 datetime(UTCで記載)から変換

datetime = dtとする
そもそも datetimeはUTCで記載するべきじゃないんでそもそもなぜこの変換が必要になるに至ったのか？と反省したほうが良い

4-1 datetime(UTCで記載) → TIMESTAMP

timestamp(dt)

4-2 datetime(UTCで記載) → datetime(JSTで記載)

一度timestampに変換する必要がある

datetime(timestamp(dt),"Asia/Tokyo")

4-3 datetime(UTCで記載) → STRING(UTCで記載)

FORMAT_DATETIME("%Y-%m-%d %H:%M:%S",dt)

4-4 datetime(UTCで記載) → STRING(JSTで記載)

FORMAT_TIMESTAMP("%Y-%m-%d %H:%M:%S",timestamp(dt),"Asia/Tokyo")

5 datetime(JSTで記載) からの変換

5-1 datetime(JSTで記載) → TIMESTAMP

timestamp(dt,"Asia/Tokyo")

5-2 datetime(JSTで記載) → datetime(UTCで記載)

やめろ

5-3 datetime(JSTで記載) → STRING(UTCで記載)

なんでこれが必要なのかまず考えろ

5-4 datetime(JSTで記載) → STRING(JSTで記載)

FORMAT_DATETIME("%Y-%m-%d %H:%M:%S",dt)

Appendix

timezoneを持つのはtimestampだけ

「timezoneの概念を持っているのはtimestamp型だけ」である
datetimeは日本時間になっていたりしてあたかもJSTのように錯覚するが、どちらかというとSTRINGに近いものだと思ったほうが良い

なので「datetime型はすべてJST時間にすること」というルールは必須
UTCのdatetimeとJSTのdatetimeは区別がつかない

こう考えると、datetime型というものは使うべきではない…。
のだが、なにかのJST時刻と比較をしたり計算したりするケースは多いのでやっぱり活躍してしまう。

timestamp()とdatetime()の違いに注意する

timestamp("2023-01-01 00:00:00")は 2023-01-01 00:00:00 UTC
datetime("2023-01-01 00:00:00") も 2023-01-01 00:00:00

だけど

timestamp("2023-01-01 00:00:00","Asia/Tokyo")は 2022-12-31 15:00:00 UTC
datetime("2023-01-01 00:00:00","Asia/Tokyo") は 2023-01-01 09:00:00

timestamp()は 「この文字列はJSTだ、それを踏まえてtimestampに変換せよ」
datetime()は「この文字列をUTCだとして、JSTの時刻をdatetimeとして出せ」
なのでぜんぜん違う

各種の日付関数は timestampでもdatetimeでも割と自由に使えてしまう

たとえば STRINGを返す format_datetime()は

format_datetime("%Y/%m/%d %H:%M:%S", current_datetime()) とdatetimeを与えるのが普通だと思うのだが

format_datetime("%Y/%m/%d %H:%M:%S", current_timestamp()) とtimestampを与えても普通に動いてしまう。

他の関数も、全部は試していないけど多分だいたいそう ADD、SUB、TRUNC…

なので どっちでも使えるじゃん〜 と思ってしまうのも混乱の種かも