LoginSignup
2
0

More than 3 years have passed since last update.

BigQueryの日付テーブルにおける、日付指定リファレンス(2020)

Posted at

はじめに

BigQueryの日付を付与したワイルドカードテーブルから、日付を指定するクエリをリファレンスとして記載しています。
以前に「BigQueryの日付テーブルにおける、日付指定リファレンス」を投稿しています。日付算術演算子やLAST_DAYファンクションが使えるようになったので、更新しました。

クエリリファレンス

前日

SELECT
  *
FROM
  `ds.table_P*`
WHERE
  _TABLE_SUFFIX = FORMAT_DATE("%Y%m%d", CURRENT_DATE('Asia/Tokyo') - 1)

前月

前月の1日から最終日。
前月1日、前月末日を指定する場合には、BETWEEN〜AND〜が使えます。

SELECT
  *
FROM
  `ds.table_P*`
WHERE
  _TABLE_SUFFIX BETWEEN 
  FORMAT_DATE("%Y%m%d", DATE_TRUNC(DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 1 MONTH), MONTH))
  AND FORMAT_DATE("%Y%m%d", LAST_DAY(DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 1 MONTH), MONTH))

前年

SELECT
  *
FROM
  `ds.table_P*`
WHERE
  _TABLE_SUFFIX BETWEEN 
  FORMAT_DATE("%Y%m%d", DATE_TRUNC(DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 1 YEAR), YEAR))
  AND FORMAT_DATE("%Y%m%d", LAST_DAY(DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 1 YEAR), YEAR))

前週

日曜日〜土曜日の指定。

SELECT
  *
FROM
  `ds.table_P*`
WHERE
  _TABLE_SUFFIX BETWEEN   
  FORMAT_DATE("%Y%m%d", DATE_TRUNC(CURRENT_DATE('Asia/Tokyo') - 7, WEEK(SUNDAY)))
  AND FORMAT_DATE("%Y%m%d", LAST_DAY(CURRENT_DATE('Asia/Tokyo') - 7, WEEK(SUNDAY)))

日付文字列変換など覚え書き

SELECT
  FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', CURRENT_TIMESTAMP()), --UTC
  CURRENT_DATETIME('Asia/Tokyo'), --JST
  STRING(CURRENT_TIMESTAMP(), 'Asia/Tokyo'), --日本(+9H)
  STRING(CURRENT_TIMESTAMP(), '+8') --中国など(+8H)

注意点

  • CURRENT_TIMESTAMP()を使うと、タイムゾーン指定が出来ないので、DATE型やDATETIME型を使用しています。
  • テーブルのプレフィックスには注意が必要です。公式ドキュメントに記載があります。 https://cloud.google.com/bigquery/docs/wildcard-tables#best_practices 例えば、table_20170727とtable_tmp_20170727というテーブルがあった場合、table_*を指定すると両方のテーブルが一致してしまうためです。そのため、パーティションを示す"_P"を付けることにしました。

終わりに

前の投稿から3年以上が経っていました。最近はBigQueryと疎遠で、キャッチアップできてないです・・・

参考

https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#supported-format-elements-for-datetime
日付算術演算子
https://cloud.google.com/bigquery/docs/reference/standard-sql/operators#date_arithmetics_operators

2
0
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
2
0