BigQueryで現在から見た先月月初日、先月月末日を求める

非エンジニアからもらったクエリがすごかったので紹介

前提条件

• STANDARD SQLです

やり方

先月月初日

FORMAT_DATE("%Y%m%d",DATE_TRUNC(DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 1 MONTH), MONTH))

原理

# 当日東京時間
CURRENT_DATE('Asia/Tokyo')

# 当日東京時間 から 1ヶ月前
DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 1 MONTH)

# 当日東京時間 から 1ヶ月前 で 年月情報のみにする
DATE_TRUNC(DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 1 MONTH), MONTH)

# 当日東京時間 から 1ヶ月前 で 年月情報のみにして YYMMDD にする（日付情報は無くなっていたので１になる）
FORMAT_DATE("%Y%m%d", DATE_TRUNC(DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 1 MONTH), MONTH))

結果例

SELECT FORMAT_DATE("%Y%m%d", DATE_TRUNC(DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 1 MONTH), MONTH))

↓

先月月末日

FORMAT_DATE("%Y%m%d",DATE_SUB(DATE_TRUNC(CURRENT_DATE('Asia/Tokyo'), MONTH), INTERVAL 1 DAY))

原理

# 当日東京時間
CURRENT_DATE('Asia/Tokyo')

# 当日東京時間 で 年月情報のみにする
DATE_TRUNC(CURRENT_DATE('Asia/Tokyo'), MONTH)

# 当日東京時間 で 年月情報のみにしたものから 1日前（日付情報がなかったので今月月初日に対して1日前）
DATE_SUB(DATE_TRUNC(CURRENT_DATE('Asia/Tokyo'), MONTH), INTERVAL 1 DAY)

# 当日東京時間 で 年月情報のみにしたものから 1日前を YYMMDD にする
FORMAT_DATE("%Y%m%d",DATE_SUB(DATE_TRUNC(CURRENT_DATE('Asia/Tokyo'), MONTH), INTERVAL 1 DAY))

結果例

SELECT FORMAT_DATE("%Y%m%d",DATE_SUB(DATE_TRUNC(CURRENT_DATE('Asia/Tokyo'), MONTH), INTERVAL 1 DAY))

↓

備考

FORMAT_DATE を変えれば 00:00:00 のデータを出すのは容易そうなので応用効きそう。