今回はBigQuery におけるDATE_ADD関数の使い方を紹介する。
以前紹介したDATE_TRUNC関数と合わせて使い方を覚えれば日付データの扱いの幅が大きく広がるので、是非参考にして欲しい。
DATE_ADD関数とは？
DATE_ADD関数は対象の日付データ（DATE型）に指定した時間間隔を追加することが出来る関数だ。
「WHERE句で納品予定日が今日から1週間後までのデータに絞り込みたい。」
という場合などに活躍する。
例えば、下記データ（order_dateはDATE型）をDATE_ADD関数を使用し、1ヶ月後の日付を取得する場合のクエリとその結果は下記の通りだ。
index | order_date |
|---|---|
| 1 | 2022-05-15 |
| 2 | 2022-05-20 |
| 3 | 2022-06-10 |
| 4 | 2022-06-25 |
| 5 | 2023-01-10 |
| 6 | 2023-01-20 |
| 7 | 2023-02-05 |
| 8 | 2023-02-20 |
| 9 | 2023-03-10 |
1 2 3 4 5 6 7 | SELECT order_date ,DATE_ADD(order_date,INTERVAL 1 MONTH) AS next_month FROM `sample_dataset.sample_table` ORDER BY order_date ; |
DATE_ADDによりorder_dateの1ヶ月後の値が取得出来ていることがお分かりいただけると思う。
実務においては、単純に1ヶ月後というよりは
「翌月の1日の値を取得したい」
という時もあるだろう。
その場合は下記クエリのようにDATE_TRUNC関数と組み合わせれば実現可能だ。
1 2 3 4 5 6 7 8 | SELECT order_date ,DATE_ADD(order_date,INTERVAL 1 MONTH) AS next_month ,DATE_TRUNC(DATE_ADD(order_date,INTERVAL 1 MONTH),MONTH) AS next_month_start FROM `sample_dateset.sample_table` ORDER BY order_date ; |
oder_dateの翌月1日の値を取得出来ている（next_month_start）ことがお分かりいただけると思う。
このようにDATE_ADD関数は対象の日付データに特定の時間間隔を追加したい時に非常に便利な関数だ。
DATE_ADD関数の使い方
DATE_ADD関数は下記を指定して使用する。
- 対象カラム：値を操作したいDATE型カラムを指定
※実際にはDATETIME型やTIMESTAMP型も指定できるが、それぞれ専用の関数が用意されているのでそちらを利用した方がよいだろう - 加算する期間数と単位：1ヶ月追加したいなら、1MONTHを指定
具体例に当てはめて確認してみよう。
index | order_date |
|---|---|
| 1 | 2022-05-15 |
| 2 | 2022-05-20 |
| 3 | 2022-06-10 |
| 4 | 2022-06-25 |
| 5 | 2023-01-10 |
| 6 | 2023-01-20 |
| 7 | 2023-02-05 |
| 8 | 2023-02-20 |
| 9 | 2023-03-10 |
DATE_ADD関数を使用して、上記データのorder_date（DATE型）の値から1年後の値を取得したnext_yearカラムを取得するクエリとその具体的な指定の詳細は下記の通りだ。
1 2 3 4 5 6 7 | SELECT order_date ,DATE_ADD(order_date,INTERVAL 1 YEAR) AS next_year FROM `sample_dataset.sample_table` ORDER BY order_date ; |
上記クエリの結果は下記となり、next_yearカラムではorder_dateの各値に1年加えた値が表示されているのがお分かりいただけると思う。
DATE_ADD関数は対象のデータに期間を加算する関数と先ほど説明したが、実際は期間の指定にマイナスを設定すれば減算することも可能だ。
※本来、時間の減算を行う用にDATE_SUB関数が用意されている
例えば、「今日の日付（24年4月15日）から1年前の値を取得したい」という場合は下記のように指定すればOKだ。
1 2 3 | SELECT CURRENT_DATE() AS today ,DATE_ADD(CURRENT_DATE(),INTERVAL -1 YEAR) AS last_year |
このクエリの結果は下記の通りだ。
マイナスの期間を指定することで1年前の値が取得出来ていることがお分かりいただけると思う。
これを利用すれば、「WHERE句で今日から1年前までのorder_dateの値に絞り込む」というような使い方も可能だ。
指定できる日付の単位
DATE_ADD関数で指定できる日付の単位は下記の通りだ。
| 日付の単位 | 説明 |
|---|---|
YEAR | 年単位 |
| QUARTER | 四半期(3ヶ月)単位 |
| MONTH | 月単位 |
| WEEK | 週（7日）単位 |
| DAY | 日単位 |
DATE_ADD関数は日時を表す下記データ型の内、DATE型カラム向けに作られている。
- DATE型
- DATETIME型
- TIMESTAMP型
- TIME型
そのため、DAYより細かい時間関係（HOURやSECOND等）の指定は出来ない。
まとめ
BigQueryにおけるDATE_ADD関数の要点は下記の通りだ。
- DATE_ADD関数は対象の日付データに指定した時間間隔を加算することが出来る関数
- CURRENT_DATEと組み合わせてWHERE句での絞り込みに活用されたり、DATE_TRUNC関数と組み合わせて利用することが多い
- 実際は期間にマイナスを指定することで指定した時間間隔を減算することも可能
他のBigQueryに関する記事は下記にまとめているので是非参考にして欲しい。
