BigQueryで「今から3ヶ月前より後」のデータを取得する

どうも！スペースマーケットのrokioです！

BigQueryで「今から3ヶ月前より後」という条件でクエリを書きたくなり、
そこで色々考えたので記事にしてみます！

「3ヶ月前」の定義

今日が2025/6/15だとしたら、3ヶ月前とは何日のことでしょうか？

• (a) 月の部分だけ-3した、2025/3/15でしょうか？
• (b) それともざっくりと30 x 3 = 90日前でしょうか？
  • 3/15は6/15の92日前です。

要件は状況によって異なると思いますので、
上記2パターン(a)(b)を実現するクエリをそれぞれ考えることにします。

前提

以下の前提で話を進めます。

• users というテーブルが存在する。
• users テーブルには created_at という TIMESTAMP 型のカラムが存在し、ユーザー作成日時が記録されている。
• この created_at が「今から3ヶ月前以降」のデータを絞り込む。

TIMESTAMP型だけを使うクエリ

「今」はCURRENT_TIMESTAMP関数で取得できます。

〜から〇〇前はTIMESTAMP_SUB関数で計算できます。

(a)パターンのつもりで

SELECT * FROM users 
WHERE created_at > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 3 MONTH)

というクエリを書くと、

TIMESTAMP_SUB does not support the MONTH date part when the argument is TIMESTAMP type at ...

というエラーになります。

ドキュメントから引用しますが、TIMESTAMP_SUBの定義をみると...👀

TIMESTAMP_SUB(timestamp_expression, INTERVAL int64_expression date_part)

TIMESTAMP_SUB supports the following values for date_part:
MICROSECOND
MILLISECOND
SECOND
MINUTE
HOUR. Equivalent to 60 MINUTE parts.
DAY. Equivalent to 24 HOUR parts.

date_partにあたる部分にMONTHは入れられないですね！

つまり、このアプローチは(b)パターンを実現するのに適しているでしょう！

SELECT * FROM users 
WHERE created_at > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY)

DATETIME型とTIMESTAMP型を併用するクエリ

では(a)パターンを実現するにはどうすればいいでしょうか？

DATETIME_SUB関数なら月の引き算ができそうです！

DATETIME型の「今」を取得してから、3ヶ月分を引いて、
それをTIMESTAMP型に変換すればやりたいことが実現できそうです。
ただし、3ヶ月を引くとき、機械的に月を3引くだけなので、
タイムゾーンを意識しないと実際に引かれる日数が変わってしまいます！
例をあげると...

2025/5/1 - 2025/2/1 = 89日
2025/4/30 - 2025/1/30 = 90日

タイムゾーンへの意識は向けておきたいですね！

DATETIME型の「今」を取得するにはCURRENT_DATETIME関数を使えば良さそうです。

「今」がJSTの場合は、引数に”Asia/Tokyo”を渡すようにします。

BigQueryでのタイムゾーンの説明はこちら：
https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#time_zones

タイムゾーンを指定するときに使う文字列はこちらで分かります！

DATETIME型からTIMESTAMP型に変換できるのはTIMESTAMP関数です。
定義は以下の通りで、

TIMESTAMP(datetime_expression[, time_zone])

datetime_expressionのタイムゾーンがUTC以外の場合は、
time_zoneを指定する必要があります。

以上の情報をまとめるとこうなります！

SELECT * FROM users 
WHERE created_at > TIMESTAMP(
  DATETIME_SUB(
    CURRENT_DATETIME("Asia/Tokyo"), 
    INTERVAL 3 MONTH
  ), 
  "Asia/Tokyo"
)

まとめ

(a)(b)パターン両方をクエリにすることができました！
作る過程で意外と考えることがあり勉強になりました。

皆さんの参考になれば幸いです！