BigQuery DATE_TRUNCの粒度指定とタイムゾーン：週次集計の開始曜日とパーティションプルーニング

日次の集計が1日ぶんずれる、週次のグラフが日曜始まりで出てくる。BigQueryで時系列を丸めるときの事故は、ほぼDATE_TRUNCの粒度指定とタイムゾーンの2点に集約されます。しかも関数のシグネチャは以前より広がり、DATE_TRUNCひとつでTIMESTAMPの切り捨てと時間帯指定まで完結する形になりました。2026年8月17日時点の公式ドキュメントをもとに、粒度の定義、日本時間へ寄せる書き方、そしてパーティション列に掛けたときのスキャン量まで整理します。

まとめ：DATE_TRUNCは値を粒度の先頭へ丸める関数です

先に結論を置きます。DATE_TRUNCは指定した粒度の「先頭」へ値を丸める関数で、四捨五入でも切り上げでもありません。公式の記述は「結果の値は常に粒度の先頭へ丸められる」で、MONTHなら月初、WEEKならその週の初日、YEARなら元日が返ります。返り値の型は第1引数に渡した型と同じです。

現行のシグネチャは3系統あり、DATE値、DATETIME値、そしてTIMESTAMP値と時間帯を受け取る形が用意されています。TIMESTAMP列を日本時間で日次に丸めたいなら、第3引数に時間帯を渡すだけで済む。ここを知らないまま組むと、UTC基準のまま丸めた結果が9時間ぶん前へずれます。

もうひとつ、コストに直結する話があります。パーティション列にDATE_TRUNCを掛けると全期間を読む、という通説は正しくありません。公式はプルーニング対応の組み込み関数にDATE_TRUNCとTIMESTAMP_TRUNCを明記しており、追加の引数が定数なら読む範囲は絞られます。効かないのはFORMAT_DATEや算術演算のほうでした。

DATE_TRUNCの構文と粒度一覧を型ごとに押さえて書き分ける

まず関数の輪郭を固定します。ここが曖昧だと後段のタイムゾーンの話が組み立てられません。

現行のDATE_TRUNCはDATEとDATETIMEとTIMESTAMPを受ける

公式ドキュメントが示す構文は3つです。DATE値と日付粒度を取る形、DATETIME値と粒度を取る形、そしてTIMESTAMP値と粒度に加えて時間帯を省略可能な第3引数として取る形。関数名がDATE_TRUNCでも、扱えるのはDATE型に限りません。

同じ構造がTIMESTAMP_TRUNCとDATETIME_TRUNCにもあり、それぞれ相手の型も受け取れます。3つの関数はほぼ同じ能力を持ち、名前が違うだけという状態に近づきました。

日付粒度はDAYからISOYEARまでの8種類で丸め先が決まる

日付粒度の定義は公式が1つずつ言葉で示しています。DAYはその値を含むグレゴリオ暦の日、WEEKはその値を含む週の初日、WEEK(WEEKDAY)は指定した曜日から始まる週の初日、ISOWEEKはISO 8601週の初日、MONTHは月の初日、QUARTERは四半期の初日、YEARは年の初日、ISOYEARはISO 8601週番号年の初日。

「その値を含む期間の初日」という共通の言い回しに注目してください。QUARTERは暦年ベースなので、会計年度が4月始まりの会社では社内の第1四半期と一致しません。

製品としての前提から確認したい場合は、BigQueryのサーバーレス構造と採用判断を先に読んでおくと、列指向の保存とパーティションの関係が後半の話につながります。

時刻粒度は5種類あり返り値の型は入力した値の型を引き継ぎます

DATETIMEとTIMESTAMPには時刻粒度も使えます。MICROSECOND、MILLISECOND、SECOND、MINUTE、HOURの5つ。公式はMICROSECONDについて「使っても値からは何も切り捨てられない」と明記しており、それ以外は「指定した単位以下で最も近い、値を超えない境界」へ丸まります。

SELECT
  DATE_TRUNC(DATE '2026-08-17', MONTH)                       AS d_month,
  DATE_TRUNC(DATETIME '2026-08-17 14:35:12', HOUR)           AS dt_hour,
  DATE_TRUNC(TIMESTAMP '2026-08-17 14:35:12+09', DAY, 'Asia/Tokyo') AS ts_day_jst

返り値の型は3つとも入力に揃い、1列目はDATE、2列目はDATETIME、3列目はTIMESTAMPです。TIMESTAMP列を日次で丸めた結果もTIMESTAMPで返り、画面表示ではUTC表記のまま出てくることがある。日付としてレポートに載せたいなら、丸めたあとにDATEへ落とす手順を足します。

週次集計は週の開始曜日とISO週のどちらを採るかで結果が変わる

最も相談が多いのが週次です。日本の業務は月曜始まりが多い一方で既定は日曜始まりなので、指定を忘れると1日ぶん前へずれた週が並びます。

WEEKは日曜始まりでWEEK(MONDAY)と書けば月曜始まりになる

公式の定義は明快でした。WEEKは「週は日曜日に始まる」とされ、WEEKはWEEK(SUNDAY)と等価です。曜日を変えたいときはWEEK(WEEKDAY)の形を使い、WEEKDAYにはSUNDAYからSATURDAYまでの7つのいずれかを入れます。

公式が挙げる例が挙動をそのまま示しています。元の日付が日曜である2017-11-05にWEEK(MONDAY)を指定すると、返るのは直前の月曜である2017-10-30。日曜は「月曜始まりの週」の最終日なので、週の初日は6日前まで戻る計算です。日曜のデータが前の週に集計される理由もここにあります。

SELECT
  DATE_TRUNC(order_date, WEEK)          AS week_sun,
  DATE_TRUNC(order_date, WEEK(MONDAY))  AS week_mon,
  COUNT(*) AS orders
FROM `my_project.sales.orders`
WHERE order_date >= DATE '2026-07-01'
GROUP BY week_sun, week_mon
ORDER BY week_mon

ISOWEEKは月曜固定でISOYEARは年またぎの境界がずれる

ISOWEEKも月曜始まりですが、WEEK(MONDAY)と同じものではありません。公式はISO週を「月曜に始まる。各ISO年の最初のISO週は、対応するグレゴリオ暦年の最初の木曜日を含む」と定義しています。年初の数日が前年のISO週へ属するのはこの規則によります。

年またぎの挙動を示す公式の例が分かりやすい。2015-06-15にISOYEARを指定すると返るのは2014-12-29です。2015年の最初の木曜日が2015-01-01だったため、ISO年2015はその直前の月曜から始まる。同じ日付にEXTRACT(ISOYEAR)を掛けると2015が返るので、丸めた日付と年番号が食い違うわけです。

ISO週で回っている海外の基幹システムや生産計画と揃えたいならISOWEEKとISOYEARを選びます。社内レポートで単に月曜始まりの7日間が欲しいだけなら、WEEK(MONDAY)のほうが年またぎの説明コストが小さい。用途で選び分けるのが実務的でした。

社内の締め日から週の開始曜日を1つ決めて全クエリで固定する手順

ここは判断を言い切ります。週の開始曜日はクエリごとに選ぶものではなく、組織で1つ決めて固定するものです。開始曜日が違う2本のクエリから出た数字は絶対に一致せず、突合の工数だけが増えます。

順序も定型化できました。売上締めや勤怠の週次締めがあるなら締め日の翌日を開始曜日にし、締めがないなら月曜始まりを既定にする。決めた曜日はビューかUDFに固めます。

CREATE OR REPLACE VIEW `my_project.mart.v_orders_weekly` AS
SELECT
  DATE_TRUNC(order_date, WEEK(MONDAY)) AS week_start,
  COUNT(*)      AS orders,
  SUM(amount)   AS amount
FROM `my_project.sales.orders`
GROUP BY week_start

TIMESTAMPの既定はUTCなので日本時間の日次集計は9時間ずれる

次がタイムゾーンです。日次の件数が合わない、深夜の注文が前日に入る症状はここが原因でした。

time_zone引数を渡すと切り捨ての基準が指定の地域時刻へ移る

公式は第3引数について「TIMESTAMP値と一緒に使う時間帯。既定の時間帯であるUTC以外を切り捨て操作の一部として使いたい場合にこの引数を使う」と説明しています。既定はUTCで、明示しない限りUTCの暦日で丸まる仕組みです。

公式の例がずれ方をそのまま示します。TIMESTAMP “2008-12-25 15:30:00+00” をDAYで丸めたとき、UTC指定では 2008-12-25 00:00:00 UTC が返り、America/Los_Angeles 指定では 2008-12-25 08:00:00 UTC が返る。後者はロサンゼルス時間の0時をUTCで表示した結果です。日本時間なら同じ理屈で、JSTの0時はUTCの前日15時にあたります。

つまり時間帯を指定しない日次集計は、日本時間の朝9時を境に日付が変わる集計になっている。深夜0時から朝9時までの9時間ぶんが前日側へ寄る形です。夜間にトラフィックが立つサービスほど食い違いが目立ちます。

SELECT
  DATE(TIMESTAMP_TRUNC(created_at, DAY, 'Asia/Tokyo'), 'Asia/Tokyo') AS d_jst,
  COUNT(*) AS events
FROM `my_project.app.events`
WHERE created_at >= TIMESTAMP '2026-08-01 00:00:00+09'
  AND created_at <  TIMESTAMP '2026-09-01 00:00:00+09'
GROUP BY d_jst
ORDER BY d_jst

DATETIME型はタイムゾーンを持たないため変換の位置で結果が変わる

型の性質も押さえておきます。TIMESTAMPは時点を表し内部的にはUTCで保持され、DATETIMEは時間帯の情報を持たない「壁掛け時計の値」です。DATETIMEに切り捨てを掛けても時間帯の引数は取れず、解釈の余地がないので取る必要もありません。

問題になるのは変換の順序でした。TIMESTAMP列を日本時間のDATETIMEへ直してから丸める書き方と、TIMESTAMPのまま時間帯付きで丸めてから日付へ落とす書き方は、どちらも同じ日付に到達します。ところが、UTC基準で日次に丸めた結果を後から日本時間へ表示し直すと、日付の境目はUTCのまま動きません。

チェックの観点は1つに絞れます。時間帯の指定は必ず「丸める瞬間」に効かせること。CURRENT_DATEも同じで、引数を省略するとUTCの今日が返るため、日本時間の今日が欲しいなら CURRENT_DATE(‘Asia/Tokyo’) と書きます。

時刻粒度の切り捨ては夏時間の切り替え付近で直感と食い違います

公式には注記も添えられていました。MINUTEまたはHOURへ切り捨てるとき、この関数は指定された（または既定の）時間帯における暦上の時刻を求め、そこから分と秒（HOURの場合）または秒（MINUTEの場合）を差し引きます。多くの場合は直感どおりの結果になるものの、時間単位で揃っていない夏時間の切り替え付近では直感と異なる結果になる、と明記されています。

日本標準時には夏時間がないため、Asia/Tokyo を指定する限りこの注記は効きません。効くのは海外拠点のログを地域時刻で時間別集計する場合だけです。

方針は先に決めておいてください。複数地域を横並びで比較したいなら丸めはUTCで行い、読み替えは表示側に寄せる。地域の生活時間に沿った時間別が欲しいなら、切り替え日の本数が23時間または25時間になる前提で扱います。

パーティション列にDATE_TRUNCを掛けてもプルーニングは効く

ここが本記事の主眼です。スキャン量に直結し、通説を信じるとコストの判断を誤ります。

公式が挙げるプルーニング対応関数にDATE_TRUNCが含まれている

公式はパーティションプルーニングの条件を「フィルタ条件は、BigQueryがテーブルデータを読まずにスキャン対象のパーティションを判定できる構造でなければならない」と説明したうえで、そのために「比較演算子の片側にパーティション列を単独で置くか、サポートされた組み込み関数だけで列を包む」と示しています。

そして対応関数の一覧が明記されています。追加の引数が定数であることを条件に、DATE_ADD、DATE_DIFF、DATE_SUB、DATE_TRUNC、YEAR部分のEXTRACT、DATETIME_DIFF、TIMESTAMP_ADD、TIMESTAMP_DIFF、TIMESTAMP_SUB、TIMESTAMP_TRUNC、DATE部分またはYEAR部分のEXTRACT、そして書式指定子が %F ・ %Y-%m-%d ・ %Y%m%d のFORMAT_TIMESTAMP。それ以外の関数と複雑な数式はフルスキャンになる、という線引きです。

プルーニングの効く例には、月単位で丸めた範囲比較がそのまま載っています。切り捨てを掛けた瞬間に全期間を読む、という理解は現行の仕様とは合いません。

SELECT COUNT(*)
FROM `bigquery-public-data.wikipedia.pageviews_2025`
WHERE TIMESTAMP_TRUNC(datehour, MONTH) >= TIMESTAMP '2025-04-01'
  AND TIMESTAMP_TRUNC(datehour, MONTH) <  TIMESTAMP '2025-07-01'

FORMAT_DATEと算術演算と動的な値はプルーニングを止めてしまう

効かない側も具体的です。公式はFORMAT_DATEによる書式化の比較、MONTH部分とHOUR部分のEXTRACTを組み合わせた条件、そして列に INTERVAL を足す算術演算をいずれも「パーティションを絞らない」例として挙げています。判定にテーブルの中身が要る形になるためです。

さらに、定数でない値との比較も止まります。サブクエリで別テーブルから引いた時刻との比較、非定数の別列との比較。いずれも動的な値なので、実行前にパーティションを選べません。ANDをORへ変えた場合も、条件に合わない区画に該当行が残りうるため絞り込みは働かなくなります。

書き換えの方針は「列を裸にする」の一語です。書式化して文字列比較するのではなく、境界のタイムスタンプで以上と未満の範囲比較にする。算術演算は比較の反対側へ移す。課金がスキャン量で決まる仕組みそのものは、BigQueryの料金とコスト監視の実装で扱っています。

パーティション境界はUTC基準なので日本時間の日次とは境目が動く

タイムゾーンの話がここで再び効いてきます。公式は時間単位列パーティション分割について「パーティションの境界はUTC時刻に基づく」と明記しており、取り込み時間パーティションでも同じ記述があります。_PARTITIONTIME はUTCの取り込み時刻をパーティション境界へ丸めた値です。

結果として、日本時間の8月17日ぶんのデータはUTC基準では8月16日と8月17日の2つのパーティションにまたがります。単一のパーティション日付だけを条件に書くと、朝9時より前の9時間が抜け落ちる。範囲は2日ぶんを含む形で書き、集計側の丸めに時間帯を指定するのが安全でした。

SELECT
  DATE(created_at, 'Asia/Tokyo') AS d_jst,
  COUNT(*) AS events
FROM `my_project.app.events`
WHERE created_at >= TIMESTAMP '2026-08-17 00:00:00+09'
  AND created_at <  TIMESTAMP '2026-08-18 00:00:00+09'
GROUP BY d_jst

粒度の選択肢も押さえておきます。TIMESTAMP列とDATETIME列は時間・日・月・年の単位で分割でき、DATE列は日・月・年の単位です。分割列がNULLの行は __NULL__ へ、1960-01-01より前または2159-12-31より後の値は __UNPARTITIONED__ へ入ります。

ドライランの推定バイト数でプルーニングの有無を実測して確かめる

判断は仕様の読み合わせで終わらせず、必ず実測してください。公式も「特定のクエリでプルーニングがサポートされるかはドライランで検証できる」と案内しています。書き換えの前後で推定バイト数を比べるだけで、費用もかかりません。

bq query --dry_run --use_legacy_sql=false \
  'SELECT COUNT(*) FROM `my_project.app.events`
   WHERE TIMESTAMP_TRUNC(created_at, DAY) = TIMESTAMP "2026-08-17"'

推定バイト数がテーブル全体の規模と同じなら絞られていない、対象期間ぶんに縮んでいれば絞られている、という単純な読み方で足ります。コンソールのエディタで右上に出る推定処理量でも同じ判定ができる。bqコマンドの導入から確認したい場合は、BigQueryの使い方と初回のつまずきどころが入口です。

DATE_TRUNCを使う場面と使うべきでない場面を条件で切り分ける

最後に判断を条件の形へ落とします。どれが優れているかではなく、どの条件でどれを選ぶかの整理です。

EXTRACTやFORMAT_DATEとの棲み分けは並べ替えの可否で決まる

DATE_TRUNCを選ぶ条件は2つです。丸めた結果を時系列として並べたいとき、返り値が日付や時点のままなのでORDER BYで正しい順序に並びます。もう1つはパーティション列に条件を掛けたいときで、前述のとおり対応関数に入っているためです。

逆にEXTRACTが向くのは、時系列を壊して周期で束ねたいときでした。曜日別の平均、時間帯別の分布、月別の季節性といった集計は、年をまたいで同じ値へ寄せたいのでEXTRACTを使います。FORMAT_DATEやFORMAT_TIMESTAMPは表示用の文字列を作る用途に限る。並べたいならDATE_TRUNC、束ねたいならEXTRACT、見せたいだけならFORMAT系という3分類で迷う場面はほぼ消えました。

日本時間で日次を出す書き方を1つに固めて全社で使い回す運用手順

運用の側もここで決め切ります。日本時間の日次を出す書き方は、社内で1つに固定してください。候補は2つあり、どちらでも結果は一致します。TIMESTAMP列にDATE(created_at, ‘Asia/Tokyo’) を掛けてDATEへ落とす形か、TIMESTAMP_TRUNC(created_at, DAY, ‘Asia/Tokyo’) で丸めてからDATEへ変換する形。

前者は短く、日次にしか使えません。後者は粒度を差し替えれば時間別にも週別にも展開でき、雛形として配りやすい形です。

GA4のエクスポートを扱う場合は前段の型変換も型として持っておきます。event_timestamp はUTCのマイクロ秒を持つ整数なので、TIMESTAMP_MICROS を通してTIMESTAMPへ直してから日本時間で丸める順序です。ネストした配列の取り出し側はBigQuery UNNESTの書き方とGA4スキーマの展開にまとめました。収集から集計までの構成をまとめて設計したい場合は、データ分析基盤構築・MLOps構築支援で要件整理から実装まで請け負っています。

外部へ依頼するときに先に渡す3点は列の型と基準時間帯と粒度です

設計や実装を外部に委託するなら、着手前に渡す情報を3点に絞れます。第一に、時系列列の型がDATEかDATETIMEかTIMESTAMPか。第二に、日付の基準時間帯を日本時間とUTCのどちらに置くか。第三に、レポートで要る最小粒度が日次か時間別かです。

この3点が決まれば、パーティションの粒度、丸めの書き方、境界のずれの扱いが自動的に決まります。曖昧なまま着手すると、後から基準時間帯を変える改修が全クエリと全ビューに波及する。時系列の基準は、途中で変えるコストが最も高い設計判断のひとつでした。丸めた時系列で前の行と値を比べ、変化した行だけを取り出す書き方はBigQuery QUALIFYの使い方とLAGでの差分抽出にまとめました。

見積書では、丸めの基準時間帯を誰が決めるか、週の開始曜日の定義がどこに書かれるか、プルーニングをドライランで検証する工程が入っているかを確認します。

よくある質問

DATE_TRUNCとTIMESTAMP_TRUNCはどちらを使うべきですか？

現行の仕様ではどちらでも同じ結果に到達します。DATE_TRUNCはDATE値、DATETIME値、そしてTIMESTAMP値と時間帯の3系統を受け取り、TIMESTAMP_TRUNCもTIMESTAMP値と時間帯、またはDATETIME値を受け取るためです。能力の差ではなく読みやすさで選ぶ問題なので、列の型と関数名を対応させる書き方を勧めます。

週次集計が月曜始まりになりません。原因は何ですか？

粒度をWEEKのまま書いているためです。公式の定義では週は日曜に始まり、WEEKはWEEK(SUNDAY)と等価とされています。月曜始まりにするならWEEK(MONDAY)と指定してください。ISOWEEKでも月曜始まりになりますが、年またぎでISO年の境界が前年へ寄るため、社内レポート用途ならWEEK(MONDAY)のほうが説明が簡単です。

日本時間で日次集計すると件数が合わないのはなぜですか？

時間帯を指定していないと、既定のUTCで日付が切られるためです。日本時間の0時から朝9時までの9時間ぶんが前日側へ寄ります。TIMESTAMP_TRUNCまたはDATE_TRUNCの第3引数に ‘Asia/Tokyo’ を渡すか、DATE関数の第2引数で時間帯を指定してください。CURRENT_DATEも引数を省略するとUTCの今日を返す点は同じです。

パーティション列にDATE_TRUNCを書くとスキャン量は増えますか？

増えません。公式はプルーニングをサポートする組み込み関数の一覧にDATE_TRUNCとTIMESTAMP_TRUNCを含めており、追加の引数が定数であればパーティションは絞られます。実際に絞られたかどうかはドライランの推定バイト数で確認できる。スキャン量が増えるのはFORMAT_DATEでの書式化、列に対する算術演算、動的な値との比較のほうです。

四半期の集計が会社の第1四半期と合わないのはなぜですか？

QUARTERは暦年ベースだからです。その値を含む四半期の初日へ丸まるため、1月1日、4月1日、7月1日、10月1日のいずれかが返ります。4月始まりの会計年度に合わせるなら、日付を3か月ずらしてから丸めるか、丸めた結果を会計四半期のラベルへ変換してください。ずらす処理をパーティション列に直接掛けると絞り込みが効かなくなるため、変換はグループ化の対象列に限ります。

関連記事

• BigQueryとは：サーバーレスDWHの仕組みと採用判断：パーティションと列指向の前提
• BigQueryの使い方：コンソールとbqコマンドの初回操作：ドライランを実行するまでの手順
• BigQueryの料金：オンデマンドとEditionsの分岐点：スキャン量が課金へ効く仕組み
• BigQuery UNNESTの書き方とGA4スキーマの展開：配列とネスト列の取り出し
• BigQuery無料枠の内訳とサンドボックスの制限：検証環境を無料で回す設計
• BigQuery APIの選び方とリトライ設計：集計結果を外へ渡す経路
• BigQuery Data Transfer Serviceの転送設計：時系列データを取り込む経路
• データ分析基盤の構築：5層アーキテクチャと実装手順：収集から可視化までの全体像
• BigQuery導入の手順と体制・外注の判断：PoCから本番移行までの進め方