数据类型为“timestamp with time zone"的时区存储 [英] Time zone storage in data type "timestamp with time zone"

问题描述

在 PostgreSQL 中，数据类型 timestamp 和 timestamp with timezone 都使用 8 个字节.

In PostgreSQL, the data types timestamp and timestamp with timezone both use 8 bytes.

我的问题是:

1. 用什么格式来存储日期&时间戳中的时间?
2. timestamp with timezone 中的时区信息是如何存储的类型，稍后在读取类型时如何解析?

1. What format is used to store date & time in a timestamp?
2. How is the time zone information stored in the timestamp with timezone type, and how is it parsed later when reading the type?

推荐答案

这只是由于有点误导性的类型名称造成的误解.时区本身根本不存储.它只是作为计算实际存储的 UTC 时间戳(输入)的偏移量.或者作为装饰器根据 当前或给定时间区域(输出).这都是按照 SQL 标准.

This is just a misunderstanding stemming from the somewhat misleading type name. The time zone itself is not stored at all. It just acts as offset to compute a UTC timestamp (input), which is actually stored. Or as decorator in the display of a timestamp according to the current or given time zone (output). That's all according to the SQL standard.

只存储时间点，没有区域信息.这就是为什么 64 位信息就足够了.时间戳根据会话的当前时区设置显示给客户端.

Just the point in time is stored, no zone information. That's why 64 bit of information is enough. The timestamp is displayed to the client according to the current time zone setting of the session.

详情:

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

另外，既然 Jon 提到了，time with time zone 是在 SQL 标准中定义的，因此在 Postgres 中实现，但不鼓励使用:

Also, since Jon mentioned it, time with time zone is defined in the SQL standard and thus implemented in Postgres, but its use is discouraged:

time with time zone 由 SQL 标准定义，但定义表现出导致有用性可疑的特性.

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

这是一种固有的歧义类型，无法正确处理DST.

It's an inherently ambiguous type that cannot deal with DST properly.

这篇关于数据类型为“timestamp with time zone"的时区存储的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！