Какой тип метки времени следует выбрать в базе данных PostgreSQL?

во-первых, обработка времени и арифметика PostgreSQL фантастичны, и Вариант 3 отлично подходит в общем случае. Это, однако, неполное представление о времени и часовых поясах и может быть дополнено:

1. сохранить имя часового пояса пользователя в качестве предпочтения пользователя (например,America/Los_Angeles, а не -0700).
2. имеют ли пользовательские события/данные времени, представленные локально в их системе отсчета (скорее всего, смещение от UTC, например -0700).
3. в приложения, преобразуйте время в UTC и хранится с помощью .
4. возврат запросов времени локального в часовой пояс пользователя (т. е. преобразование из UTC до America/Los_Angeles).
5. установите базу данных timezone до UTC.

эта опция не всегда работает, потому что может быть трудно получить часовой пояс пользователя и, следовательно, совет хеджирования использовать TIMESTAMP WITH TIME ZONE для легких приложений. Тем не менее, позвольте мне объяснить некоторые фоновые аспекты этого варианта 4 Подробнее деталь.

как вариант 3, причина WITH TIME ZONE потому что время, в которое что-то случилось это абсолютное момент времени. WITHOUT TIME ZONE дает a относительные часовой пояс. Никогда, никогда, никогда не смешивайте абсолютные и относительные временные метки.

С точки зрения программирования и согласованности убедитесь, что все вычисления выполняются с использованием UTC в качестве часового пояса. Это не требование PostgreSQL, но оно помогает при интеграции с другими языки программирования или среды. Задание CHECK в столбце, чтобы убедиться, что запись в столбец метки времени имеет смещение часового пояса 0 это защитная позиция, которая предотвращает несколько классов ошибок (например, скрипт сбрасывает данные в файл, а что-то еще сортирует данные времени с помощью лексической сортировки). Опять же, PostgreSQL не нуждается в этом, чтобы правильно выполнять вычисления даты или конвертировать между часовыми поясами (т. е. PostgreSQL очень искусен в преобразовании времени между любыми двумя произвольными часовой пояс.) Чтобы гарантировать, что данные, поступающие в базу данных, хранятся со смещением нуля:

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% идеально, но он обеспечивает достаточно сильную анти-footshooting меру, которая гарантирует, что данные уже преобразованы в 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)

обратите внимание, что 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)
• используйте UTC для всего, если нет веской причины хранить ненулевое смещение
• относиться ко всем ненулевым мирового времени в качестве входных данных ошибка
• никогда не смешивать и сопоставлять относительные и абсолютные метки времени
• использовать UTC как timezone в базе данных, если это возможно

случайные язык программирования примечание: в Python datetime тип данных очень хорошо поддерживает различие между абсолютным и относительным временем (хотя и разочаровывает сначала, пока вы не дополните его библиотекой, такой как PyTZ).

---

EDIT

позвольте мне объяснить разницу между относительным и абсолютным немного больше.

абсолютное время используется для записи события. Примеры: "пользователь 123 вошел в систему" или " a выпускные вечера начинаются в 2 часа дня 2011-05-28 ПСТ."Независимо от вашего местного часового пояса, если бы вы могли телепортироваться туда, где произошло событие, вы могли бы стать свидетелем происходящего события. Большинство данных времени в базе данных является абсолютным (и поэтому должно быть TIMESTAMP WITH TIME ZONE, в идеале со смещением +0 и текстовой меткой, представляющей правила, регулирующие конкретный часовой пояс - не смещение).

относительное событие будет записывать или планировать время чего-то с точки зрения a еще предстоит определить часовой пояс. Примеры: "двери нашего бизнеса открываются в 8 утра и закрываются в 9 вечера", "давайте встречаться каждый понедельник в 7 утра для еженедельного завтрака" или "каждый Хэллоуин в 8 вечера."В общем, относительное время используется в шаблоне или фабрике для событий, а абсолютное время используется почти для всего остального. Есть одно редкое исключение, которое стоит отметить, которое должно иллюстрировать значение относительного времени. Для будущих событий, которые достаточно далеко в будущем, где может быть неопределенность относительно абсолютного времени, в которое что-то может произойти, используйте относительную метку времени. Вот реальный пример:

предположим, что это 2004 год, и вам нужно запланировать доставку 31 октября 2008 года в 1 час дня на западном побережье США (т. е. America/Los_Angeles/PST8PDT). Если вы сохранили это, используя абсолютное время, используя ’2008-10-31 21:00:00.000000+00’::TIMESTAMP WITH TIME ZONE, доставка появилась бы в 2 часа дня, потому что правительство США прошло закон об энергетической политике 2005 года это изменило правила управление переходом на летнее время. В 2004 году, когда была запланирована поставка, дата 10-31-2008 было бы Тихоокеанское стандартное время (+8000), но начиная с 2005 года + базы данных часовых поясов признали, что 10-31-2008 было бы Тихоокеанское летнее время (+0700). Хранение относительной временной метки с часовым поясом привело бы к правильному графику доставки, потому что относительная временная метка невосприимчива к плохо информированному вмешательству Конгресса. Где отсечка между использованием относительного vs абсолютное время для планирования вещей-это нечеткая линия, но мое эмпирическое правило заключается в том, что планирование для чего-либо в будущем дальше, чем 3-6mo, должно использовать относительные временные метки (запланированные = абсолютные 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)

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