BigQueryの日付関数の使い方についてまとめる

BigQuery の日付関数の使い方についてまとめる

BigQuery では日付関数がサポートされています。この記事は BigQuery でよく使う日付関数を書いていきます。

DATE

DATEは日付を取得できる関数です。書き方は以下の 3 通りがあります。

DATE(year, month, day) : 年、月、日を表す DATE を INT64 値から構築する。
DATE(timestamp_expression[, time_zone]) : TIMESTAMP 式から DATE を抽出する。timezone の指定が可能。
DATE(datetime_expression) : DATETIME 式から DATE を抽出する。

例

SELECT
  DATE(2016, 12, 25) AS date_ymd,
  DATE(DATETIME "2016-12-25 23:59:59") AS date_dt,
  DATE(TIMESTAMP "2016-12-25 05:30:00+07", "America/Los_Angeles") AS date_tstz;

+------------+------------+------------+
| date_ymd   | date_dt    | date_tstz  |
+------------+------------+------------+
| 2016-12-25 | 2016-12-25 | 2016-12-24 |
+------------+------------+------------+

EXTRACT

EXTRACT(part FROM date_expression)

EXTRACT は日付からpartパラメータが指定する部分を取得できます。 partパラメータが指定できる

DAYOFWEEK : 1（日曜日） ~ 7（土曜日）の数値を返す
DAY
DAYOFYEAR
MONTH
YEAR
QUARTER : 1 ~ 4 の数値を返す

例

# 日付から日, 週, 月, を取得
SELECT
    EXTRACT(DAY FROM DATE '2013-12-25') AS the_day,
    EXTRACT(DAYOFWEEK FROM date) AS week AS the_week,
    EXTRACT(YEAR FROM date) AS year AS the_month;


+---------+----------+-----------+
| the_day | the_week | the_month |
+---------+----------+-----------+
| 25      |4(水曜日) | 12        |
+---------+----------+-----------+

DATE_ADD と DATE_SUB

DATE_ADD と DATE_SUB は指定した日付に x を加算または減算した日付を取得できます

DATE_ADD(date_expression, INTERVAL date_part)
DATE_SUB(date_expression, INTERVAL date_part)

date_partに指定できるのは下記になります。

DAY
WEEK （7 DAY と等価）
MONTH
QUARTER
YEAR

例

SELECT
    # 指定した日付から5日後の日付を取得
    DATE_ADD(DATE "2008-12-25", INTERVAL 5 DAY) AS five_days_later,
    # 指定した日付から5日前の日付を取得
    DATE_SUB(DATE "2008-12-25", INTERVAL 5 DAY) AS five_days_ago;

+--------------------+---------------+
| five_days_later    | five_days_ago |
+--------------------+---------------+
| 2008-12-30         | 2008-12-20    |
+--------------------+---------------+

DATE_DIFF

日付 a と日付 b の差を指定されたdate_partの単位で取得できます。

DATE_DIFF(date_expression_a, date_expression_b, date_part)

date_partに指定できるのは下記になります。

DAY
WEEK （7 DAY と等価）
MONTH
QUARTER
YEAR

例

SELECT
    # 2017-12-22と2017-12-12が何日差か取得
    DATE_DIFF(DATE "2017-12-22", DATE "2017-12-12", DAY) AS diff_day,
    # 2017-12-22と2017-12-12が何週差か取得
    DATE_DIFF(DATE "2017-12-22", DATE "2017-12-12", WEEK) AS diff_week;

+-------------+-----------+
| diff_day    | diff_week |
+-------------+-----------+
| 10          | 1         |
+-------------+-----------+

その他の日付関数

ここでは一部の日付関数紹介しました。ほかにも日付関数がありますので、より詳しい内容は公式のドキュメントで確認してください。

参考

備考

Hakky では社内のデータ活用やサービスとしてデータ提供を行うためのソリューションを展開しております。

「BigQuery の管理や使い方に困っている」など具体的な相談はもちろんのこと、「どんなことをお願いできるのか知りたい」や「こんなことをやりたい」など、ご検討段階でも構いませんので、ぜひお気軽にフォームよりお問い合わせくださいませ。