diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
index 3df189ad85..ca61439501 100644
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -2492,25 +2492,10 @@ TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'
In addition to the timezone names and abbreviations,
PostgreSQL will accept POSIX-style time zone
- specifications of the form STDoffset or
- STDoffsetDST, where
- STD is a zone abbreviation, offset is a
- numeric offset in hours west from UTC, and DST is an
- optional daylight-savings zone abbreviation, assumed to stand for one
- hour ahead of the given offset. For example, if EST5EDT
- were not already a recognized zone name, it would be accepted and would
- be functionally equivalent to United States East Coast time. In this
- syntax, a zone abbreviation can be a string of letters, or an
- arbitrary string surrounded by angle brackets (<>).
- When a daylight-savings zone abbreviation is present,
- it is assumed to be used
- according to the same daylight-savings transition rules used in the
- IANA time zone database's posixrules entry.
- In a standard PostgreSQL installation,
- posixrules is the same as US/Eastern, so
- that POSIX-style time zone specifications follow USA daylight-savings
- rules. If needed, you can adjust this behavior by replacing the
- posixrules file.
+ specifications, as described in
+ . This option is not
+ normally preferable to using a named time zone, but it may be
+ necessary if no suitable IANA time zone entry is available.
@@ -2537,19 +2522,6 @@ TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'
above, this is not necessarily the same as local civil time on that date.
-
- One should be wary that the POSIX-style time zone feature can
- lead to silently accepting bogus input, since there is no check on the
- reasonableness of the zone abbreviations. For example, SET
- TIMEZONE TO FOOBAR0 will work, leaving the system effectively using
- a rather peculiar abbreviation for UTC.
- Another issue to keep in mind is that in POSIX time zone names,
- positive offsets are used for locations west of Greenwich.
- Everywhere else, PostgreSQL follows the
- ISO-8601 convention that positive timezone offsets are east
- of Greenwich.
-
-
In all cases, timezone names and abbreviations are recognized
case-insensitively. (This is a change from PostgreSQL
diff --git a/doc/src/sgml/datetime.sgml b/doc/src/sgml/datetime.sgml
index 7cce826e2d..fb210b377b 100644
--- a/doc/src/sgml/datetime.sgml
+++ b/doc/src/sgml/datetime.sgml
@@ -555,6 +555,210 @@
+
+ <acronym>POSIX</acronym> Time Zone Specifications
+
+
+ time zone
+ POSIX-style specification
+
+
+
+ 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.
+
+
+
+ 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:
+
+
+
+ STD is the zone abbreviation to be used
+ for standard time.
+
+
+
+
+ offset is the zone's standard-time offset
+ from UTC.
+
+
+
+
+ DST is the zone abbreviation to be used
+ for daylight-savings time. If this field and the following ones are
+ omitted, the zone uses a fixed UTC offset with no daylight-savings
+ rule.
+
+
+
+
+ dstoffset is the daylight-savings offset
+ from UTC. This field is typically omitted, since it defaults to one
+ hour less than the standard-time offset,
+ which is usually the right thing.
+
+
+
+
+ rule defines the rule for when daylight
+ savings is in effect, as described below.
+
+
+
+
+
+
+ In this syntax, a zone abbreviation can be a string of letters, or an
+ arbitrary string surrounded by angle brackets
+ (<>). 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 .
+
+
+
+ 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 -). A positive sign is considered to define
+ zones west of Greenwich. (Note that this is the
+ opposite of the ISO-8601 sign convention used elsewhere in
+ PostgreSQL.)
+
+
+
+ The daylight-savings transition rule has the
+ format
+
+dstdate / dsttime , stddate / stdtime
+
+ (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.
+
+
+
+
+ Jn
+
+
+ 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.)
+
+
+
+
+ Mm.n.d
+
+
+ This form specifies a transition occurring on a particular day of
+ the week. d is the weekday number from
+ 0 to 6 (Sunday is 0). m identifies the
+ month, from 1 to 12. n specifies
+ the n'th occurrence of that weekday
+ within the month (1–4), or 5 meaning the last occurrence of
+ that weekday in the month (which could be the fourth or the fifth).
+
+
+
+
+
+
+
+
+ The M format is sufficient to describe many common
+ daylight-savings transition rules; for
+ example M3.2.0 means second Sunday in
+ March
. 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.
+
+
+
+
+ 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.
+
+
+
+ If a daylight-savings abbreviation is given but the
+ transition rule field is omitted,
+ PostgreSQL attempts to determine the
+ transition times by consulting the posixrules file
+ in the IANA time zone database. This file has the same format as a
+ full time zone entry, but only its transition times are used, not its
+ UTC offsets. Typically, this file has the same contents as the
+ US/Eastern file, so that POSIX-style time zone
+ specifications follow USA daylight-savings rules. If needed, you can
+ adjust this behavior by replacing the posixrules
+ file.
+
+
+
+ If the posixrules file is not present,
+ 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).
+
+
+
+
+ The facility to consult a posixrules file has
+ been deprecated by IANA, and it is likely to go away in the future.
+ One bug in this feature, which is unlikely to be fixed before it
+ disappears, is that it fails to apply DST rules to dates after 2038.
+
+
+
+
+ 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 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
+ and ends on the last Sunday in October at 3AM.
+
+
+
+ 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.
+
+
+
+
History of Units