[解決済み] PostgreSQLデータベースでは、どのタイムスタンプタイプを選択すればよいですか？

質問

マルチタイムゾーンプロジェクトのコンテキストで、Postgresデータベースにタイムスタンプを格納するためのベストプラクティスを定義したいと思います。

私は

1. 選ぶ TIMESTAMP WITHOUT TIME ZONE そして、このフィールドの挿入時にどのタイムゾーンが使用されたかを記憶します。
2. 選択する TIMESTAMP WITHOUT TIME ZONE を選択し、挿入時に使用されたタイムゾーンの名前を含む別のフィールドを追加します。
3. を選択します。 TIMESTAMP WITH TIME ZONE を選択し、それに応じてタイムスタンプを挿入します。

私はオプション 3 (タイムゾーン付きのタイムスタンプ) を少し好みますが、この件に関する教育的な意見を持ちたいと思います。

どのように解決するのですか?

まず最初に、PostgreSQLの時間処理と演算は素晴らしいもので、オプション3は一般的なケースでは問題ないでしょう。しかし、これは時間とタイムゾーンの不完全なビューであり、補完することができます。

1. ユーザーのタイムゾーンの名前をユーザー設定として保存する (例. America/Los_Angeles ではなく -0700 ).
2. ユーザーのイベント/時間データを、ユーザーの参照フレームにローカルに送信させる (ほとんどの場合、UTC からのオフセットで、たとえば -0700 ).
3. アプリケーションでは、時間を変換して UTC を使って保存し TIMESTAMP WITH TIME ZONE カラムを使用して保存されます。
4. ユーザーのタイムゾーンにローカルな時間要求を返す (すなわち、以下のように変換する) UTC から America/Los_Angeles ).
5. データベースの timezone を UTC .

このオプションは、ユーザーのタイムゾーンを取得するのが難しいため、常に機能するわけではありません。 TIMESTAMP WITH TIME ZONE を使用するようにというアドバイスがあります。とはいえ、このオプション 4 の背景的な側面をもう少し詳しく説明しましょう。

オプション3と同様に、その理由は WITH TIME ZONE は、何かが起こった時刻が 絶対 の瞬間だからです。 WITHOUT TIME ZONE が得られます。 相対的 となります。絶対と相対のTIMESTAMPを混在させないでください。

プログラム上および一貫性の観点から、すべての計算がUTCを時間帯として使用して行われることを確実にしてください。 これはPostgreSQLの要件ではありませんが、他のプログラミング言語や環境と連携する際に役立ちます。このため CHECK を設定することで、タイムスタンプカラムへの書き込みがタイムゾーンのオフセットが 0 は、いくつかの種類のバグ（例えば、スクリプトがデータをファイルにダンプし、他の何かがレキシカルソートを使用して時刻データをソートする）を防止するための防御的な位置づけです。 繰り返しますが、PostgreSQLは日付計算や時間帯間の変換を正しく行うためにこれを必要としません（つまり、PostgreSQLは任意の2つの時間帯間の時刻変換に非常に長けています）。データベースに入るデータがゼロのオフセットで保存されるようにするためです。

CREATE TABLE my_tbl (
  my_timestamp TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
  CHECK(EXTRACT(TIMEZONE FROM my_timestamp) = '0')
);
test=> SET timezone = 'America/Los_Angeles';
SET
test=> INSERT INTO my_tbl (my_timestamp) VALUES (NOW());
ERROR:  new row for relation "my_tbl" violates check constraint "my_tbl_my_timestamp_check"
test=> SET timezone = 'UTC';
SET
test=> INSERT INTO my_tbl (my_timestamp) VALUES (NOW());
INSERT 0 1

100%完璧とは言えませんが、データがすでにUTCに変換されていることを確認する、十分強力なフットシューティング対策になります。この方法についてはいろいろな意見がありますが、私の経験上、これが一番実践的なようです。

データベースのタイムゾーン処理に対する批判は概ね正当なものですが、PostgreSQLのタイムスタンプとタイムゾーンの処理は（いくつかの"機能"はあるものの）非常に素晴らしいものです。たとえば、そのような機能の1つを紹介します。

-- Make sure we're all working off of the same local time zone
test=> SET timezone = 'America/Los_Angeles';
SET
test=> SELECT NOW();
              now              
-------------------------------
 2011-05-27 15:47:58.138995-07
(1 row)

test=> SELECT NOW() AT TIME ZONE 'UTC';
          timezone          
----------------------------
 2011-05-27 22:48:02.235541
(1 row)

なお AT TIME ZONE 'UTC' はタイムゾーン情報を除去し、相対的な TIMESTAMP WITHOUT TIME ZONE を作成します。 UTC ).

不完全な TIMESTAMP WITHOUT TIME ZONE から TIMESTAMP WITH TIME ZONE に変更した場合、欠落しているタイムゾーンは接続から継承されます。

test=> SET timezone = 'America/Los_Angeles';
SET
test=> SELECT EXTRACT(TIMEZONE_HOUR FROM NOW());
 date_part 
-----------
        -7
(1 row)
test=> SELECT EXTRACT(TIMEZONE_HOUR FROM TIMESTAMP WITH TIME ZONE '2011-05-27 22:48:02.235541');
 date_part 
-----------
        -7
(1 row)

-- Now change to UTC    
test=> SET timezone = 'UTC';
SET
-- Create an absolute time with timezone offset:
test=> SELECT NOW();
              now              
-------------------------------
 2011-05-27 22:48:40.540119+00
(1 row)

-- Creates a relative time in a given frame of reference (i.e. no offset)
test=> SELECT NOW() AT TIME ZONE 'UTC';
          timezone          
----------------------------
 2011-05-27 22:48:49.444446
(1 row)

test=> SELECT EXTRACT(TIMEZONE_HOUR FROM NOW());
 date_part 
-----------
         0
(1 row)

test=> SELECT EXTRACT(TIMEZONE_HOUR FROM TIMESTAMP WITH TIME ZONE '2011-05-27 22:48:02.235541');
 date_part 
-----------
         0
(1 row)

最下段です。

• は、ユーザーのタイムゾーンを名前付きラベルとして保存します (例えば America/Los_Angeles のように)、UTC からのオフセットではなく、名前付きのラベルとして保存します (例: -0700 )
• 0 ではないオフセットを保存するやむを得ない理由がない限り、すべてに UTC を使用します。
• すべての非ゼロの UTC 時間を入力エラーとして扱います。
• 相対タイムスタンプと絶対タイムスタンプを混在させない。
• を使うこともできます。 UTC として timezone としてデータベースに保存します。

ランダムなプログラミング言語メモ：Pythonの datetime のようなライブラリで補うまでは、最初はイライラするかもしれませんが）絶対時間と相対時間の区別を維持するのに非常に適したデータ型です。 PyTZ ).

EDIT

相対と絶対の違いについてもう少し説明します。

絶対時間は、ある出来事を記録するために使用されます。例: "User 123 logged in" または "a graduation ceremonies start at 2011-05-28 2pm PST." あなたの地域のタイムゾーンに関係なく、イベントが起こった場所にテレポートできれば、イベントが起こったことを目撃することができます。データベース内の時間データのほとんどは絶対的なものです（したがって TIMESTAMP WITH TIME ZONE オフセットではなく、特定のタイムゾーンを管理するルールを表すテキスト ラベルと +0 オフセットを使用するのが理想的です）。

相対的なイベントは、まだ決定されていないタイムゾーンの観点から、何かの時間を記録またはスケジュールすることです。例: 「私たちのビジネスのドアは午前 8 時に開き、午後 9 時に閉まります」、「毎週月曜日の午前 7 時に朝食会を開きましょう」、「毎年ハロウィンの午後 8 時に」。一般に、相対時間はテンプレートまたは工場でイベントに使用され、絶対時間は他のほとんどすべてのものに使用されます。しかし、1つだけ例外があり、それは相対時間の価値を説明するために指摘する価値があります。絶対時刻が不明なほど遠い将来のイベントには、相対時刻を使用します。これは実際の例です。

2004 年で、2008 年の 10 月 31 日午後 1 時に米国西海岸で配達を予約する必要があるとします (すなわち America/Los_Angeles / PST8PDT ). もし、絶対時間を使って ’2008-10-31 21:00:00.000000+00’::TIMESTAMP WITH TIME ZONE を使って絶対時刻で保存すると、アメリカ政府が エネルギー政策法(2005 年) を可決し、サマータイムに関するルールが変更されたためです。配達が予定されていた2004年には、日付が 10-31-2008 は太平洋標準時であったはずです ( +8000 ) でしたが、2005 年以降、タイムゾーンデータベースは 10-31-2008 は太平洋夏時間 ( +0700 ). 相対的なタイムスタンプは議会の不正な改ざんの影響を受けないため、タイムゾーンとともに相対的なタイムスタンプを保存すれば、正しい配送スケジュールを実現できたはずです。スケジューリングに相対時間と絶対時間のどちらを使用するかの境界線は曖昧ですが、私の経験則では、3～6 ヶ月より先の将来のスケジューリングには相対タイムスタンプを使用すべきです (予定 = 絶対 vs 計画 = 相対 ?です。)。

相対的な時間のもう一つの/最後のタイプは INTERVAL . 例: "ユーザーがログインしてから20分後にセッションがタイムアウトします". また INTERVAL は、絶対タイムスタンプ ( TIMESTAMP WITH TIME ZONE ) または相対タイムスタンプ ( TIMESTAMP WITHOUT TIME ZONE ). ログインに成功した後、ユーザー セッションは 20 分で終了します (login_utc + session_duration)" または、朝の朝食会議の時間は 60 分です (recurring_start_time + meeting_length)" と言うことも同様に正しいのです。

最後の混乱のビット DATE , TIME , TIME WITHOUT TIME ZONE と TIME WITH TIME ZONE はすべて相対データ型です。例えば '2011-05-28'::DATE は相対的な日付を表します。なぜなら、真夜中を特定するために使われるタイムゾーンの情報がないからです。同様に '23:23:59'::TIME は相対的なものであり、タイムゾーンも DATE がわからないからです。たとえ '23:59:59-07'::TIME WITH TIME ZONE が何を表しているのかわからない。 DATE がどうなるかわからない。そして最後に DATE にタイムゾーンを指定することは、実際には DATE であり、それは TIMESTAMP WITH TIME ZONE :

test=> SET timezone = 'America/Los_Angeles';
SET
test=> SELECT '2011-05-11'::DATE AT TIME ZONE 'UTC';
      timezone       
---------------------
 2011-05-11 07:00:00
(1 row)

test=> SET timezone = 'UTC';
SET
test=> SELECT '2011-05-11'::DATE AT TIME ZONE 'UTC';
      timezone       
---------------------
 2011-05-11 00:00:00
(1 row)

データベースに日付やタイムゾーンを入れることは、良いことですが 微妙に正しくない結果を得るのは簡単です。 時間情報を正確かつ完全に保存するためには、最小限の追加作業が必要ですが、だからといって、追加作業が常に必要なわけではありません。