bi系の業務でよく使う日次、週次、月次のレポートを作る際、
bigquery でカレンダーを実テーブルとして持っておくと何かと便利。
日本時間と対応するUTC、週/月の始まりをフィールドとして列挙する。
drop table if exists bi.calendar;
create table bi.calendar as
SELECT
cast(date as datetime) as start_jst,
datetime_sub(datetime_add(cast(date as datetime), interval 1 day), interval 1 microsecond) as end_jst,
timestamp_sub(cast(date as timestamp), interval 9 hour) as start_utc,
timestamp_sub(timestamp_add(timestamp_sub(cast(date as timestamp), interval 9 hour),interval 1 day),interval 1 microsecond) as end_utc,
cast(date as date) as daily,
date_trunc(cast(date as date),week(monday)) as weekly,
date_trunc(cast(date as date),month) as monthkly,
FROM
UNNEST(
GENERATE_DATE_ARRAY(
'1900-01-01',
'2100-12-31',
INTERVAL 1 DAY)
) AS date
;
生成結果
| start_jst | end_jst | start_utc | end_utc | daily | weekly | monthkly |
|---|---|---|---|---|---|---|
| 1900-01-01T00:00:00 | 1900-01-01T23:59:59.999999 | 1899-12-31 15:00:00 UTC | 1900-01-01 14:59:59.999999 UTC | 1900/1/1 | 1900/1/1 | 1900/1/1 |
| 1900-01-02T00:00:00 | 1900-01-02T23:59:59.999999 | 1900-01-01 15:00:00 UTC | 1900-01-02 14:59:59.999999 UTC | 1900/1/2 | 1900/1/1 | 1900/1/1 |
| 1900-01-03T00:00:00 | 1900-01-03T23:59:59.999999 | 1900-01-02 15:00:00 UTC | 1900-01-03 14:59:59.999999 UTC | 1900/1/3 | 1900/1/1 | 1900/1/1 |
...
| start_jst | end_jst | start_utc | end_utc | daily | weekly | monthkly |
|---|---|---|---|---|---|---|
| 2023-10-23T00:00:00 | 2023-10-23T23:59:59.999999 | 2023-10-22 15:00:00 UTC | 2023-10-23 14:59:59.999999 UTC | 2023/10/23 | 2023/10/23 | 2023/10/1 |
| 2023-10-24T00:00:00 | 2023-10-24T23:59:59.999999 | 2023-10-23 15:00:00 UTC | 2023-10-24 14:59:59.999999 UTC | 2023/10/24 | 2023/10/23 | 2023/10/1 |
| 2023-10-25T00:00:00 | 2023-10-25T23:59:59.999999 | 2023-10-24 15:00:00 UTC | 2023-10-25 14:59:59.999999 UTC | 2023/10/25 | 2023/10/23 | 2023/10/1 |
| 2023-10-26T00:00:00 | 2023-10-26T23:59:59.999999 | 2023-10-25 15:00:00 UTC | 2023-10-26 14:59:59.999999 UTC | 2023/10/26 | 2023/10/23 | 2023/10/1 |