PostgreSQL.)hh can have one or two digits;mm andss (if used) must have two. The daylight-savings transitionrule has the format
dstdate [/dsttime],stddate [/stdtime]
(As before, spaces should not be included in practice.) Thedstdate anddsttime fields define when daylight-savings time starts, whilestddate andstdtime 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. 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 then'th occurrence of the weekday identified byd.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
TheM 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.
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 to02:00:00.
If a daylight-savings abbreviation is given but the transitionrule field is omitted,PostgreSQL attempts to determine the transition times by consulting theposixrules file in the IANA time zone database. This file has the same format as a full time zone entry, but only its transition timing rules are used, not its UTC offsets. Typically, this file has the same contents as theUS/Eastern file, so that POSIX-style time zone specifications follow USA daylight-savings rules. If needed, you can adjust this behavior by replacing theposixrules file.
Note
The facility to consult aposixrules 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.
If theposixrules file is not present, the fallback behavior is to use the ruleM3.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).
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 abbreviationCET and is one hour ahead (east) of UTC; daylight savings time has the abbreviationCEST 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.
The four timezone namesEST5EDT,CST6CDT,MST7MDT, andPST8PDT 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 due to lack of a suitableposixrules file.
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.
