65
Help us understand the problem. What are the problem?

More than 1 year has passed since last update.

posted at

updated 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
  ⇨ ログデータを月ごと、曜日ごとで集計して分析する際によく使いました!

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
65
Help us understand the problem. What are the problem?