PostgreSQL timestamptz 和 timetz 函数

Question

我真的需要存储本地日期和时间(飞机轮子更新日期和时间)和 utc 偏移量，以便我可以转换为 utc 来计算间隔(飞行持续时间)。人们按本地时间旅行，您需要 utc 进行跨时区计算。我曾希望使用 timestamptz，但它绝对不能用于此目的。它将所有内容转换为 postgres 时区的函数。在我的例子中，这是 yyyy-dd-mm hh:mi:ss-07。

但是，我调查了 timetz 只是为了涵盖所有基础。它存储正是我所需要的。它保留本地时间，同时向 utc 提供偏移量。除了现在我需要两列而不是一列来存储信息。

我的问题是:为什么 timestamptz 和 timetz 函数给出不同的结果？有没有办法让 timestamptz 包含本地时区偏移量而不是系统时区偏移量？以下是说明差异的查询:

select cast('2015-05-01 11:25:00 america/caracas' as timestamptz)

-- 2015-05-01 08:55:00-07
;

select cast('2015-05-01 11:25:00 america/caracas' as timetz)

-- 11:25:00-04:30
;

Answer 1

在试图理解 PostgreSQL 的 timestamptz 时，我个人觉得 timestamp with time zone 的措辞令人困惑，因为它不存储任何时区。根据the docs:

All timezone-aware dates and times are stored internally in UTC. They are converted to local time in the zone specified by the TimeZone configuration parameter before being displayed to the client.

请注意，在该页面上，timestamp 和 timestamptz 的存储特性和限制是相同的。

在我的脑海中，为了保持直白，我将 timestamptz 翻译成“现实世界中的时间戳”，并将 timestamp 翻译成“你可能不是故意的”使用这种类型”(因为到目前为止，我发现自己只需要存储与现实世界相关的时间戳。)

所以:

Why do the timestamptz and the timetz functions give different results?

这似乎是因为 PostgreSQL 认为他们不被允许让 timetz 像 timestamptz 那样工作:

The type time with time zone is defined by the SQL standard, but the definition exhibits properties which lead to questionable usefulness.

我的猜测是，其中一些“属性”是他们不喜欢的，而您却喜欢。

和:

Is there a way to make the timestamptz include the local time zone offset rather than the system time zone offset?

没有为 timestamptz 值存储偏移量。它们只是真实世界的时间戳。所以本地时区的存储方式就是你已经想到的方式:单独存储。

create table my_table (
    happened timestamptz,
    local_time_zone varchar
);
select happened at time zone 'UTC', happened at time zone local_time_zone
    from my_table;