更改数据的时区值

Question

我必须导入其中没有时区信息的数据(但是，我知道要导入的数据的特定时区)，但我需要数据库中的timestamp with time zone格式。一旦我导入它并将时间戳数据类型设置为timestamp with time zone，Postgres就会自动假设表中的数据来自我的时区，并将我的时区分配给它。不幸的是，我要导入的数据不是来自我的时间框架，所以这不起作用。

该数据库还包含不同时区的数据。但是，一个表中的时区总是相同的。

现在，我可以在导入数据之前(使用SET time zone命令)将数据库的时区设置为我想要导入的数据的时区，并在导入完成后将其更改回我的时区，我确信已经存储的数据不会受到数据库时区更改的影响。但这似乎是一种相当肮脏的方法，可能会在以后引发问题

我想知道是否有一种更优雅的方法可以指定导入的时区，而不在数据本身中包含时区数据？

此外，我还没有找到在导入后编辑时区信息的方法。是否有一种方法不进行转换，而只是编辑整个表的时区，假设整个表具有相同的时区偏移(即，如果在数据输入/导入时分配了错误的时区偏移)？

编辑：
我设法在导入时指定了一个时区，整个命令是：

set session time zone 'UTC';
COPY tbl FROM 'c:UsersPublicDownloadstest.csv' DELIMITERS ',' CSV;
set session time zone 'CET';

然后使用会话时区导入数据。我认为这不会同时影响其他连接对数据库的任何其他查询？

编辑2:
我后来发现了如何更改表的时区：
PostgreSQL更新时区偏移

我想在导入后更改表的时区，然后使用会话临时更改本地时区会更优雅。当然，假设整张表都有相同的时区。

因此，现在的代码应该是：

COPY tbl FROM 'c:UsersPublicDownloadstest.csv' DELIMITERS ',' CSV;
UPDATE tbl SET <tstz_field> = <tstz_field> AT TIME ZONE '<correct_time_zone>';

Answer 1

为导入会话设置时区比以后更新值效率高得多。

我的印象是，你认为时区就像一个设置，适用于表中其他未更改的值。但事实并非如此。把它想象成一个输入/输出修饰符。实际timestamp值(带或不带时区)始终存储为内部UTC时间戳(自'2000-01-01 00:00'以来的秒数)。更多细节：

• 在Rails和PostgreSQL中完全忽略时区

第二个例子中的UPDATE使表的大小增加了一倍，因为每一行都无效，并添加了一个新版本(这就是UPDATE在Postgres中与MVCC一起工作的方式)。除了昂贵的操作外，VACUUM稍后还必须做更多的工作来清理表膨胀。效率很低。

对于会话的本地时区SET是完全安全的。这不会以任何方式影响并发操作。BTW，SET SESSION与普通SET相同，因为SESSION无论如何都是默认值。

如果您想绝对确定，可以使用SET LOCAL将设置限制为当前事务。我在这里引用手册

SET LOCAL的影响只持续到当前事务，无论是否提交。特殊情况是SET由单个事务中的SET LOCAL：SET LOCAL值将为直到事务结束，但之后(如果事务已提交)，则CCD_ 18值将生效。

放在一起：

BEGIN;
SET LOCAL timezone = 'UTC';
COPY tabledata FROM 'c:UsersPublicDownloadstest.csv' DELIMITERS ',' CSV;
COMMIT;

检查：

SHOW timezone;