postgresql - 指定一个时区作为引用时区

Question

根据前两个答案，问题和最初发布的一样不清楚，因此我完全重写了它:

下面的问题只涉及数据的存储方式和内容，与检索时转换数据无关。因此，在 SELECT 处转换为所需的时区并不是一个合适的答案。

当将一个值插入带有时区的时间戳字段时，它会被检索(并因此可能被存储)，时间戳在插入时转换为数据库的本地时区。

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

我想要的是所有时间戳在存储时使用任意时区(因此无需任何额外工作即可检索所有时区)。也就是说，如果服务器绕地球运行，所有时间都是+00:00(任意时区)，而不是-12:00到+12:00.

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

---

原文如下。

当向timestamp with time zone 字段中插入一个值时，它会被转换为服务器的当前时区。

示例:如果我插入一个指定时区 -1 的值，检索它将返回 -5 的时间(服务器的时区位于插入时间)。

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

注意:这个问题不是如何将返回的时间转换成其他时区的问题，这是具体到时间如何存储的问题。

Answer 1

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

正如@Milen 已经解释过的(并链接到 manual ):timestamp 仅保存一个时间点(作为抽象值)。时区修饰符未保存，它仅用于调整相对于UTC 的timestamp。

考虑以下演示:

-- 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的细节构造，以及我如何从(本地!)timestamp with time zone 中提取时区。
now() 返回 timestamp with time zone 或简称为 timestamptz。

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

timestamp_at_origin 显示带有时区的时间戳，如其原点所示。如果我理解你的问题，那么这就是你要找的。
您可以进一步改进格式设置。

您可能对此感兴趣related question这揭示了时区的模糊性和陷阱。