在PostgreSQL时间戳类型中保留时区 [英] Preserve timezone in PostgreSQL timestamptz type

问题描述

对于符合ISO8601的日期时间

For an ISO8601 compliant datetime

2004-10-19 10:23:54+02

是否有可能具有抵消了 +02 的值

Is it possible to have that value, with +02 offset, reflected in the stored column value and also preserved when it is selected?

摘录自文档的适当部分 Postgres的默认行为是转换为UTC，此时原始偏移会丢失。

From my reading of the appropriate section of the docs Postgres' default behavior is to convert to UTC at which point the original offset is lost. This is certainly what I'm seeing.

数据是通过无法添加任何特殊tz转换的ORM访问的，因此我真的只需要简单地存储datetime具有原始偏移量，并且在选择时具有反映的值。

The data is accessed via an ORM that is not able to add any special tz conversion so I really need to simply store the datetime with original offset and have the value reflected when selected.

对于那些渴望告诉我它是同一时间实例的人来说，保留此值对这些数据很重要。

For anyone dying to tell me it's the same instance in time, the preservation of this value has significance to this data.

推荐答案

您已经了解了自己的情况， 时区 对于Postgres日期/时间类型根本没有保存，即使使用 timestamptz 也是如此。它的作用分别只是输入修饰符或输出修饰符。仅保存值（时间点）。此相关答案中的详细信息：

As you already figured out yourself, the time zone is not saved at all with Postgres date / time types, not even with timestamptz. Its role is just an input modifier or an output decorator, respectively. Only the value (the point in time) is saved. Ample details in this related answer:

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

• Ignoring timezones altogether in Rails and PostgreSQL

因此，如果要保留输入字符串的那部分，则必须从字符串中提取出来并自己保存。我将使用类似这样的表：

Therefore, if you want to preserve that part of the input string, you have to extract it from the string and save it yourself. I would use a table like:

CREATE TABLE tstz
 ...
 , ts timestamp    -- without time zone
 , tz text
)

tz 是文本，可以保存数字偏移量以及时区缩写或时区名称。

tz, being text, can hold a numeric offset as well as a time zone abbreviation, or a time zone name.

难点是要根据解析器遵循的所有各种规则提取时区部分一种不会轻易中断的方式。 不用解析自己的过程，而是让解析器完成工作。考虑这个演示：

The difficulty is to extract the time zone part according to all the various rules the parser follows and in a way that won't break easily. Instead of cooking up your own procedure, make the parser do the work. Consider this demo:

WITH ts_literals (tstz) AS (
   VALUES ('2013-11-28 23:09:11.761166+03'::text)
         ,('2013-11-28 23:09:11.761166 CET')
         ,('2013-11-28 23:09:11.761166 America/New_York')
   )
SELECT tstz
      ,tstz::timestamp AS ts
      ,right(tstz, -1 * length(tstz::timestamp::text)) AS tz
FROM   ts_literals;

SQL小提琴。

无论是否有 T 在日期和时间之间。关键逻辑在这里：

Works with or without a T between date and time. The key logic is here:

right(tstz, -1 * length(tstz::timestamp::text)) AS tz

在修剪解析器识别为日期/时间部分的长度后，获取时间戳字符串的剩余内容。如您所说，这取决于输入内容：

Take what's left of a timestamp string after trimming the length of what the parser identified as date / time component. This relies on the input being, as you stated:

经过验证的ISO8601字符串

validated ISO8601 strings

这篇关于在PostgreSQL时间戳类型中保留时区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！