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

# はじめに
BigQueryで日付の操作をすることはしばしば起こります。
その度に調べるのも面倒ですし、まだ使ったことのない関数もあるので
この機会に日時に関する関数を全て試したので紹介します。
下記の4本立てで書いていきます。
**①Date編←今ここ**
②DateTime編
③Time編
④TimeStamp編


**※注意**

- StandardSQLでのみ動作を確認しています。
- 引数に「日付」と出てきますが、「YYYY-MM-DD」形式のことを指しています。
- 例のクエリでDATE('2017-12-22')のように出てきますが、文字列「2017-12-22」を日付にキャストしています。 

---
###  CURRENT_DATE() 
- 現在の日付を取得できる
- 引数にtimezoneを指定できる(指定がない場合はUTC)

```sql
SELECT CURRENT_DATE()

=> 2017-12-22
```

```sql
# タイムゾーンを指定
SELECT CURRENT_DATE('Asia/Tokyo')

=> 2017-12-22
```
---
### EXTRACT(part FROM 日付)
- 日付に対応するpartが取得できる
- partに指定できるのは以下(ISO8601 週番号を返すものもありますが、あまり使わないかと。。。)
    - DAYOFWEEK
        - 1(日曜日) ~ 7（土曜日）の数値を返す
    - DAY
    - DAYOFYEAR
    - MONTH
    - QUARTER
        - 1 ~ 4の数値を返す
    - YEAR

```sql
# 曜日を取得
SELECT EXTRACT(DAYOFWEEK FROM  DATE ('2017-12-22'))

=> 6 (金曜日)
```
```sql
# 日付を取得
SELECT EXTRACT(DAY FROM  DATE ('2017-12-22') )

=> 22
```
```sql
# 通日を取得
SELECT EXTRACT(DAYOFYEAR FROM  DATE ('2017-12-22') )

=> 356
```
```sql
# 月を取得
SELECT EXTRACT(MONTH FROM  DATE ('2017-12-22') )

=> 12
```
```sql
#　クオーターを取得(１月から数えて)
SELECT EXTRACT(QUARTER FROM  DATE ('2017-12-22') )


=> 4
```
```sql
#　年を取得
SELECT EXTRACT(YEAR FROM  DATE ('2017-12-22') )

=> 2017
```
---

### DATE
- 日付が取得できる
- 書き方は以下の２通り
    - DATE(year,month,day)
    - DATE(timestamp,[必要であればtimezoneを指定])
    
```sql
SELECT DATE (2017,12,22) 

=> 2017-12-22
```
```sql
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
    
```sql
# 指定した日付から１日後の日付を取得
SELECT DATE_ADD(DATE '2017-12-22', INTERVAL 1 DAY)

=> 2017-12-23
```
```sql
# 指定した日付から２週間後の日付を取得
SELECT DATE_ADD(DATE '2017-12-22', INTERVAL 2 WEEK)

=> 2018-01-05
```
##### **注意）1MONTH = 30DAY ではない**
```sql
# 指定した日から１ヵ月後の日付を取得
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と同じ

```sql
# 指定した日付から１日前の日付を取得
SELECT DATE_SUB(DATE '2017-12-22', INTERVAL 1 DAY)

=> 2017-12-21
```
```sql
# 指定した日付から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が書いてませんが、指定できます](https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators?hl=ja#date_diff)


```sql
# 2017/12/22と2017/12/12が何日差か取得
SELECT DATE_DIFF(DATE "2017-12-22", DATE "2017-12-12", DAY) 

=> 10
```
##### **注意）第二引数日付の方が大きい場合は負の値になる**
```sql
SELECT DATE_DIFF(DATE "2017-12-12", DATE "2017-12-22", DAY) 

=> -10
```

```sql
# 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
    
```sql
# 2017/12/22を月まで切り詰める
SELECT DATE_TRUNC(DATE "2017-12-22", MONTH)

=> 2017-12-01
```
```sql
# 2017/12/22を週まで切り詰める
SELECT DATE_TRUNC(DATE "2017-12-22", WEEK)

=> 2017-12-17
```
```sql
# 2017/12/22を年まで切り詰める
SELECT DATE_TRUNC(DATE "2017-12-22", YEAR) 

=> 2017-01-01
```
---
### DATE_FROM_UNIX_DATE(unixdate)
- UNIXDATEから日付を取得できる

```sql
SELECT DATE_FROM_UNIX_DATE(17522)

=> 2017-12-22
```
---
### FORMAT_DATE(format, 日付)
- 指定したフォーマットに、日付の形式を変更する
- 表示形式は[ここ](https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators?hl=ja#supported-format-elements-for-date)に載っていますが、個人的によく使うものをいくつか紹介します
    - %a:省略した曜日名
    - %b:省略した月名
    - %F:YYYY-MM-DD
    - %Y:西暦４桁
    - %m:月
    - %d:日

```sql
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)
- [ここ](https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators?hl=ja#supported-format-elements-for-date)のフォーマットの通りにパースする

```sql
SELECT PARSE_DATE("%x", "12/22/17")

=> 2017-12-22
```
---
### UNIX_DATE(日付)
- 日付をunixdateに変換する

```sql
SELECT UNIX_DATE(DATE "2017-12-22")

=> 17522
```


## 終わりに,,,2017年最優秀Date関数賞の発表
**FORMAT_DATE**
　　⇨ ログデータを月ごと、曜日ごとで集計して分析する際によく使いました！