【超便利！】BigQueryでよく使う日時関数の小技集

BigQueryの集計で必ず使う日時関連の関数をまとめてみました。
日時がパーティションキーとして利用することが多いため、日時関数を使用する機会が多いはずです。
何かと便利なので覚えといて損はないと思います。

BigQueryでは集計量単位での課金となるため、ビックデータの暴走を抑制するためにも日時データの操作は必須となります。

日付データの作成

任意の文字列から日付型とタイムスタンプ型データを取得する際は以下の関数を利用します。

• DATE()関数
• TIMESTAMP()関数

これらの第２引数にタイムゾーンを指定することによって、正しくUTCへの変換を行ってくれます。

こちらの例ではWITHを利用して仮想テーブルを作成してその結果をSELECT * で表示しています。

実行例

WITH sample_data AS(
    SELECT
     DATE('2021-12-31')AS date
    ,TIMESTAMP('2021-12-01 00:00:00')AS ts
    ,TIMESTAMP('2021-12-01 00:00:00', 'Asia/Tokyo')AS ts_jp
    ,TIMESTAMP('2021-11-01 00:00:00')AS ts_ago
)
SELECT * FROM sample_data

結果

日時の計算

加減算

日時の加算を行う場合は以下の関数を使用します

• DATE_ADD()関数
• TIMESTAMP_ADD()関数

DATE_SUB()関数やTIMESTAMP_SUB（）関数を使用すれば減算もできるのですが、数値に負の値を指定すれば減算が可能なため_ADD（）関数をメインで使用しています。

最後の引数部分に数値の単位を指定すればそれぞれの日時を算出してくれます

• 年(YEAR)
• 月(MONTH)
• 日(DAY)
• 時(HOUR)
• 分(MINUTE)
• 秒(SECOND)
• マイクロ秒(MICROSECOND)

実行例

SELECT
     date
    ,DATE_ADD(date, INTERVAL 3 DAY)
    ,DATE_ADD(date, INTERVAL -3 DAY)
    ,ts
    ,TIMESTAMP_ADD(ts, INTERVAL 3 DAY)
    ,TIMESTAMP_ADD(ts, INTERVAL 30 HOUR)
FROM sample_data

結果

日時計算

日時の差分を行う場合は以下の関数を使用します。

• DATE_DIFF()関数
• TIMESTAMP_DIFF()関数

最後の引数部分に数値の単位を指定すればそれぞれの日時を算出してくれます

• 日(DAY)
• 時(HOUR)
• 分(MINUTE)
• 秒(SECOND)
• マイクロ秒(MICROSECOND)

SELECT
     TIMESTAMP_DIFF(ts,ts_ago,DAY)
    ,TIMESTAMP_DIFF(ts,ts_ago,HOUR)
    ,TIMESTAMP_DIFF(ts,ts_ago,MINUTE)
FROM sample_data

結果

タイムスタンプのフォーマット変換

FORMAT_TIMESTAMP()関数を利用するとタイムスタンプのデータを任意のフォーマットで出力することが可能です。
取得する際にタイムゾーンを指定して表示用の日時文字列の整形を行います。

よく使用するフォーマット文字列

• %F 年月日(%Y-%m-%d)
• %Y 年(4桁)
• %m 月(01-12)
• %d 日(01-31)
• %X 時分秒(%H:%M:%S)
• %H 時(00-23)
• %M 分(00-59)
• %S 秒(00-59)
• %V ISO週番号(月曜日始まり)

SELECT
     FORMAT_TIMESTAMP('%F', ts)AS ts_date
    ,FORMAT_TIMESTAMP('%X', ts)AS ts_time
    ,FORMAT_TIMESTAMP('%F %X', ts_jp, 'Asia/Tokyo')AS jp_datetime
FROM sample_data

結果

タイムゾーン指定

BigQueryのタイムスタンプはUTC固定となっているため、日本の日時を登録する場合はUTCを考慮した値を指定する必要があります。
また、同様にBigQueryに登録されたタイムスタンプを取得して利用する際にはタイムゾーンを日本に変換しないと９時間もずれた値を操作することになってしまいます。

間違いを起こさないためにはタイムスタンプを利用する際にはタイムゾーンを意識するようにしましょう。

SELECT DATE(dt,'Asia/Tokyo')

結果

タイムスタンプ項目を期間指定

タイムスタンプ型の項目を日付(yyyy-mm-dd)で指定する際には注意が必要です。
UTCで登録されているタイムスタンプデータを日本時間で日付指定する場合はタイムゾーンの指定が必要となります。

    WHERE DATE(ts,'Asia/Tokyo') = "2021-01-01"

また、範囲指定する際は以下のようなBETWEENを使用しましょう。

    WHERE DATE(ts,'Asia/Tokyo') BETWEEN "2021-01-01" AND "2021-01-31" 

週次と月次の日付取得

データ分析の粒度として日次と週次と月次があります。
大抵の処理は日単位で行うため日次については考慮する必要はないと思うのですが、週次と月次においてはそうはいきません。

週と月の頭の日付を決めておいて、その日付でGROUP BYをすることになります。

具体的には以下のSELECT文をVIEWで作成しておき、日次集計を行なうSELECT文にLEFT JOINをして週日付(week_date)と月日付(moth_date)を取得します。
そしてそれぞれの日付でGROUP BYすると良いでしょう。

SELECT
    date
    ,MIN(date)OVER(PARTITION BY year,weekno)AS week_date
    ,DATE(EXTRACT(YEAR FROM date),EXTRACT(MONTH FROM date), 1)AS month_date
FROM(
    SELECT
        date
        ,FORMAT_DATE("%Y", date)AS year
        ,FORMAT_DATE("%V", date)AS weekno
    FROM UNNEST(
            GENERATE_DATE_ARRAY('2019-01-01', '2025-12-31', INTERVAL 1 DAY)
    )AS date
)
ORDER BY 1

結果