存储带有时间戳的原始时区-解网

问：

我有一个如下所示的表定义：

CREATE TABLE what_happened (
  id SERIAL PRIMARY KEY,
  action VARCHAR(128),
  created TIMESTAMP WITH TIME ZONE NOT NULL
);

我希望能够存储为列中插入的值给定的原始时间偏移量。据我了解，postgresql 在内部转换为 UTC，然后在 fetch 时根据 .createdTIMESTAMP WITH TIME ZONESHOW TIMEZONE

假设我们正在添加一个偏移量为 +0900 的时间戳，并且该偏移量是有意义的。当我们添加值时，它将转换为 UTC 并存储在数据库中。2023-06-30T12:34:56+0900

提取同一记录时，日期将转换为当前时区。如果我们当前处于 CET +0100，它将显示为。2023-06-30T04:34:56+0100

虽然这是正确的，但它不是很合乎逻辑，它假设原始时区不感兴趣。

那么，除了将值存储为文本并在读取时根据需要将其转换为日期之外，我该如何存储原始时间偏移量呢？我可以将原始时间偏移量与时间戳一起存储并使用它吗？如果是这样，我看不出正确的方法，因为更改时间戳的偏移量总是返回一个没有时区的新值，这并不是很有帮助（如文档中所述）。AT TIME ZONE xyz

所以也许我需要：

添加第二列以保存时间偏移量和时间戳
在显示时，将时间戳转换回原始时间偏移量，并构建一个有效的 ISO 8601 字符串来附加我的原始时间偏移量信息。

这似乎有点复杂，而且有些步骤不是很简单。我希望避免使用字符串操作手动执行此操作。

PostgreSQL 日期时间时区 postgresql-15

我不相信我的理解是错误的。您建议如何获取原始时间戳？根据上面的表格定义，我将时区设置为欧洲/柏林并插入一行，然后设置为亚洲/东京并插入另一行，然后将时区更改为美国/太平洋，所有结果的时区均为 -07。Postgres 文档似乎很清楚：

When a timestamp with time zone value is output, it is always converted from UTC to the current timezone zone, and displayed as local time in that zone. To see the time in another time zone, either change timezone or use the AT TIME ZONE construct.

1赞 Matt Johnson-Pint 9/28/2023

当前时区与当前偏移量不同。是的，您的结果会受到您设置的当前时区的影响，但该时区内的偏移量将是应用于相关时间戳的偏移量。换言之，封装了 CET （UTC+1）和 CEST （UTC+2），以及管理这些偏移量之间转换的规则。如果同时用于存储和检索，则偏移量不会有所不同。（除非政府修改了实际规则。Europe/BerlinEurope/BerlinEurope/Berlin

1赞 Matt Johnson-Pint 9/28/2023

时区标签 wiki 可能对您有所帮助。

答：

1赞 Laurenz Albe 9/27/2023 #1

保留输入的时区的唯一方法是将时区字符串保存在单独的列中。提取时区最好在客户端完成。

提取时区是一回事，但将内部 UTC 时间戳转换为正确的时区，然后在字符串中附加正确的时区偏移量，似乎是一大堆容易出错的工作。我突然想到，处理这个问题的最佳方法似乎是保持定义中的列，但添加第二列。这会将日期转换为字符串，并且似乎将当前时间戳存储为时间偏移量，正如我所希望的那样。使用更多空间，但不需要更多的转换或串联。createdcreated_str VARCHAR DEFAULT now()

0赞 Laurenz Albe 9/28/2023

我认为这种做法没有错。

1赞 ketil 9/28/2023 #2

正如 @Matt Johnson-Pint 所指出的，偏移量和时区是不同的东西。像这样的时区在夏令时和冬令时有不同的时间偏移，postgres 会跟踪这一点。因此，我的示例在夏令时边界上使用不同的偏移量不需要进一步关注，因为当我们在同一时区内工作时，postgres 已经正确处理了它。Europe/Berlin

如果要求存储来自完全不同时间偏移量的时间戳，并且对原始偏移量感兴趣，则以下内容可能很有用。

似乎同时存储存储原始值的字符串类型和字符串类型可能是要走的路。TIMESTAMP WITH TIME ZONE

因此，该表可能如下所示：

CREATE TABLE what_happened (
  id SERIAL PRIMARY KEY,
  action VARCHAR(128),
  created TIMESTAMP WITH TIME ZONE NOT NULL,
  created_str VARCHAR NOT NULL
);

这提供了我一直在寻找的行为。主要缺点是时间戳存储两次。

postgres=# insert into what_happened (action, created, created_str) VALUES('test us pacific', '2023-09-27 11:26:35.844749-07', '2023-09-27 11:26:35.844749-07');
INSERT 0 1
postgres=# insert into what_happened (action, created, created_str) VALUES('test tokyo', '2023-09-28 02:26:35.844749+09', '2023-09-28 02:26:35.844749+09');
INSERT 0 1
postgres=# insert into what_happened (action, created, created_str) VALUES('test berlin', '2023-09-27 20:27:00.725301+02', '2023-09-27 20:27:00.725301+02');
INSERT 0 1
postgres=# select * from what_happened;
 id |    action       |            created            |          created_str
----+-----------------+-------------------------------+-------------------------------
  1 | test us pacific | 2023-09-27 20:26:35.844749+02 | 2023-09-27 11:26:35.844749-07
  2 | test tokyo      | 2023-09-27 20:26:51.236822+02 | 2023-09-28 02:26:35.844749+09
  3 | test berlin     | 2023-09-27 20:27:00.725301+02 | 2023-09-27 20:27:00.725301+02
(3 rows)

编写一个从设置的触发器可能是有意义的，而不是在任何地方提供两次相同的值。我不能只存储，因为字符串排序不会在时间偏移量之间正确排序。如果不需要索引，则以下查询将正确排序：created_strcreatedcreated_strcreated_str

SELECT * FROM what_happened ORDER BY (created_str::TIMESTAMP WITH TIME ZONE);

创建索引也不是完全简单的，但可能可以通过标记为不可变的包装函数来解决。(created_str::TIMESTAMP WITH TIME ZONE)

不过，最安全的选择可能是同时保留和。索引和排序依据，仅用于显示，或在原始时区重要时用于进一步处理。createdcreated_strcreatedcreated_str

存储带有时间戳的原始时区

Store original time zone with time stamp

评论

评论

评论