【BigQuery】標準SQLで日付処理をする

わたしへ。忘れてたでしょ。わたしより。

きも。

・日付を扱うための型として、timestamp型と、datetime型がある。timestamp型は年-月-日-時-分-秒まで扱えるが、datetime型は日まで。混同しない。

・日付計算にはDATETIME_SUB(datetime('地域名'),INTERVAL n DAY)を使う

・TIMESTAMP(datetime,"地域名")で、datetime型をtimestamp型に変換できる

・FORMAT_TIMESTAMP("%Y-%m-%d %H:%M:%s",timestamp)でtimestamp型の値を文字列に変換できる

・CURRENT_DATETIME("地域名")で、該当地域の「今日」を取得

・地域名を指定しないとUSTで計算されて、日本で実行したときに日付がずれるので、JSTになるよう地域には"Asia/Tokyo"を指定する。

全然実用的じゃない実用例

SELECT TIMESTAMP(
    FORMAT_TIMESTAMP(
        "%Y-%m-%d 00:00:00",
        TIMESTAMP(DATETIME_SUB(
            DATETIME_SUB(CURRENT_DATETIME("Asia/Tokyo"), INTERVAL 1 MONTH),
            INTERVAL 1 DAY)
        ),
    "Asia/Tokyo")
)

「今日の一ヶ月前、の、1日前」を取得する。

まず

DATETIME_SUB(CURRENT_DATETIME("Asia/Tokyo"), INTERVAL 1 MONTH)

で、「今日の一ヶ月前」を取得

それをさらに

DATETIME_SUB("今日の一ヶ月前" , INTERVAL 1 DAY)

で「一ヶ月前の1日前」を取得

さらにさらに

FORMAT_TIMESTAMP("%Y-%m-%d 00:00:00","一ヶ月の1日前")

で、一ヶ月の1日前の00:00:00時点のtimestamp型対応文字列に変換。

さらにさらにさらに

TIMESTAMP("timestamp型対応文字列","Asia/Tokyo")

でtimestamp型に変換。

ああめんどくさかった……でもどうしてもこうしないといけない案件があったんだ……