寝ても覚めてもこんぴうた

プログラム書いたり、ネットワーク設計したり、サーバ構築したり、車いじったり、ゲームしたり。そんなひとにわたしはなりたい。 投げ銭は kyash_id : chidakiyo マデ

BigQueryのStandardSQLで日付(date, datetime, timestamp)を変換する方法

BigQueryで日付周りの操作が意外とややこしかったりするので、普段使いそうな操作をまとめます。
 

日付を日本時間に変換する

BQ内の日付データは世界中から利用することを想定し UTC で登録されているため、日本時間(JST)と比較すると9時間ずれます。
そのまま利用すると日毎の集計などでズレが発生するため、タイムゾーンを渡して補正します。
 

Timestamp 型で登録されている日付を Date (JST) に変換する

DATE(TIMESTAMP "2008-12-25 15:30:00+07", "Asia/Tokyo")
DATE(TIMESTAMP createdAt, "Asia/Tokyo")

Timestamp 型で登録されている日付を Timestamp (JST) に変換する

TIMESTAMP("2008-12-25 15:30:00", "Asia/Tokyo")
TIMESTAMP(createdAt, "Asia/Tokyo")
 

日付の一部を取得する

集計する際に 年/月/日 の一部だけ利用したい場合があります。
 

年を取得する

Timestampから
FORMAT_TIMESTAMP("%Y", TIMESTAMP "2008-12-25 15:30:00", "Asia/Tokyo")
 
Dateから
FORMAT_DATE("%Y", DATE "2008-12-25")
 

月を取得する

Timestampから
FORMAT_TIMESTAMP("%m", TIMESTAMP "2008-12-25 15:30:00", "Asia/Tokyo")
 
Dateから
FORMAT_DATE("%m", DATE "2008-12-25")
 

日を取得する

Timestampから
FORMAT_TIMESTAMP("%d", TIMESTAMP "2008-12-25 15:30:00", "Asia/Tokyo")
 
Dateから
FORMAT_DATE("%d", DATE "2008-12-25")
 

2017-12 (年-月)のような形で取得する

Timestampから
FORMAT_TIMESTAMP("%Y-%d", TIMESTAMP "2008-12-25 15:30:00", "Asia/Tokyo")
 
Dateから
FORMAT_DATE("%Y-%d", DATE "2008-12-25")
 

日付の加算/減算

 

5日足す

 
Timestampから (5day * 24hour = 120hour)
TIMESTAMP_ADD(TIMESTAMP "2008-12-25 15:30:00 UTC", INTERVAL 120 HOUR)
 
使える単位
  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR
 
Dateから
DATE_ADD(DATE "2008-12-25", INTERVAL 5 DAY) 
 
使える単位
  • DAY
  • WEEK
  • MONTH
  • QUARTER
  • YEAR

5日引く

 
Timestampから (5day * 24hour = 120hour)
TIMESTAMP_SUB(TIMESTAMP "2008-12-25 15:30:00 UTC", INTERVAL 120 HOUR)  -- 5day * 24hour
 
Dateから
DATE_SUB(DATE "2008-12-25", INTERVAL 5 DAY)