ブリティッシュコロンビア州、タイムゾーン、そしてPostgres
British Columbia, Time Zones, and Postgres (crunchydata.com)
要約
ブリティッシュコロンビア州が2026年3月8日から年間を通じて太平洋夏時間(UTC-7)に移行したことで、Postgresで将来のローカル時刻を扱う際の課題が生じています。特に、`timestamptz`型でUTCベースの時刻を保存すると、`tzdata`の更新や時間帯ルールの変更により、ユーザーが意図したローカル時刻とデータベースから取得される時刻がずれる可能性があります。この問題に対処するため、記事では「デュアルカラムパターン」を推奨し、ローカル時刻、タイムゾーン名、そして計算されたUTC時刻を個別に保存して、将来のローカルな意図を正確に保持する方法を提案しています。
全文翻訳
ブリティッシュコロンビア州、タイムゾーン、そしてPostgres
2026年3月8日、ブリティッシュコロンビア州は時計を通年太平洋夏時間(Pacific Daylight Savings Time)に移行しました。3月には時計を1時間進めUTC-7になりましたが、11月にはUTC-8に戻ることはありません。今後、America/Vancouver タイムゾーンのUTCオフセットは永続的にUTC-7となります。
これを機に、日付とタイムゾーンの格納についてお話ししましょう。最も基本的な例では、デフォルトでUTC値を格納し、ローカルタイムをUTCからの相対値として計算します。しかし、カレンダーシステムを使用する人々はローカルタイム(つまり、時計の時刻)で考え、UTCを考慮しません。タイムゾーンデータを変更した後、ある地域に対するUTCからのこれらの時間計算は、ユーザーの入力値と異なるものになります。
ブリティッシュコロンビア州を拠点とする2026年以降の予約について、UTCベースのタイムスタンプ列にタイムスタンプを格納していた場合、11月から3月までの予約が1時間ずれる可能性があります!
[画像: 計算の変化を示す図]
timestamptz 列はローカルタイムを格納するのではなく、UTC時刻を格納し、タイムゾーンは挿入時およびクエリ時のUTCとの変換にのみ使用されることに注意してください。もしAmerica/Vancouver タイムゾーンで将来の予約を timestamptz として格納した場合、それは格納時のルールを使用してUTCに変換されました。後でその予約をクエリすると、現在のルールを使用してローカルタイムに変換されます。格納時からクエリ時までにルールが変更された場合、返されるローカルタイムはユーザーが元々意図したものとは異なります。
tzdata パッケージを更新していない場合、Postgres はその変更を知らないため、古いルールを使用して変換を続行します。Ubuntu の tzdata パッケージはどれくらいの頻度で更新されるのでしょうか?驚くべきことに、数ヶ月に一度です。
列が timestamptz 列タイプに格納されており、ブリティッシュコロンビア州の顧客とやり取りしている場合は、tzdata パッケージが更新されたかどうかを確認するために、次の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 として格納します。
INSERT INTO appointments (patient_id, starts_at)
VALUES (42, '2026-11-10T10:00:00-08:00');
-- 格納される値: 2026-11-10 18:00:00+00 (UTC)
2026年4月、新しいタイムゾーンルールを適用するための tzdata アップデートがリリースされました。
2026年11月10日、患者はカレンダーに記録された通り、ローカルタイムの午前10時に現れます。しかし、予約をクエリすると、ローカルタイムは午前11時と表示されます。
SELECT starts_at AT TIME ZONE 'America/Vancouver' AS local_time
FROM appointments
WHERE patient_id = 42;
-- 返される値: 2026-11-10 11:00:00
元の入力よりも1時間遅く計算されていることに注意してください。
タイムゾーン変更に強いスキーマ:デュアルカラムパターン
その名前が示すように、デュアルカラムパターンはデータを2つの列(実際には3つ)に格納します。
- ローカルタイムスタンプ
- ローカルタイムゾーン
- UTCタイムスタンプ
UTCタイムスタンプ列は計算列であるべきです。タイムスタンプとタイムゾーンを使用してUTCを計算します。その計算されたUTC値は、バックグラウンドジョブが通知を送信したり、予約の衝突のような制約チェックを簡素化したりするために格納およびクエリされます。
デュアルカラムパターンは、ローカルな意図が権威を持つ場合に必要です。つまり、特定の時間と場所での人々や配達、法的期限、カレンダーイベントなどです。
ただし、やりすぎる必要はありません。イベントが過去のものである場合、または正確なUTCの瞬間が権威を持つ場合(ログエントリ、金融取引、センサーの読み取り値など)は、通常の timestamptz を使用してください。デュアルカラムパターンは、将来のローカルな意図を保持する必要がある場合にのみ、コストと複雑さに見合う価値があります。
詳細なスキーマは次のようになります。
CREATE TABLE appointments (
id bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
local_time timestamp NOT NULL, -- 時計の壁の値
timezone_name text NOT NULL, -- IANA名: 'America/Vancouver'
starts_at_utc timestamptz NOT NULL -- トリガー経由で計算
...
);
local_time と timezone_name は一緒に、「ユーザーは何を意図したのか?」に答えます。これらは、壁掛けカレンダー/壁掛け時計の値/壁掛け時計の場所を格納します。これらの値は、ユーザーの要求によってのみ変更されるべきです。これらを使用して starts_at_utc を計算します。
starts_at_utc は、インデックス作成、クエリ、および制約に使用できる列です。これは、「この予約は現在、どのUTCの瞬間に対応しているか?」に答えます。計算され格納されたUTC値を持つことで、現在行っているようにUTC値を簡単に使用できるようになります。
starts_at_utc を計算する方法はいくつかあります。アプリケーションを使用するか、データベースを使用します。計算されたUTC列は生成列の素晴らしい例になりますが、Postgres はタイムゾーンルールが変更されるため timestamptz が不変(immutable)と分類されないため、タイムゾーン付きタイムスタンプ型の生成列を許可していません。そのため、挿入および更新時に starts_at を計算するためにトリガーを使用します。
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 ステートメントで実行できます。
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万人の人口を持つブリティッシュコロンビア州がタイムゾーンの好みを変更すると、一部のデータセットには広範囲に影響を与え、他のデータセットには全く影響を与えないでしょう。tzdata パッケージは驚くほど頻繁に更新されるため、タイムゾーンの変更に見落とされないようにしてください。