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