created: 2023-08-28T01:28:21.800Z

BigQuery で TIMESTAMP, DATETIME のパースやタイムゾーンの指定についてまとめ

毎回コンソールに行って SELECT して確認しながら実装していたのでありそうなやつをメモ。

SELECT 
  -- TIMESTAMP はだいたいの形式は通る
  TIMESTAMP("2020-01-01 00:00:00"),
  TIMESTAMP("2020-01-01 00:00:00.000000 UTC"),
  TIMESTAMP("2020-01-01T00:00:00+00:00"),
  TIMESTAMP("2020-01-01T00:00:00Z"),
  TIMESTAMP("2020-01-01T00:00:00+09:00"),  --> UTC にしてくれる '2019-12-31 15:00:00 UTC'

  -- DATETIME型もだいたい大丈夫だけど、どのタイムゾーンかの情報をつけてあげる必要がある
  DATETIME("2020-01-01 00:00:00", "Asia/Tokyo"),  --> '2020-01-01T09:00:00'
  DATETIME("2020-01-01 00:00:00Z", "Asia/Tokyo"),
  DATETIME("2020-01-01T00:00:00Z", "Asia/Tokyo"),
  DATETIME("2020-01-01T00:00:00+00:00", "Asia/Tokyo"),  --> '2020-01-01T09:00:00'
  DATETIME("2020-01-01T00:00:00+00:00", "UTC"),  --> '2020-01-01T00:00:00'

  -- この形式だとタイムゾーンを指示しなくても DATETIME にしてくれる
  DATETIME("2020-01-01 00:00:00"),

  -- DATETIME でもタイムゾーンを指定しないとダメな形式
  -- DATETIME("2020-01-01T00:00:00Z"),  `Invalid datetime string`
  -- DATETIME("2020-01-01T00:00:00+00:00"), `Invalid datetime string`

  -- RFC 2822 形式 は頑張らないとパースしてくれない `%b` とかほとんど使わない
  -- TIMESTAMP("Aug 2023 15:30:00 +0000"),
  PARSE_TIMESTAMP('%b %Y %H:%M:%S %z', 'Aug 2023 15:30:00 +0000'),

  -- unixtime は `TIMESTAMP_SECONDS` をつかう
  TIMESTAMP_SECONDS(1630156200),

BigQuery で TIMESTAMP, DATETIME のパースやタイムゾーンの指定についてまとめ

参考