codekingpro/portable-devtools
114k
1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>B.5. POSIX Time Zone Specifications</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="datetime-config-files.html" title="B.4. Date/Time Configuration Files" /><link rel="next" href="datetime-units-history.html" title="B.6. History of Units" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">B.5. <acronym class="acronym">POSIX</acronym> Time Zone Specifications</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="datetime-config-files.html" title="B.4. Date/Time Configuration Files">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="datetime-appendix.html" title="Appendix B. Date/Time Support">Up</a></td><th width="60%" align="center">Appendix B. Date/Time Support</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="datetime-units-history.html" title="B.6. History of Units">Next</a></td></tr></table><hr /></div><div class="sect1" id="DATETIME-POSIX-TIMEZONE-SPECS"><div class="titlepage"><div><div><h2 class="title" style="clear: both">B.5. <acronym class="acronym">POSIX</acronym> Time Zone Specifications <a href="#DATETIME-POSIX-TIMEZONE-SPECS" class="id_link">#</a></h2></div></div></div><a id="id-1.11.3.8.2" class="indexterm"></a><p>3 <span class="productname">PostgreSQL</span> can accept time zone specifications4 that are written according to the <acronym class="acronym">POSIX</acronym> standard's rules5 for the <code class="varname">TZ</code> environment6 variable. <acronym class="acronym">POSIX</acronym> time zone specifications are7 inadequate to deal with the complexity of real-world time zone history,8 but there are sometimes reasons to use them.9 </p><p>10 A POSIX time zone specification has the form11</p><pre class="synopsis">12<em class="replaceable"><code>STD</code></em> <em class="replaceable"><code>offset</code></em> [<span class="optional"> <em class="replaceable"><code>DST</code></em> [<span class="optional"> <em class="replaceable"><code>dstoffset</code></em> </span>] [<span class="optional"> , <em class="replaceable"><code>rule</code></em> </span>] </span>]13</pre><p>14 (For readability, we show spaces between the fields, but spaces should15 not be used in practice.) The fields are:16 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>17 <em class="replaceable"><code>STD</code></em> is the zone abbreviation to be used18 for standard time.19 </p></li><li class="listitem"><p>20 <em class="replaceable"><code>offset</code></em> is the zone's standard-time offset21 from UTC.22 </p></li><li class="listitem"><p>23 <em class="replaceable"><code>DST</code></em> is the zone abbreviation to be used24 for daylight-savings time. If this field and the following ones are25 omitted, the zone uses a fixed UTC offset with no daylight-savings26 rule.27 </p></li><li class="listitem"><p>28 <em class="replaceable"><code>dstoffset</code></em> is the daylight-savings offset29 from UTC. This field is typically omitted, since it defaults to one30 hour less than the standard-time <em class="replaceable"><code>offset</code></em>,31 which is usually the right thing.32 </p></li><li class="listitem"><p>33 <em class="replaceable"><code>rule</code></em> defines the rule for when daylight34 savings is in effect, as described below.35 </p></li></ul></div><p>36 </p><p>37 In this syntax, a zone abbreviation can be a string of letters, such38 as <code class="literal">EST</code>, or an arbitrary string surrounded by angle39 brackets, such as <code class="literal"><UTC-05></code>.40 Note that the zone abbreviations given here are only used for output,41 and even then only in some timestamp output formats. The zone42 abbreviations recognized in timestamp input are determined as explained43 in <a class="xref" href="datetime-config-files.html" title="B.4. Date/Time Configuration Files">Section B.4</a>.44 </p><p>45 The offset fields specify the hours, and optionally minutes and seconds,46 difference from UTC. They have the format47 <em class="replaceable"><code>hh</code></em>[<span class="optional"><code class="literal">:</code><em class="replaceable"><code>mm</code></em>[<span class="optional"><code class="literal">:</code><em class="replaceable"><code>ss</code></em></span>]</span>]48 optionally with a leading sign (<code class="literal">+</code>49 or <code class="literal">-</code>). The positive sign is used for50 zones <span class="emphasis"><em>west</em></span> of Greenwich. (Note that this is the51 opposite of the ISO-8601 sign convention used elsewhere in52 <span class="productname">PostgreSQL</span>.) <em class="replaceable"><code>hh</code></em>53 can have one or two digits; <em class="replaceable"><code>mm</code></em>54 and <em class="replaceable"><code>ss</code></em> (if used) must have two.55 </p><p>56 The daylight-savings transition <em class="replaceable"><code>rule</code></em> has the57 format58</p><pre class="synopsis">59<em class="replaceable"><code>dstdate</code></em> [<span class="optional"> <code class="literal">/</code> <em class="replaceable"><code>dsttime</code></em> </span>] <code class="literal">,</code> <em class="replaceable"><code>stddate</code></em> [<span class="optional"> <code class="literal">/</code> <em class="replaceable"><code>stdtime</code></em> </span>]60</pre><p>61 (As before, spaces should not be included in practice.)62 The <em class="replaceable"><code>dstdate</code></em>63 and <em class="replaceable"><code>dsttime</code></em> fields define when daylight-savings64 time starts, while <em class="replaceable"><code>stddate</code></em>65 and <em class="replaceable"><code>stdtime</code></em> define when standard time66 starts. (In some cases, notably in zones south of the equator, the67 former might be later in the year than the latter.) The date fields68 have one of these formats:69 </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><em class="replaceable"><code>n</code></em></span></dt><dd><p>70 A plain integer denotes a day of the year, counting from zero to71 364, or to 365 in leap years.72 </p></dd><dt><span class="term"><code class="literal">J</code><em class="replaceable"><code>n</code></em></span></dt><dd><p>73 In this form, <em class="replaceable"><code>n</code></em> counts from 1 to 365,74 and February 29 is not counted even if it is present. (Thus, a75 transition occurring on February 29 could not be specified this76 way. However, days after February have the same numbers whether77 it's a leap year or not, so that this form is usually more useful78 than the plain-integer form for transitions on fixed dates.)79 </p></dd><dt><span class="term"><code class="literal">M</code><em class="replaceable"><code>m</code></em><code class="literal">.</code><em class="replaceable"><code>n</code></em><code class="literal">.</code><em class="replaceable"><code>d</code></em></span></dt><dd><p>80 This form specifies a transition that always happens during the same81 month and on the same day of the week. <em class="replaceable"><code>m</code></em>82 identifies the month, from 1 to 12. <em class="replaceable"><code>n</code></em>83 specifies the <em class="replaceable"><code>n</code></em>'th occurrence of the84 weekday identified by <em class="replaceable"><code>d</code></em>.85 <em class="replaceable"><code>n</code></em> is a number between 1 and 4, or 586 meaning the last occurrence of that weekday in the month (which87 could be the fourth or the fifth). <em class="replaceable"><code>d</code></em> is88 a number between 0 and 6, with 0 indicating Sunday.89 For example, <code class="literal">M3.2.0</code> means <span class="quote">“<span class="quote">the second90 Sunday in March</span>”</span>.91 </p></dd></dl></div><p>92 </p><div class="note"><h3 class="title">Note</h3><p>93 The <code class="literal">M</code> format is sufficient to describe many common94 daylight-savings transition laws. But note that none of these variants95 can deal with daylight-savings law changes, so in practice the96 historical data stored for named time zones (in the IANA time zone97 database) is necessary to interpret past time stamps correctly.98 </p></div><p>99 The time fields in a transition rule have the same format as the offset100 fields described previously, except that they cannot contain signs.101 They define the current local time at which the change to the other102 time occurs. If omitted, they default to <code class="literal">02:00:00</code>.103 </p><p>104 If a daylight-savings abbreviation is given but the105 transition <em class="replaceable"><code>rule</code></em> field is omitted,106 the fallback behavior is to use the107 rule <code class="literal">M3.2.0,M11.1.0</code>, which corresponds to USA108 practice as of 2020 (that is, spring forward on the second Sunday of109 March, fall back on the first Sunday of November, both transitions110 occurring at 2AM prevailing time). Note that this rule does not111 give correct USA transition dates for years before 2007.112 </p><p>113 As an example, <code class="literal">CET-1CEST,M3.5.0,M10.5.0/3</code> describes114 current (as of 2020) timekeeping practice in Paris. This specification115 says that standard time has the abbreviation <code class="literal">CET</code> and116 is one hour ahead (east) of UTC; daylight savings time has the117 abbreviation <code class="literal">CEST</code> and is implicitly two hours ahead118 of UTC; daylight savings time begins on the last Sunday in March at 2AM119 CET and ends on the last Sunday in October at 3AM CEST.120 </p><p>121 The four timezone names <code class="literal">EST5EDT</code>,122 <code class="literal">CST6CDT</code>, <code class="literal">MST7MDT</code>,123 and <code class="literal">PST8PDT</code> look like they are POSIX zone124 specifications. However, they actually are treated as named time zones125 because (for historical reasons) there are files by those names in the126 IANA time zone database. The practical implication of this is that127 these zone names will produce valid historical USA daylight-savings128 transitions, even when a plain POSIX specification would not.129 </p><p>130 One should be wary that it is easy to misspell a POSIX-style time zone131 specification, since there is no check on the reasonableness of the132 zone abbreviation(s). For example, <code class="literal">SET TIMEZONE TO133 FOOBAR0</code> will work, leaving the system effectively using a134 rather peculiar abbreviation for UTC.135 </p></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="datetime-config-files.html" title="B.4. Date/Time Configuration Files">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="datetime-appendix.html" title="Appendix B. Date/Time Support">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="datetime-units-history.html" title="B.6. History of Units">Next</a></td></tr><tr><td width="40%" align="left" valign="top">B.4. Date/Time Configuration Files </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> B.6. History of Units</td></tr></table></div></body></html>