Edited at

Bigqueryの日時に関係する関数全部試してみた ①Date編


はじめに

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



# 曜日を取得

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

# クオーターを取得(1月から数えて)

SELECT EXTRACT(QUARTER FROM DATE ('2017-12-22') )

=> 4

# 年を取得

SELECT EXTRACT(YEAR FROM DATE ('2017-12-22') )

=> 2017



DATE


  • 日付が取得できる

  • 書き方は以下の2通り


    • 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



# 指定した日付から1日後の日付を取得

SELECT DATE_ADD(DATE '2017-12-22', INTERVAL 1 DAY)

=> 2017-12-23

# 指定した日付から2週間後の日付を取得

SELECT DATE_ADD(DATE '2017-12-22', INTERVAL 2 WEEK)

=> 2018-01-05


注意)1MONTH = 30DAY ではない

# 指定した日から1ヵ月後の日付を取得

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と同じ

# 指定した日付から1日前の日付を取得

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/222017/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/222017/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:西暦4桁

    • %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

  ⇨ ログデータを月ごと、曜日ごとで集計して分析する際によく使いました!