Блог

Главная - Вопросы по программированию - PostgreSQL: добавление часового пояса в столбцы без преобразования времени

PostgreSQL: добавление часового пояса в столбцы без преобразования времени

#postgresql #datetime #timezone #timestamp-with-timezone

#postgresql #datetime #Часовой пояс #timestamp-with-timezone

Вопрос:

У меня есть таблица с несколькими столбцами временных меток, в основном следующими:

          timestamp_utc        |          timestamp
------------------------------------------------------------
'2020-10-28 08:00:00.000'     | '2020-10-28 04:00:00.000'

Я знаю, что первый столбец находится во времени UTC, а второй — по Нью-йоркскому времени. Мне было интересно, как я могу добавить соответствующий часовой пояс. Итак, результат, который я хотел бы получить, выглядит примерно так:

          timestamp_utc        |          timestamp
------------------------------------------------------------
'2020-10-28 08:00:00.000Z'     | '2020-10-28 04:00:00.000-04'

Я пытался делать такие вещи, как:

 >>> select timestamp_utc AT TIME ZONE 'UTC' from table;

'2020-10-28 04:00:00.000-04' /* wrong */
  
 >>> select timestamp AT TIME ZONE 'EST' from table;

'2020-10-28 05:00:00.000-04' /* wrong, it should be 04:00:00 */

Итак, наконец-то попробовал это для Нью-йоркской части:

 >>> select timestamp_est AT TIME ZONE 'America/New_York' from table;

'2020-10-28 04:00:00.000-04'

Поэтому я думаю, что когда я добавляю часовой пояс с указанием местоположения, это работает. Однако, когда я пытаюсь использовать истинные часовые пояса, это преобразует время в дополнение к добавлению часового пояса?

Комментарии:

1. Какие типы для столбцов timestamp_utc и timestamp (К вашему сведению, я бы переименовал это или сделал с вами timestamp_est )? Каковы настройки TimeZone для сервера?

2. Имена столбцов — это просто фиктивные примеры, поэтому не беспокойтесь, если это называется timestamp . Часовой пояс установлен, но я не могу изменить часовой пояс сервера. Обойдите это, используя Python вместо PG. Как сказал @Laurenz Albe, вероятно, это невозможно сделать в PG

3. Я бы посмотрел этот раздел » Часовые пояса » в документах. Это поможет вам понять, что происходит. Подсказка для вашего TimeZone значения не установлена EST . Second EST — это действительно просто смещение, а не полный часовой пояс, отсюда и ошибка в вашем преобразовании. Наконец, важен тип данных, в котором вы храните свои значения: timestamp или timestamptz. Я постоянно использую dateutil Python, но это все равно зависит от базового поведения в базе данных.

Ответ №1:

Вы не можете этого сделать, поскольку PostgreSQL (вопреки интуиции) не сохраняет часовой пояс вместе с a timestamp with time zone . Этот тип данных внутренне хранится в UTC, и на дисплее он преобразуется в соответствии с текущей настройкой timezone параметра.

Итак, есть два способа сохранить смещение часового пояса с меткой времени:

  1. Добавьте новый столбец timezone , содержащий часовой пояс в виде строки ( Europe/Vienna ) или числа ( 2 ).

  2. Сохраните временную метку в виде строки. Обычно это плохая идея.

Комментарии:

1. Я бы предостерег от сохранения смещения часового пояса (2). Поскольку при этом НЕ учитывается переход на летнее время (летнее время), где учитывается соответствующее имя часового пояса.

2. @Страховщик я согласен. Я только что предложил возможность использования числового смещения, поскольку, похоже, это то, что запрашивает OP.