Сохранять часовой пояс в типе timestamptz PostgreSQL

Ответ 1

Как вы уже выяснили, часовой пояс вообще не сохраняется с типами даты/времени Postgres, даже с timestamptz. Его роль - это только модификатор ввода или декодер вывода, соответственно. Сохраняется только значение (момент времени). Достаточно подробные сведения в этом ответном ответе:

• Игнорирование часовых поясов вообще в Rails и PostgreSQL

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

CREATE TABLE tstz
 ...
 , ts timestamp    -- without time zone
 , tz text
)

tz, будучи text, может содержать числовое смещение, а также временную зону аббревиатура или часовой пояс имя.

Трудность состоит в том, чтобы извлечь часть часового пояса в соответствии со всеми различными правилами, которые выполняет парсер, и таким образом, что это не будет легко ломаться. Вместо того, чтобы приготовить свою собственную процедуру, заставить парсер выполнять работу. Рассмотрим эту демонстрацию:

WITH ts_literals (tstz) AS (
   VALUES ('2013-11-28 23:09:11.761166+03'::text)
         ,('2013-11-28 23:09:11.761166 CET')
         ,('2013-11-28 23:09:11.761166 America/New_York')
   )
SELECT tstz
      ,tstz::timestamp AS ts
      ,right(tstz, -1 * length(tstz::timestamp::text)) AS tz
FROM   ts_literals;

SQL Fiddle.

Работает с или без T между датой и временем. Ключевая логика здесь:

right(tstz, -1 * length(tstz::timestamp::text)) AS tz

Возьмите то, что осталось от строки timestamp после обрезки длины того, что парсер определил как компонент даты/времени. Это зависит от ввода, как вы заявили:

проверенные строки ISO8601

Ответ 2

Java-разработчики могут использовать Joda Time в сочетании с Jadira UserType PersistentDateTimeAndZone. Пример:

@Basic(optional = false)
@Columns(columns = { @Column(name = "modificationtime"),
        @Column(name = "modificationtime_zone") })
@Type(type = "org.jadira.usertype.dateandtime.joda.PersistentDateTimeAndZone")
@Index(name = "payment_modificationtime_idx")
private DateTime modificationTime = null;

В этом примере информация DateTime находится в двух столбцах:

• modificationtime timestamp without time zone для сохранения метки времени в часовом поясе UTC
• modificationtime_zone varchar(255), чтобы сохранить идентификатор часового пояса в виде строки (например, America/Caracas)

Хотя Joda Time и Jadira (и Hibernate) специфичны для Java (и это де-факто подход), вышеупомянутый подход к структурированию столбцов RDBMS для хранения как временной метки, так и часового пояса может применяться к любому языку программирования.

Ответ 3

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

Я собирался предложить, чтобы ваш ORM мог определить пользовательские методы надувания/дефляции для обработки магии, но, по-видимому, этого не может. Вы должны указать, какую ОРМ вы используете.

У вас может быть хранилище ORR/получить строку в базе данных и использовать trigger в Postgres, чтобы преобразовать это в хранимый timestamptz в другом столбце, который используется при выполнении запросов на стороне базы данных. Если у вас много таблиц с данным типом данных, это может быть немного громоздким.

Если вам действительно нужны данные в одном столбце в базе данных, вы можете определить составной тип в Postgres, хотя ваш ORM не может быть в состоянии справиться с ними.