postgresql - 更改数据的时区值

Question

我要在里面导入没有时区信息的数据(但是，我知道我要导入的数据的具体时区)，但是我需要timestamp with time zone 数据库中的格式。一旦我导入它并将时间戳数据类型设置为 timestamp with time zone，Postgres 将自动假定表中的数据来 self 的时区并将我的时区分配给它。不幸的是，我要导入的数据不是来 self 的时间范围，所以这不起作用。

数据库还包含不同时区的数据。但是，一张表中的时区始终相同。

现在，我可以在导入数据之前(使用SET time zone 命令)将数据库的时区设置为我要导入的数据的时区，然后再改回我的时间导入完成后，我很确定已经存储的数据不会受到数据库时区更改的影响。但这似乎是一种非常肮脏的方法，以后可能会导致问题。

我想知道是否有更优雅的方式来指定导入的时区，而无需在数据本身中包含时区数据？

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

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

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

然后使用 session 时区导入数据。我假设这对其他连接同时对数据库的任何其他查询没有影响？

编辑 2:
后来我发现了如何更改表的时区:
PostgreSQL update time zone offset

我想在导入后更改表的时区比使用 session 临时更改本地时区更优雅。当然假设整个表具有相同的时区。

所以代码现在应该是这样的:

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

Answer 1

为导入 session 设置时区比稍后更新值很多更有效率。

我的印象是您将时区视为适用于表中其他未更改值的设置。但它根本不是那样的。将其视为输入/输出修饰符。实际 timestamp 值(带或不带时区)始终在内部存储为 UTC 时间戳(自 '2000-01-01 00:00')。更多详细信息:

• Ignoring time zones altogether in Rails and PostgreSQL

第二个示例中的 UPDATE 使表的大小加倍，因为每一行都无效并添加了新版本(这就是 UPDATE 使用 MVCC 的方式)在 Postgres 中)。除了昂贵的操作之外，VACUUM 稍后将不得不做更多的工作来清理表膨胀。非常低效。

为 session SET本地时区绝对安全。这不会以任何方式影响并发操作。顺便说一句，SET SESSION 与普通 SET 相同，因为 SESSION 无论如何都是默认值。

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

The effects of SET LOCAL last only till the end of the current transaction, whether committed or not. A special case is SET followed by SET LOCAL within a single transaction: the SET LOCAL value will be seen until the end of the transaction, but afterwards (if the transaction is committed) the SET value will take effect.

放在一起:

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

检查:

SHOW timezone;