数据类型为“带时区的时间戳"的时区存储在存储设备中. [英] 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, 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中的时区

此外，由于乔恩(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.

这篇关于数据类型为“带时区的时间戳"的时区存储在存储设备中.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！