はじめに
BigQueryで日付の操作をすることはしばしば起こります。
その度に調べるのも面倒ですし、まだ使ったことのない関数もあるので
この機会に日時に関する関数を全て試したので紹介します。
下記の4本立てで書いていきます。
①Date編←今ここ
②DateTime編
③Time編
④TimeStamp編
※注意
- StandardSQLでのみ動作を確認しています。
- 引数に「日付」と出てきますが、「YYYY-MM-DD」形式のことを指しています。
- 例のクエリでDATE('2017-12-22')のように出てきますが、文字列「2017-12-22」を日付にキャストしています。
CURRENT_DATE()
- 現在の日付を取得できる
- 引数にtimezoneを指定できる(指定がない場合はUTC)
SELECT CURRENT_DATE()
=> 2017-12-22
# タイムゾーンを指定
SELECT CURRENT_DATE('Asia/Tokyo')
=> 2017-12-22
EXTRACT(part FROM 日付)
- 日付に対応するpartが取得できる
- partに指定できるのは以下(ISO8601 週番号を返すものもありますが、あまり使わないかと。。。)
- DAYOFWEEK
- 1(日曜日) ~ 7（土曜日）の数値を返す
- DAY
- DAYOFYEAR
- MONTH
- QUARTER
- 1 ~ 4の数値を返す
- YEAR
- DAYOFWEEK
# 曜日を取得
SELECT EXTRACT(DAYOFWEEK FROM DATE ('2017-12-22'))
=> 6 (金曜日)
# 日付を取得
SELECT EXTRACT(DAY FROM DATE ('2017-12-22') )
=> 22
# 通日を取得
SELECT EXTRACT(DAYOFYEAR FROM DATE ('2017-12-22') )
=> 356
# 月を取得
SELECT EXTRACT(MONTH FROM DATE ('2017-12-22') )
=> 12
# クオーターを取得(１月から数えて)
SELECT EXTRACT(QUARTER FROM DATE ('2017-12-22') )
=> 4
# 年を取得
SELECT EXTRACT(YEAR FROM DATE ('2017-12-22') )
=> 2017
DATE
- 日付が取得できる
- 書き方は以下の２通り
- DATE(year,month,day)
- DATE(timestamp,[必要であればtimezoneを指定])
SELECT DATE (2017,12,22)
=> 2017-12-22
SELECT DATE (TIMESTAMP("2017-12-22 00:00:00"),'Asia/Tokyo')
=> 2017-12-22
DATE_ADD(日付, INTERVAL x part)
- 指定した日付に「x part」を加算した日付を取得できる。
- partに指定できるのは下記
- DAY
- WEEK
- MONTH
- QUARTER
- YEAR
# 指定した日付から１日後の日付を取得
SELECT DATE_ADD(DATE '2017-12-22', INTERVAL 1 DAY)
=> 2017-12-23
# 指定した日付から２週間後の日付を取得
SELECT DATE_ADD(DATE '2017-12-22', INTERVAL 2 WEEK)
=> 2018-01-05
注意）1MONTH = 30DAY ではない
# 指定した日から１ヵ月後の日付を取得
SELECT
DATE_ADD(DATE '2016-01-29', INTERVAL 1 MONTH),
DATE_ADD(DATE '2016-01-30', INTERVAL 1 MONTH),
DATE_ADD(DATE '2016-01-31', INTERVAL 1 MONTH)
=>2016-02-29
2016-02-29
2016-02-29
DATE_SUB(日付, INTERVAL x part)
- 指定した日付に「x part」を減算した日付を取得できる。
- partに指定できるものはDATE_ADDと同じ
# 指定した日付から１日前の日付を取得
SELECT DATE_SUB(DATE '2017-12-22', INTERVAL 1 DAY)
=> 2017-12-21
# 指定した日付から3年前の日付を取得
SELECT DATE_SUB(DATE '2017-12-22', INTERVAL 3 YEAR)
=> 2014-12-22
DATE_DIFF(日付1,日付2,part)
- 日付1と日付2の差をpartの単位で取得できる
- partに指定できるのは下記
- DAY
- WEEK
- MONTH
- QUARTER
- YEAR
※ 2017/12/22時点の公式ドキュメントにはWEEKが書いてませんが、指定できます
# 2017/12/22と2017/12/12が何日差か取得
SELECT DATE_DIFF(DATE "2017-12-22", DATE "2017-12-12", DAY)
=> 10
注意）第二引数日付の方が大きい場合は負の値になる
SELECT DATE_DIFF(DATE "2017-12-12", DATE "2017-12-22", DAY)
=> -10
# 2017/12/22と2017/12/12が何週差か取得
SELECT DATE_DIFF(DATE "2017-12-22", DATE "2017-12-12", WEEK)
=> 10
DATE_TRUNC(日付,part)
- 指定した日付をpartの粒度に切り詰める
- partで指定できるのは下記
- DAY
- WEEK
- MONTH
- QUARTER
- YEAR
# 2017/12/22を月まで切り詰める
SELECT DATE_TRUNC(DATE "2017-12-22", MONTH)
=> 2017-12-01
# 2017/12/22を週まで切り詰める
SELECT DATE_TRUNC(DATE "2017-12-22", WEEK)
=> 2017-12-17
# 2017/12/22を年まで切り詰める
SELECT DATE_TRUNC(DATE "2017-12-22", YEAR)
=> 2017-01-01
DATE_FROM_UNIX_DATE(unixdate)
- UNIXDATEから日付を取得できる
SELECT DATE_FROM_UNIX_DATE(17522)
=> 2017-12-22
FORMAT_DATE(format, 日付)
- 指定したフォーマットに、日付の形式を変更する
- 表示形式はここに載っていますが、個人的によく使うものをいくつか紹介します
- %a:省略した曜日名
- %b:省略した月名
- %F:YYYY-MM-DD
- %Y:西暦４桁
- %m:月
- %d:日
SELECT
FORMAT_DATE("%A", DATE "2017-12-22"),
FORMAT_DATE("%a", DATE "2017-12-22"),
FORMAT_DATE("%B", DATE "2017-12-22"),
FORMAT_DATE("%b", DATE "2017-12-22"),
FORMAT_DATE("%F", DATE "2017-12-22"),
FORMAT_DATE("%Y", DATE "2017-12-22"),
FORMAT_DATE("%m", DATE "2017-12-22"),
FORMAT_DATE("%d", DATE "2017-12-22")
=>
Friday
Fri
December
Dec
2017-12-22
2017
12
22
PARSE_DATE(format, Date)
- ここのフォーマットの通りにパースする
SELECT PARSE_DATE("%x", "12/22/17")
=> 2017-12-22
UNIX_DATE(日付)
- 日付をunixdateに変換する
SELECT UNIX_DATE(DATE "2017-12-22")
=> 17522
終わりに,,,2017年最優秀Date関数賞の発表
FORMAT_DATE
⇨ ログデータを月ごと、曜日ごとで集計して分析する際によく使いました！