bigqueryの時間機能

カラムの型情報の確認

• timestamp型とdatetime型は別のもの
  • timestampは範囲が1970-01-01 00:00:01(UTC) ~ 2038-01-19 03:14:07(UTC)
  • datetimeは1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
    • timezoneのリファレンス情報を取得しない
• webUIのスキーマから確認できる

datetimeの情報をDATETIME_TRUNC関数を使用して粒度を揃える

• 切り詰めることで様々な粒度にできる
  • DAY, WEEK, HOUR, MINUTEなど

SELECT
  *,
  DATETIME_TRUNC(created_at, DAY) as day, -- DAYの粒度で切り詰めて揃える
FROM 
  `table`

集計時点の日時の情報を追加する

• CURRENT_DATETIME関数を用いる
• タイムゾーンを指定するときには、引数で文字列で与える

SELECT
  id,
  name,
  email,
  CURRENT_DATETIME("Asia/Tokyo") as dt,
FROM UNNEST([
  STRUCT(1 AS id, "abc" AS name, "abc@google.com" as email),
  (2, "abcd", "abcd@microsoft.com"),
  (3, "例子", "reiko@google.com"),
  (4, "例夫", "reio@yahoo.com"),
  (5, "例ちゃん", "reichan@aol.com")
])

参考

• DATETIME_TRUNC/データポータル
• What difference between the DATE, TIME, DATETIME, and TIMESTAMP Types/stackoverflow