11
8

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

【SQL】BigQueryで日付型を扱う

Last updated at Posted at 2019-02-21

弊社の研修で、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型として認識されました。

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

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

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]

上文を要約すると、
DATE型はINT64型と加算できません。加算(引算)はINT64型、FLOAT64型、NUMERIC型同士で実行しなさい」とのこと!

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

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

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
このINTERVALと新関数DATE_ADD,DATE_SUBをつかうことで加算、引算が実行されました。
単純にDATE型プラスマイナスを使えたのはPostgreSQLの恩恵だったと気づきました

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

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

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

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とは、別の関数を使うと良いと教えてもらいました。

BigQuery
SELECT
  user_id,
  last_date,
  p_date,
  DATE_DIFF(p_date,last_date,day) AS date
FROM
  test1.purchase_log;

image.png

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

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

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型の関数を適用してみます。

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

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

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

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

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

##まとめ

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

11
8
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
11
8

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?