【SQL】BigQueryで日付型を扱う

弊社の研修で、PostgreSQLを使ってDATE型の使い方を学びました。
理解に苦労したため、こちらで復習の意味を込めて、BigQueryでのDATE型及びTIMESTAMP型の使い方をシェアしようと思います。

#概要
研修で習ったPostgreSQLのコードは、そのままBigQueryで使えるか検証
BigQueryでつかうTIMESTAMP型とDATE型の加算・差の導き方を学ぶ

今回の学ぶ内容は下記のサイトにて確認しました。

https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types?hl=ja

#利用テーブル

弊社で使ったサンプルテーブル
##テーブル名：purchase_log

| 行 | user_id | last_date |p_date |item|
|-----------|:------------|------------|------------|------------|
| 1     |        A |     	2016-01-30     | 2016-01-30 | メロン|
| 2    |      A |    	2017-12-30    | 2016-01-30 | 白菜|
| 3      |       B |     	2016-10-31     | 2016-01-30 | りんご|
| 4         |          B |      2017-06-30        |2016-01-30  | りんご|



上記のcsvで作ったテーブルをBigQueryに自動で読み込んでもらうと。。。下記のように認識された。

|フィールド名|	タイプ|	モード|
|:-----------|:------------|:------------|
|<b>user_id</b>|	STRING	|NULLABLE |	
|<b>last_date</b>	|DATE	|NULLABLE |	
|<b>p_date	</b>|DATE	|NULLABLE|	
|<b>item</b>|	STRING	|NULLABLE |	

文字列(user_id,item)はSTRING型として、日時項目（last_date,p_date）はDATE型として認識されました。


##実践１:その日から何日後、何日前を出力してみる


早速　、習ったばかりのPostgreSQLのコードを使い、DATE型の加算・引算をやってみました。

```sql:PostgreSQLのコードをそのまま使った
SELECT
  user_id,
  last_date,
  (last_date + 10) AS ld_plus,
  (last_date - 10) AS ld_minus
FROM
  test1.purchase_log ;
```
しかし、BigQueryではエラー警告が出ました！

```
--Errorコメント--
No matching signature for operator + for argument types:
 DATE, INT64. Supported signatures: INT64 + INT64; FLOAT64 + FLOAT64;
 NUMERIC + NUMERIC at [5:2]
```
上文を要約すると、
「<B>DATE型はINT64型と加算できません。加算（引算）はINT64型、FLOAT64型、NUMERIC型同士で実行しなさい</b>」とのこと!

ではどのようにlast_dateの10日後を表現したらよいか。。。


先輩に聞くと丁寧に教えてくれました。

```sql:BigQuery
SELECT
  user_id,
  last_date,
  DATE_ADD(last_date,INTERVAL 10 day ) AS ld_plu,
  DATE_SUB(last_date,INTERVAL 10 day) AS ld_minus
FROM
  test1.purchase_log;

```
![image.png](https://qiita-image-store.s3.amazonaws.com/0/352801/1ab15a78-cdf1-9973-2ab9-778a89fbf471.png)
このINTERVALと新関数DATE_ADD,DATE_SUBをつかうことで加算、引算が実行されました。
単純にDATE型プラスマイナスを使えたのはPostgreSQLの恩恵だったと気づきました


##実践２:二つの日付から差の日数を導け

DATE型-DATE型は果たして可能なのでしょうか！

まず研修で使ったコードをそのまま実行してみます。

```sql:PostgreSQLでコードをそのまま使う
SELECT
  user_id,
  last_date,
  p_date,
  (p_date - last_date) AS date
FROM
  test1.purchase_log;
```
すると

```
--Errorコメント--
No matching signature for operator + for argument types:
 DATE, INT64. Supported signatures: INT64 + INT64; FLOAT64 + FLOAT64;
 NUMERIC + NUMERIC at [5:2]
```

またしても、PostgreSQLのコードはRejectされました。DATE型の計算はBigQueryでは受け付けてくれないようです。
心がおれそうです。

先輩に確認し、こちらでもPostgreSQLとは、別の関数を使うと良いと教えてもらいました。

```sql:BigQuery
SELECT
  user_id,
  last_date,
  p_date,
  DATE_DIFF(p_date,last_date,day) AS date
FROM
  test1.purchase_log;
```
![image.png](https://qiita-image-store.s3.amazonaws.com/0/352801/d26541c4-ca4e-bfe2-2ae4-dd452fe5f081.png)


うまくdateカラムに二つの日付の差が表示されました。
##実践３:TIMESTAMP型で指定の日数後、二つの日付の差を求めたい
DATE型についてPostgreSQLとBigQueryでは大きく扱いが異なりました。
はたして、TIMESTAMP型ではどのようになるか試してみます。

まず、DATE型をTIMESTAMP型へ変換します。


```SQL:BigQuery
SELECT
  user_id,
  CAST(last_date AS TIMESTAMP) AS last_date,
  CAST(p_date AS TIMESTAMP) AS p_date
FROM
  test1.purchase_log;
```

特にPostgreSQLのコードがちゃんと実行されました結果



BigQueryのTIMESTAMP型は”2016-01-30 00:00:00 UTC”の形がデフォルトの模様

今使用したコードをサブクエリとして利用し、last_date +10 とp_date -last_dateを試してみます、。。。。と思ったのですが、
すでにBigQueryではTIMESTAMP型の直接の加算引算が対応していない、と警告が出てました。
おとなしくBigQueryで対応しているTIMESTAMP型の関数を適用してみます。

```sql:BigQuery
SELECT
  user_id,
  last_date,
  TIMESTAMP_ADD(last_date, INTERVAL 10 day)AS after,
  TIMESTAMP_DIFF(p_date,last_date, day) AS Diff
FROM (
  SELECT
    user_id,
    CAST(last_date AS TIMESTAMP) AS last_date,
    CAST(p_date AS TIMESTAMP) AS p_date
  FROM
    test1.purchase_log) AS a;
```
実行すると、
![image.png](https://qiita-image-store.s3.amazonaws.com/0/352801/fdf69a8d-ef3e-1a9e-21e6-8b38373e2c04.png)

TIMESTAMP_ADDでlast_dateから10日後を
TIMESTAMP_DIFFでp_dateとlast_dateの差を求めることができました。

##実践4:表示をJST(日本標準時）へ変更したい
TIMESTAMP型へ変更すると、UTC（協定世界時）で表示されました。せっかく日本に生まれたのでJST時間へ変更してみます。
実践3で利用したサブクエリをつかって以下を実行します。

``` sql:BigQuery
SELECT
  user_id,
  last_date,
  FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', last_date, 'Asia/Tokyo') AS JST
FROM (
  SELECT
    user_id,
    CAST(last_date AS TIMESTAMP) AS last_date,
    CAST(p_date AS TIMESTAMP) AS p_date
  FROM
    test1.purchase_log) AS a;
```


結果、以下のようにテーブルが作成されました。
![image.png](https://qiita-image-store.s3.amazonaws.com/0/352801/11caac0d-e7b7-9c0c-74a3-984d9e41cb21.png)

last_dateに＋9時間され、JSTの時間が表記されました。一点注意で、FORMAT_TIMESTAMP関数を使うとSTRING型に変更されるためJSTカラムも加算、引算はできません！


##まとめ

今回は、サンプル問題で、PostgreSQLとBigQueryのDATE型とTIMESTAMP型の取り扱いの違いを学ぶことができました。
予想以上に取り扱いが異なり、めんくらいましたが、今後は、BigQueryを扱う上で注意していこうと思います。