PostgreSQL时间戳记中的时区存储 [英] Time zone storage in PostgreSQL timestamps

问题描述

在PostgreSQL中，数据类型 timestamp 和时区戳与时区都使用8个字节。

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

我的问题是：

（1）用于存储date&时间戳的时间？

（2）时区信息如何存储在时间段的时间戳中，以及在阅读时如何解析该类型。

(1) What format is used to store date & time in a timestamp?
(2) And how the is time zone information stored in the timestamp with timezone type, and how it's 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, which is actually stored. 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中完全忽略时区

• Ignoring timezones altogether in Rails and PostgreSQL

此外，由于Jon提到它，时区与时区在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:

时区与时区由SQL定义标准，但定义
显示属性，导致有问题的有用性。

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

这是一个固有的含糊不清的类型，一个href =https://en.wikipedia.org/wiki/Daylight_saving_time =noreferrer> DST pro perly。

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

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