PostgreSQL 存储哪些时区信息? [英] What timezone information does PostgreSQL store?

问题描述

PostgreSQL 文档相当详尽且有用:

https://www.postgresql.org/docs/9.2/datatype-datetime.html#DATATYPE-TIMEZONES

但似乎忽略了一个相当有用的点的清晰度，在这个点上清晰度可能是必要的和有帮助的.阅读了文档以及各种相关的 stackoverflow 问题和回复后，我怀疑以下情况是正确的:

<块引用>

PostgreSQL 数据类型 timestamp with timezone 存储日期和时间和 utcoffset(+ve 在格林威治以东)

我会进一步推断并怀疑这是真的:

<块引用>

PostgreSQL 数据类型 timestamp with timezone 存储日期和时间和 utcoffset(+ve 在格林威治以东)到分钟分辨率.

我的问题与这些推论有关.是否正确，如果正确，可以转交什么证据予以证实，如果不正确，可以转交什么证据相反.

这很有趣的主要原因当然是因为如果为真，那么接受存储在表 pg_timezone_names 中的名称或缩写的时区的 PostgreSQL 只存储 UTC 偏移量，从而丢失 DST 信息.

意思是，为了使读者将来可以使用实际时区名称(如表 pg_timezone_names 中所定义)，它必须与 timestamp with timezone 在它旁边的列中.

我现在感兴趣的主要原因是我想到了一种相当聪明的渲染时间方式，可以记录地球上任何地方的事件时间.即如果记录的时间在用户当前时区，则将其报告为一个简单的日期/时间(没有时区信息)，并且只有当它在与读者不同的时区中时，才报告时区信息(即使这样，时区名称可能比 UTC 偏移量更用户友好).

而且如果我希望在网站上实现这种上下文敏感的渲染，我将不得不在我的事件时间(以及我存储的任何其他时区感知日期/时间)旁边存储时区名称.

但我对基于推理而非知识做出这样的承诺感到不自在，并且想要一些支持或反驳这些推理的证据.

解决方案

你的两个假设都是错误的:

PostgreSQL 将 timestamp with time zone 存储为 8 字节整数，其中包含以微秒为单位的 2000-01-01 00:00:00 UTC 偏移量.>

所以它既不存储时区，也不存储 1 分钟的精度.

转换为字符串后，时间戳将根据 timezone 参数的当前设置进行格式化.

所以如果你需要单独存储时区如果你需要记住它并使用AT TIME ZONE表达式将时间戳转换为正确的时区.

您要求提供文档参考.其中一部分是 这里:

/** 时间戳代表绝对时间.[...]* 时间戳，以及间隔的 h/m/s 字段，存储为* 以微秒为单位的 int64 值.(曾几何时他们是* 以秒为单位的双精度值.)

在同一个文件中，你找到

/* Unix 和 Postgres 计算中第 0 天的儒略日期等价物 */#define UNIX_EPOCH_JDATE 2440588/* == date2j(1970, 1, 1) */#define POSTGRES_EPOCH_JDATE 2451545/* == date2j(2000, 1, 1) */

The PostgreSQL documentation is fairly thorough and useful:

https://www.postgresql.org/docs/9.2/datatype-datetime.html#DATATYPE-TIMEZONES

but seems to overlook clarity on a rather useful point where clarity might be warranted and help. Having read the documentation and various related stackoverflow questions and responses, I am left suspecting that the following is true:

The PostgreSQL datatype timestamp with timezone stores a date and time and a utcoffset (+ve being east of Greenwich)

I would further infer and suspect it is true that:

The PostgreSQL datatype timestamp with timezone stores a date and time and a utcoffset (+ve being east of Greenwich) to minute resolution.

My question relates to these inferences. Are they correct, and if so what evidence can be forwarded to confirm them, and if not what evidence can be forwarded to the contrary.

The main reason this is interesting is because of course if true, then PostgreSQL which accepts timezones by name or abbreviation as stored in the table pg_timezone_names only stores the UTC offset and thereby loses DST information.

Meaning, to make the actual time zone name (as defined in the table pg_timezone_names) available to a reader in the future it must be explicitly stored alongside the timestamp with timezone in a column beside it.

The main reason this interests me right now is that I had in mind what I felt was a reasonably clever way of rendering times that can record the time of an event anywhere on earth. Namely if the recorded time is in the users current timezone, then report it as a naive date/time (no timezone info), and only if it is in a timezone different to the readers, report the timezone information (and even then, the timezone name may be more user friendly than the UTC offset).

And it looks like I will be obliged to store timezone name beside my event times (and any other timezone aware date/time's I store) if I wish to implement such contextually sensitive rendering on a website.

But I feel ill at ease making such a commitment on the basis of inference, and not knowledge and would like some evidence supporting or contradicting these inferences.

解决方案

Both your assumptions are wrong:

PostgreSQL stores a timestamp with time zone as 8-byte integer that contains the offset from 2000-01-01 00:00:00 UTC in microseconds.

So it neither stores the time zone, nor is the precision 1 minute.

Upon conversion to a string, the timestamp is formatted according to the current setting of the timezone parameter.

So if you have to store the time zone separately if you need to remember it and use the AT TIME ZONE expression to convert the timestamp to the proper time zone.

You ask for documentation references. Part of that is here:

/*
 * Timestamp represents absolute time.
[...]
 * Timestamps, as well as the h/m/s fields of intervals, are stored as
 * int64 values with units of microseconds.  (Once upon a time they were
 * double values with units of seconds.)

In the same file, you find

/* Julian-date equivalents of Day 0 in Unix and Postgres reckoning */
#define UNIX_EPOCH_JDATE        2440588 /* == date2j(1970, 1, 1) */
#define POSTGRES_EPOCH_JDATE    2451545 /* == date2j(2000, 1, 1) */

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