Каков подходящий тип данных для хранения часового пояса?

Ответ 1

К сожалению, PostgreSQL не предлагает тип данных часового пояса, поэтому вы, вероятно, должны использовать text.

interval на первый взгляд кажется логичным вариантом, и он подходит для некоторых целей. Однако он не учитывает летнее время и не учитывает тот факт, что разные регионы в одном и том же UTC-смещении имеют разные правила DST.

Нет отображения 1:1 из UTC offset обратно в часовой пояс.

Например, часовой пояс для Australia/Sydney (Новый Южный Уэльс) составляет UTC+10 (EST), или UTC+11 (EDT) во время летнего времени. Да, тот же аббревиатур EST, который использует США; аббревиатуры часовых поясов не являются уникальными в базе данных tzdata, поэтому Pg имеет параметр timezone_abbreviations. Хуже того, Брисбен (Квинсленд) имеет почти ту же самую долготу и находится в UTC+10 EST... но не имеет летнего сбережения, поэтому когда-то он при смещении -1 к Новому Южному Уэльсу во время НОУ в Южном Уэльсе.

(Обновление: совсем недавно Австралия приняла префикс A, поэтому использует AEST в качестве своего аббревиатура TZ в восточных штатах, но EST и WST остаются общепринятыми).

Смущает много?

Если все, что вам нужно сохранить, это UTC-смещение, тогда подходит interval. Если вы хотите сохранить часовой пояс, сохраните его как text. Это боль, чтобы подтвердить и преобразовать в смещение часового пояса на данный момент, но по крайней мере он справляется с DST.

Ответ 2

"+ hh: mm" и "-hh: mm" не являются часовыми поясами, они являются смещениями UTC. Хороший формат для сохранения - это целое число со знаком со смещением в минутах. Вы также можете использовать такие вещи, как interval, но это поможет вам только в том случае, если вы хотите выполнять вычисления даты непосредственно в PostgreSQL, например, в запросе и т.д. Обычно, хотя вы делаете эти вычисления на другом языке, а затем это зависит от этого языка если он поддерживает тип interval и имеет хорошую библиотеку даты и времени или нет. Но преобразование целого числа в некий тип interval -like, такой как Pythons timedelta, должно быть тривиальным, поэтому я бы просто сохранил его как целое число.

В часовых поясах есть имена, и, хотя нет стандартизованных имен для часовых поясов, в базе данных "tz" или "zoneinfo" есть один де-факто, и такие имена, как "Европа/Париж", "Америка/Нью-Йорк" "или" США/Тихоокеанский регион ". Они должны храниться как строки.

Windows использует совершенно разные имена, такие как "Romance time" (не спрашивайте). Вы можете хранить их, а также строки, но я бы избегал этого, эти имена не используются вне Windows, и имена не имеют смысла. Кроме того, переведенные версии окон имеют тенденцию использовать переведенные имена для этих часовых поясов, что делает его еще хуже.

Аббревиатуры типа "PDT" и "EST" не могут использоваться в качестве имен часовых поясов, поскольку они не уникальны. Есть четыре (я думаю, или это было пять?) Разных часовых поясов, все называемые "CST", так что они не пригодны для использования.

Короче: для часовых поясов сохраните имя в виде строки. Для смещений UTC сохраните смещение в минутах как целое число со знаком.

Ответ 3

В идеальном мире вы можете иметь внешний ключ для набора известных часовых поясов. Вы можете сделать что-то близкое к этому с представлениями и доменами.

Этот вики-совет Дэвида Э. Уилера создает домен, который проверяется на валидность в качестве часового пояса:

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 ) );

Полезно иметь список известных часовых поясов, в этом случае вы можете обойтись без домена и просто применить ограничение в одной таблице, содержащей известные имена часовых pg_timezone_names (полученной из представления pg_timezone_names), избегая необходимости выставлять домен в другом месте:

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;

Тогда вы можете применить корректность с помощью внешних ключей:

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

Ответ 4

возможно интервал

postgres=# select interval '01:30';
 interval 
----------
 01:30:00
(1 row)

postgres=# select interval '-01:30';
 interval  
-----------
 -01:30:00
(1 row)

Ответ 5

В postgres вы уже можете привести любой TIMESTAMP или TIMESTAMPTZ к или из названного часового пояса, поэтому вам не нужно искать значения из таблицы. Вы можете использовать это выражение непосредственно в проверочном ограничении, поэтому вам не нужно создавать функцию для этого:

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

Если вы попытаетесь вставить значение, которое не содержит действительный часовой пояс, вы получите ошибку, которая на самом деле довольно удобна для пользователя:

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

В зависимости от ваших требований, вам может потребоваться, чтобы ошибка была в том формате, который предоставит вам обычное нарушение ограничения, однако во многих случаях этого будет достаточно.

Если вы используете веб-фреймворк, который предоставляет вам список часовых поясов, которые вы можете иметь в раскрывающемся списке, тогда этой проверки должно быть достаточно, и тогда ваше контрольное ограничение будет просто резервной копией.