BigQueryで日付周りの操作が意外とややこしかったりするので、普段使いそうな操作をまとめます。
日付を日本時間に変換する
そのまま利用すると日毎の集計などでズレが発生するため、タイムゾーンを渡して補正します。
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)