ブリティッシュコロンビア州、タイムゾーン、そしてPostgres

Christopher Winslett 2026年6月16日・6分読了 この著者による最新記事 ブリティッシュコロンビア州、タイムゾーン、そしてPostgres PostgresシリアルはBIGINTであるべき（そして移行方法） Postgres 18のデータチェックサムの新しいデフォルトとアップグレードへの対処法 PostGISパフォーマンス：簡素化 EXPLAINプランにおけるPostgresのスキャンタイプ 本番Postgres ブリティッシュコロンビア州、タイムゾーン、そしてPostgres Christopher Winslett 2026年6月16日・6分読了・この著者による最新記事 2026年3月8日、ブリティッシュコロンビア州は時計を年間を通して太平洋夏時間に変更しました。3月に時計を1時間進めてUTC-7にしましたが、11月にはUTC-8に戻しません。今後、アメリカ/バンクーバータイムゾーンのUTCオフセットは恒久的にUTC-7となります。 これを機に、日付とタイムゾーンの保存について話しましょう。最も基本的な例では、デフォルトはUTC値を保存し、UTCからの相対的なローカル時刻を計算することです。しかし、カレンダーシステムを使用する人々はローカル時刻（つまり壁時計時間）で物事を考え、UTCを考慮することはありません。タイムゾーンデータを変更した後、ある地域でのUTCからのこれらの時刻計算は、ユーザーの入力値と異なることになります。 2026年以降のブリティッシュコロンビア州での予定をUTCベースの列にタイムスタンプとして保存した場合、11月から3月までの予定が1時間ずれる可能性があります！ `timestamptz`列はローカル時刻を保存しないことに注意してください。それらはUTC時刻を保存し、タイムゾーンは挿入時とクエリ時にUTCとの変換にのみ使用されます。将来の予定をアメリカ/バンクーバータイムゾーンの`timestamptz`として保存した場合、保存時のルールを使用してUTCに変換されました。その予定を後でクエリすると、現在のルールを使用してローカル時刻に変換されます。保存時とクエリ時のルールが変更された場合、取得されるローカル時刻はユーザーが元々意図したものとは異なります。 `tzdata`パッケージを更新していない場合、Postgresはその変更を知らず、古いルールを使用して変換を続けます。Ubuntuの`tzdata`パッケージはどのくらいの頻度で更新されるのでしょうか？驚くべきことに、数ヶ月ごとです。 もしあなたの列が`timestamptz`型で保存されており、ブリティッシュコロンビア州の顧客と取引がある場合、`tzdata`パッケージが更新されたかどうかを確認するために以下のSQLクエリを使用してください。 ```sql SELECT to_char( '2026-12-01 10:00:00'::timestamp AT TIME ZONE 'America/Vancouver', 'HH24:MI:SS OF' ) AS november_2026_vancouver_offset; ``` 値が`17:00:00 +00`であれば、`tzdata`は更新されています。しかし、これは思ったほど良いことではありません。将来の予定がタイムゾーン調整の前後に作成されたかどうかを知るために、ログを深く掘り下げる必要があるからです。 値が`18:00:00 +00`であれば、朗報です！`tzdata`は更新されておらず、更新によってデータが分割されることはありませんでした。 ### タイムゾーンシフトの例 今年の初め、あるユーザーが2026年11月10日のバンクーバーでの午前10時の予約をしました。これを`timestamptz`として保存します。 ```sql INSERT INTO appointments (patient_id, starts_at) VALUES (42, '2026-11-10T10:00:00-08:00'); -- stored as: 2026-11-10 18:00:00+00 (UTC) ``` 2026年4月に、新しいタイムゾーンルールを適用するための`tzdata`アップデートがリリースされます。 2026年11月10日、患者はカレンダーに記載されている通りローカル時間の午前10時に現れます。しかし、予約をクエリすると、ローカル時間の午前11時と表示されます。 ```sql SELECT starts_at AT TIME ZONE 'America/Vancouver' AS local_time FROM appointments WHERE patient_id = 42; -- returns: 2026-11-10 11:00:00 ``` 元々入力された時間よりも1時間遅く計算されていることに注目してください。 ### タイムゾーンの変更に耐えるスキーマ：デュアルカラムパターン その名前が示す通り、デュアルカラムパターンはデータを2つのカラム（実際には3つ）に保存します。 * ローカルタイムスタンプ * ローカルタイムゾーン * UTCタイムスタンプ UTCタイムスタンプカラムは計算されたカラムであるべきです。タイムスタンプとタイムゾーンを使用してUTCを計算します。その計算されたUTC値も保存およびクエリされ、バックグラウンドジョブが通知を送信したり、予約の衝突などの制約チェックを簡素化したりできるようにします。 デュアルカラムパターンは、ローカルな意図が権威である場合に必要です。特定の時間と場所の人々や配達、法的な期限、カレンダーイベントなどです。 ただし、やりすぎないでください。イベントが過去のものである場合や、正確なUTC瞬間が権威である場合（ログエントリ、金融取引、センサー読み取りなど）は、プレーンな`timestamptz`を使用してください。デュアルカラムパターンは、将来のローカルな意図を保持する必要がある場合にのみ、そのコストと複雑さを支払う価値があります。 詳細なスキーマは次のようになります。 ```sql CREATE TABLE appointments ( id bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY, local_time timestamp NOT NULL, -- wall clock value timezone_name text NOT NULL, -- IANA name: 'America/Vancouver' starts_at_utc timestamptz NOT NULL -- Calculated via trigger ... ); ``` `local_time`と`timezone_name`は、「ユーザーは何を意図していましたか？」という問いに答えるために、壁カレンダー/壁時計の値/壁時計の場所を保存します。これらの値は、ユーザーの要求があった場合にのみ変更されるべきです。これらは`starts_at_utc`を計算するために使用されます。 `starts_at_utc`は、インデックスを付け、クエリし、制約に使用できるカラムです。これは「この予約は現時点でどのUTC瞬間に対応しますか？」という問いに答えます。計算され保存されたUTC値を持つことで、現在UTC値を使用しているのと同様に、UTC値の使用が簡素化されるはずです。 `starts_at_utc`を計算する方法はいくつかあり、アプリケーションまたはデータベースを使用できます。計算されたUTCカラムは生成列の良い例となるでしょうが、Postgresはタイムゾーンルールが変更されるため`timestamptz`が不変と分類されないため、生成列に`timestamp with time zone`型を許可しません。したがって、挿入および更新時に`starts_at_utc`を計算するためにトリガーを使用します。 ```sql CREATE OR REPLACE FUNCTION recompute_appointment_utc() RETURNS TRIGGER AS $$ BEGIN NEW.starts_at_utc := NEW.local_time AT TIME ZONE NEW.timezone_name; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER ts_recompute_starts_at_utc BEFORE INSERT OR UPDATE ON appointments FOR EACH ROW EXECUTE FUNCTION recompute_appointment_utc(); ``` ### デュアルカラムでのタイムゾーン変更 `tzdata`の更新がタイムゾーンのルールを変更すると、データベース内の導出された`starts_at_utc`値は古くなり、再計算する必要があります。これは、変換ロジックを再適用する簡単なUPDATE文で実行できます。 ```sql UPDATE appointments SET starts_at_utc = local_time AT TIME ZONE timezone_name WHERE timezone_name = 'America/Vancouver' AND starts_at_utc > now(); ``` ### RFC 9557について 2024年に、RFC 9557が`1996-12-19T16:39:57-08:00[America/Los_Angeles]`のような新しいタイムスタンプ形式としてリリースされました。2025年11月にpgsql-generalフォーラムで短い議論が行われました。標準がまだ非常に新しく、人々がその採用状況を見守っているため、使用は進んでいません。 しかし、RFC 9557は明示的に以下の問題を解決することを意図していませんでした。 > 特定のタイムゾーンでのローカル時刻として与えられた将来の時刻。そのタイムゾーンの定義の変更（夏時間の制定または廃止などの政治的決定）が、タイムスタンプが表す瞬間に影響を与える場合。 したがって、十分に将来の現実世界の時刻については、デュアルカラムパターンを堅持してください。 ### `tzdata`がすでに更新されている場合の対処法 新しいタイムゾーンのために`tzdata`パッケージをすでに更新しており、列の値に不明なUTCシフトが割り当てられており、データベースがブリティッシュコロンビア州の将来の時刻を記録している場合、あなたはデータプロジェクトに取り組むことになります。理想的には、以下のことを行います。 * `tzdata`パッケージがいつ更新されたかを見つけるか推定する * 潜在的に誤ったすべてのレコードを見つける * `tzdata`更新後の`updated_at`タイムスタンプを使用して、潜在的に影響を受ける行を特定する * タイムシフト調整をユーザーに通知する計画を立てる（オプトアウトまたはオプトインの可能性も含む） * 非本番データセットで、潜在的に影響を受ける行に対してタイムシフト移行をテストする * バックアップを実行し、本番環境でタイムシフト移行を実行する * 変更の影響を受けるカレンダー項目にUI要素を追加する 今や廃止された11月の時間変更が近づいたら、タイムゾーンの問題が発生する可能性をユーザーに再度通知します。 人口580万人のブリティッシュコロンビア州の変更は