Postgresql 中文操作指南

B.5. POSIX Time Zone Specifications #

PostgreSQL 可以接受根据 POSIX 标准编写针对 TZ 环境变量的规则来编写的时区规范。POSIX 时区规范不足以处理现实世界中时区历史的复杂性,但有时有理由使用它们。

PostgreSQL can accept time zone specifications that are written according to the POSIX standard’s rules for the TZ environment variable. POSIX time zone specifications are inadequate to deal with the complexity of real-world time zone history, but there are sometimes reasons to use them.

POSIX 时区规范有以下形式

A POSIX time zone specification has the form

STD offset [ DST [ dstoffset ] [ , rule ] ]

(为提高可读性,我们在字段之间显示空格,但实际中不应使用空格)。字段如下:

(For readability, we show spaces between the fields, but spaces should not be used in practice.) The fields are:

在此语法中,时区缩写可以是字母字符串,例如 EST,或用尖括号括起来的任意字符串,例如 <UTC-05>。请注意,此处提供的时区缩写仅用于输出,即使是那样也仅用于某些时间戳输出格式。正如在 Section B.4 中解释的那样,识别时间戳输入中的时区缩写的过程是确定的。

In this syntax, a zone abbreviation can be a string of letters, such as EST, or an arbitrary string surrounded by angle brackets, such as <UTC-05>. Note that the zone abbreviations given here are only used for output, and even then only in some timestamp output formats. The zone abbreviations recognized in timestamp input are determined as explained in Section B.4.

偏移量字段指定与 UTC 的小时差,还可以选择指定分钟和秒。它们的格式为 hh [ :_mm[:_ss ],可以选择在前面加一个符号( +- )。UTC 以西的时区使用正号。(请注意,这与 PostgreSQL 中其他地方使用的 ISO-8601 符号约定相反。) hh 可以有一位或两位数字;如果使用了 mmss ,则它们必须有两位数字。

The offset fields specify the hours, and optionally minutes and seconds, difference from UTC. They have the format hh[:mm[:ss]] optionally with a leading sign (+ or -). The positive sign is used for zones west of Greenwich. (Note that this is the opposite of the ISO-8601 sign convention used elsewhere in PostgreSQL.) hh can have one or two digits; mm and ss (if used) must have two.

夏令时转换 rule 的格式为

The daylight-savings transition rule has the format

dstdate [ / dsttime ] , stddate [ / stdtime ]

(如前所述,实际情况下不应包含空格。)dstdatedsttime 字段定义日光节约时间开始的时间,而 stddatestdtime 定义标准时间开始的时间。(在某些情况下,尤其在赤道以南的时区中,前者可能晚于后者。)日期字段具有以下格式之一:

(As before, spaces should not be included in practice.) The dstdate and dsttime fields define when daylight-savings time starts, while stddate and stdtime define when standard time starts. (In some cases, notably in zones south of the equator, the former might be later in the year than the latter.) The date fields have one of these formats:

  • n

    • A plain integer denotes a day of the year, counting from zero to 364, or to 365 in leap years.

  • J__n

    • In this form, n counts from 1 to 365, and February 29 is not counted even if it is present. (Thus, a transition occurring on February 29 could not be specified this way. However, days after February have the same numbers whether it’s a leap year or not, so that this form is usually more useful than the plain-integer form for transitions on fixed dates.)

  • Mm.n.__d

    • This form specifies a transition that always happens during the same month and on the same day of the week. m identifies the month, from 1 to 12. n specifies the n'th occurrence of the weekday identified by d. n is a number between 1 and 4, or 5 meaning the last occurrence of that weekday in the month (which could be the fourth or the fifth). d is a number between 0 and 6, with 0 indicating Sunday. For example, M3.2.0 means “the second Sunday in March”.

Note

M 格式足以描述许多常见夏时制转换规律。但请注意,这些变量中没有一个能处理夏时制变化,因此在实践中,为已命名时区(在 IANA 时区数据库中)存储的历史数据对于正确解释过去的时间戳非常有必要。

The M format is sufficient to describe many common daylight-savings transition laws. But note that none of these variants can deal with daylight-savings law changes, so in practice the historical data stored for named time zones (in the IANA time zone database) is necessary to interpret past time stamps correctly.

转换规则中的时间字段具有与先前描述的偏移字段相同的格式,除了不能包含符号。它们定义将更改为其他时间的当前本地时间。如果省略,则它们默认为 02:00:00

The time fields in a transition rule have the same format as the offset fields described previously, except that they cannot contain signs. They define the current local time at which the change to the other time occurs. If omitted, they default to 02:00:00.

如果给出了日光节约时间的缩写,但是省略了转换 rule 字段,则备用行为是使用规则 M3.2.0,M11.1.0,该规则对应于截至 2020 年的美国惯例(即在 3 月的第二个星期日向前推进,在 11 月的第一个星期日回退,两个转换都发生在现行时间上午 2 点)。请注意,此规则并未提供 2007 年之前年份的正确美国过渡日期。

If a daylight-savings abbreviation is given but the transition rule field is omitted, the fallback behavior is to use the rule M3.2.0,M11.1.0, which corresponds to USA practice as of 2020 (that is, spring forward on the second Sunday of March, fall back on the first Sunday of November, both transitions occurring at 2AM prevailing time). Note that this rule does not give correct USA transition dates for years before 2007.

作为一个示例,CET-1CEST,M3.5.0,M10.5.0/3 描述了截至 2020 年的巴黎当前时间管理实践。此规范指出标准时间具有缩写 CET,比 UTC 早一小时(东部时间);日光节约时间具有缩写 CEST,并且比 UTC 隐式提前两小时;日光节约时间在 3 月的最后一个星期日上午 2 点开始,在 10 月的最后一个星期日上午 3 点结束。

As an example, CET-1CEST,M3.5.0,M10.5.0/3 describes current (as of 2020) timekeeping practice in Paris. This specification says that standard time has the abbreviation CET and is one hour ahead (east) of UTC; daylight savings time has the abbreviation CEST and is implicitly two hours ahead of UTC; daylight savings time begins on the last Sunday in March at 2AM CET and ends on the last Sunday in October at 3AM CEST.

四个时区名称 EST5EDTCST6CDTMST7MDTPST8PDT 看起来像是 POSIX 时区规范。但是,它们实际上被视为命名时区,因为(由于历史原因)IANA 时区数据库中有这些名称的文件。实际含义是,即使纯 POSIX 规范不这样做,这些时区名称仍将产生有效的美国历史日光节约时间转换。

The four timezone names EST5EDT, CST6CDT, MST7MDT, and PST8PDT look like they are POSIX zone specifications. However, they actually are treated as named time zones because (for historical reasons) there are files by those names in the IANA time zone database. The practical implication of this is that these zone names will produce valid historical USA daylight-savings transitions, even when a plain POSIX specification would not.

人们应该小心,很容易拼错 POSIX 样式的时区规范,因为没有检查时区缩写的合理性。例如,SET TIMEZONE TO FOOBAR0 将起作用,使系统有效使用 UTC 的相当特殊的缩写。

One should be wary that it is easy to misspell a POSIX-style time zone specification, since there is no check on the reasonableness of the zone abbreviation(s). For example, SET TIMEZONE TO FOOBAR0 will work, leaving the system effectively using a rather peculiar abbreviation for UTC.