Postgres:“AT TIME ZONE 'localtime'"==“AT TIME ZONE 'utc'"? [英] Postgres: "AT TIME ZONE 'localtime'"== "AT TIME ZONE 'utc'"?

问题描述

我很难理解 "AT TIME ZONE 'localtime'" 究竟是如何工作的?通过使用它，我发现它的行为与 "AT TIME ZONE 'UTC'" 完全一样......但为什么呢?"localtime" 在 postgres 中是 "UTC" 的同义词吗?或者它来自某些设置(环境?连接时区?虽然检查了两者，但它们似乎不相关)...

I'm struggling to understand how "AT TIME ZONE 'localtime'" exactly work? By playing with it, I found out that it acts exactly as "AT TIME ZONE 'UTC'"... But why? Is "localtime" a synonym of "UTC" in postgres? Or it comes from some setting (environment? connection timezone? although checked both, seems they are not related)...

有 "localtime" 函数 但我认为这里不涉及.

There's "localtime" function but I think it is not involved here.

示例 SQL:

# date
Thu Dec  8 12:00:05 AEDT 2016

# SELECT LOCALTIMESTAMP;
----------------------------
 2016-12-08 01:13:29.444725

# SELECT LOCALTIMESTAMP AT TIME ZONE 'America/New_York';
-------------------------------
 2016-12-08 06:08:31.183103+00

# SELECT LOCALTIMESTAMP AT TIME ZONE'localtime';
------------------------------
 2016-12-08 01:09:25.294063+00

# SELECT LOCALTIMESTAMP AT TIME ZONE 'utc';
 -------------------------------
 2016-12-08 01:09:44.32587+00 -- SAME AS ABOVE

 # SET TIME ZONE 'America/New_York';

 # SELECT LOCALTIMESTAMP;
 ----------------------------
  2016-12-07 20:13:34.924647

 # SELECT LOCALTIMESTAMP AT TIME ZONE 'localtime';
 ------------------------------
  2016-12-07 15:10:08.188197-05

 # SELECT LOCALTIMESTAMP AT TIME ZONE 'utc';
 ------------------------------
  2016-12-07 15:10:44.88332-05 -- SAME AS ABOVE

有什么提示吗?它在某处有记录吗?

Any hint? Is it documented somewhere?

推荐答案

Postgres 中的时间戳实际上并不存储任何时区信息.相反，此信息来自服务器设置的时区.在内部，所有时间戳信息都以 UTC 时间记录.因此，例如，如果您存储来自 UTC 以外时区的时间戳信息，Postgres 会先将该时间戳转换为 UTC，然后再存储.

A timestamp in Postgres does not actually store any timezone information. Rather, this information comes from the timezone which is set by the server. Internally, all timestamp information is recorded in UTC time. So, for example, if you stored timestamp information from a timezone other than UTC, Postgres would first convert that timestamp to UTC before storing it.

来自文档:

对于带时区的时间戳，内部存储的值始终采用 UTC(世界协调时间，传统上称为格林威治标准时间，GMT).使用该时区的适当偏移量将具有指定显式时区的输入值转换为 UTC.如果输入字符串中未指定时区，则假定其位于系统时区参数指示的时区中，并使用时区区域的偏移量转换为 UTC.

For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system's timezone parameter, and is converted to UTC using the offset for the timezone zone.

对于您的实际问题，localtime 只是服务器的时区，它始终是 UTC.

To your actual question, localtime is just the timezone of the server which is always UTC.

此外，似乎 Postgres 的 localtime 只是包装了 C 库函数 localtime()，它试图找到本地系统时间(默认为 UTC 时间)).同样，来自文档:

Furthermore, it appears that Postgres' localtime simply wraps the C library function localtime(), which attempts to find the local system time (which is in default UTC time). Again, from the documentation:

如果时区未在 postgresql.conf 中指定或作为服务器命令行选项指定，则服务器尝试使用 TZ 环境变量的值作为默认时区.如果 TZ 未定义或不是 PostgreSQL 已知的任何时区名称，服务器将尝试通过检查 C 库函数 localtime() 的行为来确定操作系统的默认时区.选择默认时区作为 PostgreSQL 已知时区中最接近的匹配项.(如果未指定，这些规则也用于选择 log_timezone 的默认值.)

If timezone is not specified in postgresql.conf or as a server command-line option, the server attempts to use the value of the TZ environment variable as the default time zone. If TZ is not defined or is not any of the time zone names known to PostgreSQL, the server attempts to determine the operating system's default time zone by checking the behavior of the C library function localtime(). The default time zone is selected as the closest match among PostgreSQL's known time zones. (These rules are also used to choose the default value of log_timezone, if not specified.)

这篇关于Postgres:“AT TIME ZONE 'localtime'"==“AT TIME ZONE 'utc'"?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！