在 PostgreSQL timestamptz 类型中保留时区 [英] 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 访问的，因此我真的需要简单地存储具有原始偏移量的日期时间，并在选择时反映该值.

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 date/时间类型，即使是 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 中完全忽略时区

因此，如果你想保留输入字符串的那部分，你必须从字符串中提取它并自己保存.我会使用如下表格:

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，即 text，可以保存数字 offset 以及时区 abbreviation，或时区名称.

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 timestamptz 类型中保留时区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！