更改数据的时区值 [英] Changing time zone value of data

问题描述

我必须在其中导入没有时区信息的数据（但是，我知道我要导入的数据的特定时区），但是我需要时间戳数据库中带有时区格式的文件。一旦导入并将时间戳数据类型设置为带有时区的时间戳，Postgres将自动假定表中的数据来自我的时区并将我的时区分配给它。不幸的是，我要导入的数据不是我的时间范围，所以这行不通。

I have to import data without time zone information in it (however, I know the specific time zone of the data I want to import), but I need the timestamp with time zone format in the database. Once I import it and set the timestamp data type to timestamp with time zone, Postgres will automatically assume that the data in the table is from my time zone and assign my time zone to it. Unfortunately the data I want to import is not from my time frame, so this does not work.

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

The database also contains data with different time zones. However, the time zone within one table is always the same.

现在，我可以将数据库的时区设置为要导入的数据的时区。导入数据之前（使用 SET时区命令），并在导入完成后将其更改回我的时区，我很确定已经存储的数据不会受到影响通过数据库的时区更改。但这似乎是一种很肮脏的方法，以后可能会引起问题。

Now, I could set the time zone of the database to the time zone of the data I want to import before importing the data (using SET time zone command) and change it back to my time zone once the import is done, and I am pretty sure already stored data will not be affected by the time zone change of the database. But this seems to be a pretty dirty approach and may cause problems later on.

我想知道是否有一种更优雅的方式来指定导入时区而不用数据本身中的时区数据？

I wonder if there is a more elegant way to specify the time zone for the import without having the time zone data in the data itself?

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

Also, I have not found a way to edit time zone information after import. Is there a way not to convert, but simply to edit the time zone for a whole table, assuming that the whole table has the same time zone offset (i.e. if a wrong one has been assigned upon data entry/import)?

编辑：

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

I managed to specify a time zone upon import, the whole command being:

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

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

The data then gets imported using the session time zone. I assume this has no effect on any other queries on the database at the same time from other connections?

编辑2：

我发现了以便随后更改表的时区：

PostgreSQL更新时区偏移量

我想在导入后更改表的时区再使用会话更改临时时区更为优雅。假设整个表具有相同的时区。

I suppose it is more elegant to change the time zone of the table after import then to use session to change the local time zone temporary. Assuming the whole table has the same time zone of course.

因此，代码现在类似于：

So the code would be now something along the line of:

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

推荐答案

很多为导入会话设置时区比以后更新值更有效。

It is a lot more efficient to set the time zone for your import session than to update the values later.

给我的印象是，您将时区视为适用于否则表中的值不变。但这根本不是那样。将其视为输入/输出修饰符。实际的时间戳值（带有或不带有时区）总是 在内部存储为UTC时间戳（自'2000起的秒数-01-01 00:00'）。更多详细信息：

I get the impression that you think of the time zone like a setting that applies to otherwise unchanged values in the tables. But it's not like that at all. Think of it as an input / output modifier. Actual timestamp values (with or without time zone) are always stored as UTC timestamps internally (number of seconds since '2000-01-01 00:00'). A lot more details:

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

• Ignoring time zones altogether in Rails and PostgreSQL

第二个示例中的code> UPDATE 将表的大小加倍，因为每一行都无效并且添加了新版本（这就是 UPDATE 与Postgres中的 MVCC 一起使用）。除了昂贵的操作之外， VACUUM 将不得不在以后做更多的工作来清理表膨胀。效率很低。

The UPDATE in your second example doubles the size of the table, as every single row is invalidated and a new version added (that's how UPDATE works with MVCC in Postgres). In addition to the expensive operation, VACUUM will have to do more work later to clean up the table bloat. Very inefficient.

完全安全对 SET 的本地时区进行设置会议。这不会以任何方式影响并发操作。顺便说一句， SET SESSION 与普通的 SET 相同，因为 SESSION

It is perfectly safe to SET the local time zone for the session. This doesn't affect concurrent operations in any way. BTW, SET SESSION is the same as plain SET because SESSION is the default anyway.

如果您想完全确定，则可以将设置限制为当前的交易和 SET LOCAL 。我在此处

If you want to be absolutely sure, you can limit the setting to the current transaction with SET LOCAL. I quote the manual here

SET LOCAL 的影响仅持续到当前
交易的结束，无论已提交还是已结束不。特殊情况是在一次交易中， SET 后跟
，然后是 SET LOCAL ： SET LOCAL 的值将显示
，直到交易结束，但是之后（如果已提交
交易），则 SET 值将生效。

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;

这篇关于更改数据的时区值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！