タイムゾーン地獄：UTC vs JST、日付の境目で消える1時間

「23時の購入が、ダッシュボードでは翌日になってる」── タイムゾーンが原因。サーバ UTC、アプリ JST、BI（Business Intelligence） 自動変換、と各層が違う tz で動いていると、1時間ズレが滑り込む。国際展開や夜間集計のあるサービスで必ず遭遇。

起きる仕組み

• サーバが UTC、ユーザが JST：23時 JST = 14時 UTC。日付 truncate で別の日に分類
• DB のデータ型：`TIMESTAMP WITHOUT TIME ZONE` と `TIMESTAMP WITH TIME ZONE` の混在
• アプリが文字列で渡す：`'2026-04-01 23:00'` の解釈がサーバ依存
• BI ツールの自動変換：Looker Studio / Tableau がブラウザのタイムゾーンに自動変換
• 夏時間（DST）：米国データを扱うと、3月・11月で1時間スキップ/重複
• 過去のデータ：1999年以前など、サマータイム制度の歴史的変更

調査手順

-- 同じ取引を UTC・JST 両方で見るSELECT  order_id,  ordered_at_utc,  DATETIME(ordered_at_utc, 'Asia/Tokyo') AS ordered_at_jst,  DATE(ordered_at_utc) AS date_utc,  DATE(ordered_at_utc, 'Asia/Tokyo') AS date_jstFROM analytics.ordersWHERE ordered_at_utc BETWEEN '2026-04-01 14:00:00 UTC' AND '2026-04-01 15:00:00 UTC'LIMIT 20;

• サーバの日付：`SELECT NOW(), CURRENT_TIMESTAMP, CURRENT_SETTING('timezone')` で確認
• アプリのログ：実際の `created_at` が JST/UTC どっちで保存されているか
• BI 設定：Looker Studio は user timezone・data timezone の二重設定。ズレる
• 境界時刻の集計：23:00〜01:00 JST のデータで日付列がどう振り分けられているか

短期対処

• SQL（Structured Query Language） でタイムゾーン明示：`DATE(timestamp, 'Asia/Tokyo')` のように毎回明示
• BI のフィルタ条件にも適用：「2026-04-01 全日」を選ぶ際に tz が一致しているか
• ダッシュボードのキャプションに明記：「JST集計」と書く

中長期対策（鉄則）

• 保存は UTC、表示で変換（業界標準）
• `TIMESTAMP WITH TIME ZONE` を使う（PG / Snowflake で標準）
• ISO 8601 文字列：`2026-04-01T23:00:00+09:00` で送受信、文字列なら誤解釈なし
• 夏時間ありの地域は IANA tz：`'America/New_York'` のように地域指定（`EST` などの3文字略称は曖昧）
• dbt メトリクス層で固定：`date_trunc('day', timestamp_column, 'Asia/Tokyo')` を1箇所に
• BI 設定の標準化：Looker / Tableau の datasource 単位で tz を強制
• 境界条件のテスト：23:30, 00:30, 02:30 (DST 切替) でユニットテスト

ふくふくの進め方

タイムゾーン整理は1〜2週間で：① 全テーブルのカラム型監査、② UTC 統一に向けたマイグレーション計画、③ dbt（data build tool） の date_trunc を1箇所に集約。「23時問題」が再現しなくなったら成功。国際展開している案件で特によく対応します。