マルチタイムゾーン プロジェクトのコンテキストで、Postgres データベースにタイムスタンプを保存するためのベスト プラクティスを定義したいと考えています。
できます
TIMESTAMP WITHOUT TIME ZONEこのフィールドの挿入時に使用されたタイムゾーンを選択して記憶しますTIMESTAMP WITHOUT TIME ZONE挿入時に使用されたタイムゾーンの名前を含む別のフィールドを選択して追加しますTIMESTAMP WITH TIME ZONEそれに応じてタイムスタンプを選択して挿入します
私はオプション 3 (タイムゾーン付きのタイムスタンプ) を少し好みますが、この件について知識のある意見を聞きたいです。
ベストアンサー1
まず、PostgreSQL の時間処理と計算は素晴らしく、オプション 3 は一般的なケースでは問題ありません。ただし、時間とタイムゾーンのビューが不完全であるため、補足することができます。
- ユーザーのタイムゾーンの名前をユーザー設定として保存します (例
America/Los_Angeles: ではなく-0700)。 - ユーザーのイベント/時間データを、参照フレームに対してローカルに送信します (おそらく UTC からのオフセット、など
-0700)。 - アプリケーションでは、列を使用して時間を変換し
UTC、保存しますTIMESTAMP WITH TIME ZONE。 - ユーザーのタイムゾーンにローカルな時間リクエストを返します (つまり、 から に変換します
UTC)America/Los_Angeles。 - データベース
timezoneを に設定しますUTC。
このオプションは、ユーザーのタイムゾーンを取得するのが難しい場合があるため、常に機能するとは限りません。そのため、TIMESTAMP WITH TIME ZONE軽量アプリケーションには使用しないことをお勧めします。とはいえ、このオプション 4 の背景について、詳しく説明しましょう。
オプション3と同様に、その理由は、WITH TIME ZONE何かが起こった時間が絶対瞬間WITHOUT TIME ZONE。相対的タイムゾーン。絶対タイムスタンプと相対タイムスタンプを絶対に混在させないでください。
プログラムと一貫性の観点から、すべての計算が UTC をタイムゾーンとして使用して行われるようにしてください。これは PostgreSQL の要件ではありませんが、他のプログラミング言語や環境と統合するときに役立ちます。CHECK列に を設定して、タイムスタンプ列への書き込みに のタイムゾーンオフセットがあることを確認することは、0いくつかの種類のバグ (スクリプトがデータをファイルにダンプし、他の何かが語彙ソートを使用して時間データをソートするなど) を防ぐ防御策です。繰り返しますが、PostgreSQL では、日付計算を正しく実行したり、タイムゾーンを変換したりするためにこれは必要ありません (つまり、PostgreSQL は任意の 2 つのタイムゾーン間の時間を変換するのに非常に優れています)。データベースに入力されるデータがオフセット 0 で保存されるようにするには、次のようにします。
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 のタイムスタンプとタイムゾーンの処理は、(あちこちにいくつかの「機能」があるにもかかわらず) 非常に優れています。たとえば、次のような機能があります。
-- 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)
Note that AT TIME ZONE 'UTC' strips time zone info and creates a relative TIMESTAMP WITHOUT TIME ZONE using your target’s frame of reference (UTC).
When converting from an incomplete TIMESTAMP WITHOUT TIME ZONE to a TIMESTAMP WITH TIME ZONE, the missing time zone is inherited from your connection:
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)
The bottom line:
- store a user’s time zone as a named label (e.g.
America/Los_Angeles) and not an offset from UTC (e.g.-0700) - use UTC for everything unless there is a compelling reason to store a non-zero offset
- treat all non-zero UTC times as an input error
- never mix and match relative and absolute timestamps
- also use
UTCas thetimezonein the database if possible
Random programming language note: Python's datetime data type is very good at maintaining the distinction between absolute vs relative times (albeit frustrating at first until you supplement it with a library like PyTZ).
EDIT
Let me explain the difference between relative vs absolute a bit more.
Absolute time is used to record an event. Examples: "User 123 logged in" or "a graduation ceremonies start at 2011-05-28 2pm PST." Regardless of your local time zone, if you could teleport to where the event occurred, you could witness the event happening. Most time data in a database is absolute (and therefore should be TIMESTAMP WITH TIME ZONE, ideally with a +0 offset and a textual label representing the rules governing the particular timezone - not an offset).
A relative event would be to record or schedule the time of something from the perspective of a yet-to-be-determined time zone. Examples: "our business's doors open at 8am and close at 9pm", "let's meet every Monday at 7am for a weekly breakfast meeting," or "every Halloween at 8pm." In general, relative time is used in a template or factory for events, and absolute time is used for almost everything else. There is one rare exception that’s worth pointing out which should illustrate the value of relative times. For future events that are far enough in the future where there could be uncertainty about the absolute time at which something could occur, use a relative timestamp. Here’s a real world example:
Suppose it’s the year 2004 and you need to schedule a delivery on October 31st in 2008 at 1pm on the West Coast of the US (i.e. America/Los_Angeles/PST8PDT). If you stored that using absolute time using ’2008-10-31 21:00:00.000000+00’::TIMESTAMP WITH TIME ZONE , the delivery would have shown up at 2pm because the US Government passed the Energy Policy Act of 2005 that changed the rules governing daylight savings time. In 2004 when the delivery was scheduled, the date 10-31-2008 would have been Pacific Standard Time (+8000), but starting in year 2005+ timezone databases recognized that 10-31-2008 would have been Pacific Daylight Savings time (+0700). Storing a relative timestamp with the time zone would have resulted in a correct delivery schedule because a relative timestamp is immune to Congress’ ill-informed tampering. Where the cutoff between using relative vs absolute times for scheduling things is, is a fuzzy line, but my rule of thumb is that scheduling for anything in the future further than 3-6mo should make use of relative timestamps (scheduled = absolute vs planned = relative ???).
The other/last type of relative time is the INTERVAL. Example: "the session will time out 20 minutes after a user logs in". An INTERVAL can be used correctly with either absolute timestamps (TIMESTAMP WITH TIME ZONE) or relative timestamps (TIMESTAMP WITHOUT TIME ZONE). It is equally correct to say, "a user session expires 20min after a successful login (login_utc + session_duration)" or "our morning breakfast meeting can only last 60 minutes (recurring_start_time + meeting_length)".
Last bits of confusion: DATE, TIME, TIME WITHOUT TIME ZONE and TIME WITH TIME ZONE are all relative data types. For example: '2011-05-28'::DATE represents a relative date since you have no time zone information which could be used to identify midnight. Similarly, '23:23:59'::TIME is relative because you don't know either the time zone or the DATE represented by the time. Even with '23:59:59-07'::TIME WITH TIME ZONE, you don't know what the DATE would be. And lastly, DATE with a time zone is not in fact a DATE, it is a 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)
Putting dates and time zones in databases is a good thing, but it is easy to get subtly incorrect results. Minimal additional effort is required to store time information correctly and completely, however that doesn’t mean the extra effort is always required.