指定一个时区用作参考时区 [英] Specify a time zone to use as the reference time zone

问题描述

基于前两个答案，该问题尚不明确，因此我将其完全重写：

以下问题仅与数据的存储方式和方式有关，而与检索时转换数据的方式或形式无关。因此，将SELECT转换为所需的时区是不合适的。

The following question is concerned only with how and what data is stored, and is no way shape or form concerned with converting data upon retrieval. As such, converting at SELECT to the desired time zone is not an appropriate answer.

将值插入带有时区的时间戳字段时，将对其进行检索（因此

When inserting a value into a timestamp with time zone field, it is retrieved (and thus presumably stored) with the timestamp converted to the local time zone of the database at the time it was inserted.

就是说，插入的时间戳记为 2012-01-01 00：00：00 + 00：00 检索为 2011-12-31 19：00：00-05 ，其中插入时数据库的本地时区为 -05 。在数据库位于 -04 的夏令时期间插入的时间戳记，将使用 -04 时间返回

That is so say, a timestamp inserted as 2012-01-01 00:00:00+00:00 is retrieved as 2011-12-31 19:00:00-05, where the local time zone of the database at the time of the insert was -05. Timestamps that were inserted during daylight savings time, when the database was at -04, are returned using the -04 time zone.

我想要的是所有时间戳在存储时都使用任意时区（因此可以检索所有时区，而无需任何其他工作，因为具有该时区）。也就是说，如果服务器绕地球运行，则所有时间都将是 +00：00 （任意时区），而不是 -12 ：00 到 +12：00 。

What I want is for all timestamps to use an arbitrary time zone when stored (and thus all be retrieved without any additional work as having that time zone). That is to say, were the server orbiting the planet, all times would be at +00:00 (arbitrary time zone), instead of -12:00 to +12:00.

我可以插入带有时区列，以便所有时间戳都相对于任意时区存储？如果是，怎么办？

Can I insert into a timestamp with time zone column such that all timestamps are stored relative to an arbitrary time zone? If so, how?

原始内容如下。

何时将值插入带有时区的时间戳字段中，它将被转换为服务器的当前时区。

When inserting a value into a timestamp with time zone field, it is being converted to the server's current time zone.

示例：如果我插入一个指定时区为 -1 的值，则检索该值将返回 -5 （服务器插入时所在的时区）。

Example: If I insert a value specifying a time zone of -1, retrieving it will give back the time at -5 (the time zone of the server at the time it was inserted).

是否可以指定应使用任意时区存储它？

Is it possible to specify that it should be stored using an arbitrary time zone?

注意：这个问题不是如何将返回的时间转换为另一个时区，而是特定于时间的存储方式。

Note: This question is not how to convert the returned time to another time zone, this is specific to how the time is stored.

推荐答案

除了时间戳，您还必须保存时区偏移。

如@Milen所述（并链接到手动）：时间戳仅保存时间点（作为抽象值）。 未保存时区修饰符，它仅用于相对于 UTC timestamp >。

As @Milen already explained (and linked to the manual): a timestamp only saves a point in time (as abstract value). The time zone modifier is not saved, it only serves to adjust the timestamp relative to UTC.

请考虑以下演示：

-- DROP TABLE tbl;
CREATE TEMP TABLE tbl (id int, myts timestamptz, mytz interval);

INSERT INTO tbl VALUES
 (1, now()                , EXTRACT (timezone from now()) * interval '1s')
,(2, '2012-01-01 00:00-05', interval '-5h')
,(3, '2012-01-01 00:00+04', interval '4h')
,(4, '2012-11-11 20:30+03', interval '3h');

SELECT *
      ,(myts AT TIME ZONE mytz)::text
       || CASE WHEN mytz > '0:0' THEN '+' ELSE '' END
       || to_char(mytz, 'FMHH24:mi') AS timestamp_at_origin
FROM tbl;

在本地运行以查看。请特别注意 的详细信息> AT TIME ZONE 的构造，以及如何从具有时区的（local！）时间戳中提取时区。

now（）返回带有时区的时间戳或 timestamptz

Run it locally to see. Pay special attention to the details of the AT TIME ZONE construct, and how I extract the time zone from the (local!) timestamp with time zone.
now() returns timestamp with time zone or timestamptz for short.

EXTRACT (timezone from now()) * interval '1s'

timestamp_at_origin 显示带有时区的时间戳记，如其来源。如果我了解您的问题，那么您正在寻找的是什么。

您可以进一步改善格式。

timestamp_at_origin displays the timestamp with time zone as seen at its origin. If I understood your question, then that is what you are looking for.
You could further improve formatting.

您可能对此<一个与href = https://stackoverflow.com/q/8233351/939860>相关的问题，该问题揭示了时区的模糊性和陷阱。

You may be interested in this related question which sheds some light on the ambiguities and pitfalls of time zones.

这篇关于指定一个时区用作参考时区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！