Big Query 今日の開始時刻を取得するユーザー定義の関数を作った

昨日１日分のレコードを取得したいときとかに、タイムゾーンを指定して、昨日の午前0時と、午後11時59分59.999999秒のTIMESTAMPを取得したいので、つくった。

CREATE TEMPORARY FUNCTION BEGINNING_OF_DAY_CURRENT_DATE_SUB(expr INT64, timezone STRING) RETURNS TIMESTAMP AS (
  TIMESTAMP_SUB(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY, timezone), INTERVAL 24 * expr HOUR)
);
CREATE TEMPORARY FUNCTION END_OF_DAY_CURRENT_DATE_SUB(expr INT64, timezone STRING) RETURNS TIMESTAMP AS (
  TIMESTAMP_SUB(TIMESTAMP_SUB(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY, timezone), INTERVAL 24 * (expr - 1) HOUR), INTERVAL 1 MICROSECOND)
);

SELECT
    CURRENT_DATETIME() AS utc_now,
    CURRENT_DATETIME('Asia/Tokyo') AS local_now,
    BEGINNING_OF_DAY_CURRENT_DATE_SUB(0, 'Asia/Tokyo') AS beginning_of_today,
    END_OF_DAY_CURRENT_DATE_SUB(0, 'Asia/Tokyo') AS end_of_today,
    BEGINNING_OF_DAY_CURRENT_DATE_SUB(1, 'Asia/Tokyo') AS beginning_of_yesterday,
    END_OF_DAY_CURRENT_DATE_SUB(1, 'Asia/Tokyo') AS end_of_yesterday,
    BEGINNING_OF_DAY_CURRENT_DATE_SUB(5, 'Asia/Tokyo') AS beginning_of_5days_ago,
    END_OF_DAY_CURRENT_DATE_SUB(5, 'Asia/Tokyo') AS end_of_5days_ago,
    FORMAT_TIMESTAMP("%Y%m%d",BEGINNING_OF_DAY_CURRENT_DATE_SUB(5, 'Asia/Tokyo')) AS TABLE_SUFFIX_from_5days_ago,
    FORMAT_TIMESTAMP("%Y%m%d",END_OF_DAY_CURRENT_DATE_SUB(1, 'Asia/Tokyo')) AS TABLE_SUFFIX_to_yesterday

[
  {
    "utc_now": "2018-10-05T01:06:56.595840",
    "local_now": "2018-10-05T10:06:56.595840",
    "beginning_of_today": "2018-10-04 15:00:00 UTC",
    "end_of_today": "2018-10-05 14:59:59.999999 UTC",
    "beginning_of_yesterday": "2018-10-03 15:00:00 UTC",
    "end_of_yesterday": "2018-10-04 14:59:59.999999 UTC",
    "beginning_of_5days_ago": "2018-09-29 15:00:00 UTC",
    "end_of_5days_ago": "2018-09-30 14:59:59.999999 UTC",
    "TABLE_SUFFIX_from_5days_ago": "20180929",
    "TABLE_SUFFIX_to_yesterday": "20181004"
  }
]

_TABLE_SUFFIXとtimeカラムをつかって絞り込む

_TABLE_SUFFIXで必要なテーブルの日付を決定しつつ、時刻が入っているtimeカラムをつかって、狙った期間のレコードを対象とする

こんなテーブルがあったとする

sample_20180928
sample_20180929
sample_20180930
...

スキーマはこんな↓感じを想定

  SELECT
    *
  FROM `sample_*`
  WHERE
    _TABLE_SUFFIX BETWEEN FORMAT_TIMESTAMP("%Y%m%d",BEGINNING_OF_DAY_CURRENT_DATE_SUB(6, 'Asia/Tokyo')) AND FORMAT_TIMESTAMP("%Y%m%d",END_OF_DAY_CURRENT_DATE_SUB(1, 'Asia/Tokyo')) AND
    time BETWEEN UNIX_SECONDS(BEGINNING_OF_DAY_CURRENT_DATE_SUB(6, 'Asia/Tokyo')) AND UNIX_SECONDS(END_OF_DAY_CURRENT_DATE_SUB(6, 'Asia/Tokyo'))