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

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

##クエリリファレンス
###前日

SELECT
  *
FROM
  `ds.table_P*`
WHERE
  _TABLE_SUFFIX = FORMAT_DATE("%Y%m%d", CURRENT_DATE('Asia/Tokyo') - 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"を付けることにしました。

終わりに

前の投稿から３年以上が経っていました。最近は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