弊社の研修で、PostgreSQLを使ってDATE型の使い方を学びました。
理解に苦労したため、こちらで復習の意味を込めて、BigQueryでのDATE型及びTIMESTAMP型の使い方をシェアしようと思います。
概要
研修で習ったPostgreSQLのコードは、そのままBigQueryで使えるか検証
BigQueryでつかうTIMESTAMP型とDATE型の加算・差の導き方を学ぶ
今回の学ぶ内容は下記のサイトにて確認しました。
利用テーブル
弊社で使ったサンプルテーブル
テーブル名：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に自動で読み込んでもらうと。。。下記のように認識された。
| フィールド名 | タイプ | モード |
|---|---|---|
| user_id | STRING | NULLABLE |
| last_date | DATE | NULLABLE |
| p_date | DATE | NULLABLE |
| item | STRING | NULLABLE |
文字列(user_id,item)はSTRING型として、日時項目（last_date,p_date）はDATE型として認識されました。
実践１:その日から何日後、何日前を出力してみる
早速 、習ったばかりのPostgreSQLのコードを使い、DATE型の加算・引算をやってみました。
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]
上文を要約すると、
「DATE型はINT64型と加算できません。加算（引算）はINT64型、FLOAT64型、NUMERIC型同士で実行しなさい」とのこと!
ではどのようにlast_dateの10日後を表現したらよいか。。。
先輩に聞くと丁寧に教えてくれました。
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;
このINTERVALと新関数DATE_ADD,DATE_SUBをつかうことで加算、引算が実行されました。
単純にDATE型プラスマイナスを使えたのはPostgreSQLの恩恵だったと気づきました
実践２:二つの日付から差の日数を導け
DATE型-DATE型は果たして可能なのでしょうか！
まず研修で使ったコードをそのまま実行してみます。
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とは、別の関数を使うと良いと教えてもらいました。
SELECT
user_id,
last_date,
p_date,
DATE_DIFF(p_date,last_date,day) AS date
FROM
test1.purchase_log;
うまくdateカラムに二つの日付の差が表示されました。
実践３:TIMESTAMP型で指定の日数後、二つの日付の差を求めたい
DATE型についてPostgreSQLとBigQueryでは大きく扱いが異なりました。
はたして、TIMESTAMP型ではどのようになるか試してみます。
まず、DATE型をTIMESTAMP型へ変換します。
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型の関数を適用してみます。
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;
実行すると、
TIMESTAMP_ADDでlast_dateから10日後を
TIMESTAMP_DIFFでp_dateとlast_dateの差を求めることができました。
実践4:表示をJST(日本標準時）へ変更したい
TIMESTAMP型へ変更すると、UTC（協定世界時）で表示されました。せっかく日本に生まれたのでJST時間へ変更してみます。
実践3で利用したサブクエリをつかって以下を実行します。
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;
結果、以下のようにテーブルが作成されました。
last_dateに＋9時間され、JSTの時間が表記されました。一点注意で、FORMAT_TIMESTAMP関数を使うとSTRING型に変更されるためJSTカラムも加算、引算はできません！
まとめ
今回は、サンプル問題で、PostgreSQLとBigQueryのDATE型とTIMESTAMP型の取り扱いの違いを学ぶことができました。
予想以上に取り扱いが異なり、めんくらいましたが、今後は、BigQueryを扱う上で注意していこうと思います。