Изменение значения часового пояса данных

Мне нужно импортировать в него данные без информации о часовом поясе (однако я знаю конкретный часовой пояс данных, которые я хочу импортировать), но мне нужен формат timestamp with time zone в базе данных. Как только я импортирую его и установлю тип данных временной метки на timestamp with time zone, Postgres автоматически предположит, что данные в таблице относятся к моему часовому поясу, и назначит ему мой часовой пояс. К сожалению, данные, которые я хочу импортировать, не относятся к моему периоду времени, поэтому это не работает.

База данных также содержит данные с разными часовыми поясами. Однако часовой пояс в одной таблице всегда один и тот же.

Теперь я могу установить часовой пояс базы данных в часовой пояс данных, которые я хочу импортировать, перед импортом данных (используя команду SET time zone) и изменить его обратно на мой часовой пояс после завершения импорта, и я почти уверен уже сохраненные данные не будут затронуты изменением часового пояса базы данных. Но это кажется довольно грязным подходом и может вызвать проблемы позже.

Интересно, есть ли более элегантный способ указать часовой пояс для импорта, не имея данных о часовом поясе в самих данных?

Кроме того, я не нашел способа редактировать информацию о часовом поясе после импорта. Есть ли способ не конвертировать, а просто редактировать часовой пояс для всей таблицы, предполагая, что вся таблица имеет одинаковое смещение часового пояса (т.е. если при вводе/импорте данных был назначен неправильный)?

Изменить:
Мне удалось указать часовой пояс при импорте, вся команда:

set session time zone 'UTC';
COPY tbl FROM 'c:\Users\Public\Downloads\test.csv' DELIMITERS ',' CSV;
set session time zone 'CET';

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

Изменить 2:
Впоследствии я узнал, как изменить часовой пояс таблицы:
Смещение часового пояса обновления PostgreSQL

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

Таким образом, теперь код будет выглядеть примерно так:

COPY tbl FROM 'c:\Users\Public\Downloads\test.csv' DELIMITERS ',' CSV;
UPDATE tbl SET <tstz_field> = <tstz_field> AT TIME ZONE '<correct_time_zone>';

postgresql timestamp datetime timezone bulk-load
person harbun    schedule 01.10.2012    source источник
comment
Как вы выполняете импорт? вы не можете назначить часовой пояс при импорте?   -  person ertx    schedule 01.10.2012
comment
Спасибо за подсказку, я обновил свой пост, но я не уверен, какое влияние установка часового пояса сеанса оказывает на другие процессы в базе данных во время выполнения?   -  person harbun    schedule 01.10.2012

Ответы (1)


arrow_upward
12
arrow_downward

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

У меня сложилось впечатление, что вы думаете о часовом поясе как о настройке, которая применяется к неизменяемым значениям в таблицах. Но это совсем не так. Думайте об этом как о модификаторе ввода/вывода. Фактические значения timestamp (с часовым поясом или без него) всегда хранятся внутри в виде временных меток UTC (количество секунд с '2000-01-01 00:00'). Еще много подробностей:

UPDATE во втором примере удваивает размер таблицы, так как каждая отдельная строка становится недействительной и добавляется новая версия (вот как UPDATE работает с MVCC в Postgres). В дополнение к дорогостоящей операции, VACUUM позже придется выполнить дополнительную работу, чтобы убрать раздувание таблицы. Очень неэффективно.

совершенно безопасно использовать SET местный часовой пояс для сеанса. Это никак не влияет на параллельные операции. Кстати, SET SESSION это то же самое, что и простое SET, потому что SESSION в любом случае используется по умолчанию.

Если вы хотите быть абсолютно уверены, вы можете ограничить настройку текущей транзакцией с SET LOCAL. Цитирую руководство здесь

Эффекты SET LOCAL действуют только до конца текущей транзакции, независимо от того, зафиксирована она или нет. Особым случаем является SET, за которым следует SET LOCAL в рамках одной транзакции: значение SET LOCAL будет отображаться до конца транзакции, но затем (если транзакция зафиксирована) вступит в силу значение SET.

Соединить:

BEGIN;
SET LOCAL timezone = 'UTC';
COPY tabledata FROM 'c:\Users\Public\Downloads\test.csv' DELIMITERS ',' CSV;
COMMIT;

Проверить:

SHOW timezone;
person Erwin Brandstetter    schedule 01.10.2012
comment
Именно то, что мне нужно было знать, отличное объяснение. Данке Эрвин. :) - person harbun; 02.10.2012