Was ist ein geeigneter Datentyp zum speichern einer Zeitzone?

Leider PostgreSQL nicht bieten eine time zone-Datentyp ist, so sollten Sie wahrscheinlich verwenden text.

interval scheint wie eine logische option, auf den ersten Blick, und es ist geeignet für einige Anwendungen. Jedoch nicht zu berücksichtigen Sommerzeit, noch macht es die Tatsache berücksichtigen, dass unterschiedliche Regionen in der gleichen UTC-offset haben verschiedene DST-Regeln.

Gibt es nicht eine 1:1-Zuordnung von UTC-offset zurück auf die Zeitzone.

Zum Beispiel die Zeitzone für Australia/Sydney (New South Wales) ist UTC+10 (EST), oder UTC+11 (EDT) während der Sommerzeit. Ja, das ist die gleiche Abkürzung EST dass die USA verwendet; Zeit zone Abkürzungen sind nicht eindeutig, in der tzdata-Datenbank, weshalb die Pg hat die timezone_abbreviations Einstellung. Schlimmer noch, Brisbane (Queensland) ist mit fast der gleichen longditude und ist in UTC+10 EST ... aber keine Sommerzeit, deshalb ist es bei einer -1 offset zu New South Wales im New South Wales die Sommerzeit.

(Update: Mehr kürzlich Australien hat eine A Präfix, so dass es verwendet AEST als seine östlichen Staaten TZ Abkürzung, aber EST und WST bleiben in gemeinsame Nutzung).

Verwirrend viel?

Wenn alles, was Sie brauchen, um zu speichern ist ein UTC-offset dann ein interval geeignet ist. Wenn Sie speichern möchten, eine Zeitzone, speichern Sie es als text. Es ist ein Schmerz, um zu validieren und zu konvertieren, Zeitzonen-offset im moment, aber zumindest ist es meistert mit DST.

In einer idealen Welt könnte man einen foreign key auf eine Reihe von bekannten Zeitzonen. Sie können etwas tun, schließen Sie diese mit Ansichten und domains.

Diese wiki-Tipp von David E. Wheleer erstellt eine Domäne, die getestet wird, für seine Gültigkeit als eine Zeitzone:

CREATE OR REPLACE FUNCTION is_timezone( tz TEXT ) RETURNS BOOLEAN as $$
BEGIN
 PERFORM now() AT TIME ZONE tz;
 RETURN TRUE;
EXCEPTION WHEN invalid_parameter_value THEN
 RETURN FALSE;
END;
$$ language plpgsql STABLE;

CREATE DOMAIN timezone AS CITEXT
CHECK ( is_timezone( value ) );

Es ist nützlich, um eine Liste der bekannten Zeitzonen, in dem Fall könnten Sie verzichten auf die domain und nur die Einschränkung erzwungen wird, in der eine Tabelle mit den bekannten timezone Namen (gewonnen aus der Sicht pg_timezone_names), Vermeidung der Notwendigkeit aussetzen, die domain woanders:

CREATE TABLE tzone
(
  tzone_name text PRIMARY KEY (tzone_name) CHECK (is_timezone(tzone_name))
);

INSERT INTO tzone (tzone_name)
SELECT name FROM pg_timezone_names;

Dann können Sie erzwingen Richtigkeit durch Fremdschlüssel:

CREATE TABLE myTable (
...
tzone TEXT REFERENCES tzone(tzone_name)
);

In postgres können Sie schon gegossen jedem TIMESTAMP oder TIMESTAMPTZ zu oder von einem benannten Zeitzone, so brauchen Sie nicht zu suchen, bis die Werte aus einer Tabelle. Sie können mit diesem Ausdruck direkt in einer check-Einschränkung, so dass Sie nicht brauchen zu erstellen eine Funktion für diese entweder:

CREATE TABLE locations (
    location_id SERIAL PRIMARY KEY,
    name TEXT,
    timezone TEXT NOT NULL CHECK (now() AT TIME ZONE timezone IS NOT NULL)
);

Wenn Sie versuchen, legen Sie einen Wert enthält keinen gültigen Zeitzone, erhalten Sie einen Fehler, ist eigentlich Recht benutzerfreundlich:

INSERT INTO locations (name, timezone) VALUES ('foo', 'Adelaide/Australia');
ERROR:  time zone "Adelaide/Australia" not recognized

Je nach Ihren Anforderungen, Sie könnte müssen Fehler werden in das format, dass ein normaler constraint-Verletzung würde Ihnen aber in vielen Fällen wird dies reichen.

Wenn Sie ein web-framework, das bietet Ihnen eine Liste von Zeitzonen, die Sie haben, in einer dropdown-box, dann ist diese Validierung sollte ausreichend sein, und dann Ihre check-Einschränkung ist nur eine Sicherung.