Debian Codegraph @

doc/src/sgml/datetime.sgml

raw
1 <!-- doc/src/sgml/datetime.sgml -->
2
3 <appendix id="datetime-appendix">
4 <title>Date/Time Support</title>
5
6 <para>
7 <productname>PostgreSQL</productname> uses an internal heuristic
8 parser for all date/time input support. Dates and times are input as
9 strings, and are broken up into distinct fields with a preliminary
10 determination of what kind of information can be in the
11 field. Each field is interpreted and either assigned a numeric
12 value, ignored, or rejected.
13 The parser contains internal lookup tables for all textual fields,
14 including months, days of the week, and time zones.
15 </para>
16
17 <para>
18 This appendix includes information on the content of these
19 lookup tables and describes the steps used by the parser to decode
20 dates and times.
21 </para>
22
23 <sect1 id="datetime-input-rules">
24 <title>Date/Time Input Interpretation</title>
25
26 <para>
27 Date/time input strings are decoded using the following procedure.
28 </para>
29
30 <procedure>
31 <step>
32 <para>
33 Break the input string into tokens and categorize each token as
34 a string, time, time zone, or number.
35 </para>
36
37 <substeps>
38 <step>
39 <para>
40 If the numeric token contains a colon (<literal>:</literal>), this is
41 a time string. Include all subsequent digits and colons.
42 </para>
43 </step>
44
45 <step>
46 <para>
47 If the numeric token contains a dash (<literal>-</literal>), slash
48 (<literal>/</literal>), or two or more dots (<literal>.</literal>), this is
49 a date string which might have a text month. If a date token has
50 already been seen, it is instead interpreted as a time zone
51 name (e.g., <literal>America/New_York</literal>).
52 </para>
53 </step>
54
55 <step>
56 <para>
57 If the token is numeric only, then it is either a single field
58 or an ISO 8601 concatenated date (e.g.,
59 <literal>19990113</literal> for January 13, 1999) or time
60 (e.g., <literal>141516</literal> for 14:15:16).
61 </para>
62 </step>
63
64 <step>
65 <para>
66 If the token starts with a plus (<literal>+</literal>) or minus
67 (<literal>-</literal>), then it is either a numeric time zone or a special
68 field.
69 </para>
70 </step>
71 </substeps>
72 </step>
73
74 <step>
75 <para>
76 If the token is an alphabetic string, match up with possible strings:
77 </para>
78
79 <substeps>
80 <step>
81 <para>
82 See if the token matches any known time zone abbreviation.
83 These abbreviations are determined by the configuration settings
84 described in <xref linkend="datetime-config-files"/>.
85 </para>
86 </step>
87
88 <step>
89 <para>
90 If not found, search an internal table to match
91 the token as either a special string (e.g., <literal>today</literal>),
92 day (e.g., <literal>Thursday</literal>),
93 month (e.g., <literal>January</literal>),
94 or noise word (e.g., <literal>at</literal>, <literal>on</literal>).
95 </para>
96 </step>
97
98 <step>
99 <para>
100 If still not found, throw an error.
101 </para>
102 </step>
103 </substeps>
104 </step>
105
106 <step>
107 <para>
108 When the token is a number or number field:
109 </para>
110
111 <substeps>
112 <step>
113 <para>
114 If there are eight or six digits,
115 and if no other date fields have been previously read, then interpret
116 as a <quote>concatenated date</quote> (e.g.,
117 <literal>19990118</literal> or <literal>990118</literal>).
118 The interpretation is <literal>YYYYMMDD</literal> or <literal>YYMMDD</literal>.
119 </para>
120 </step>
121
122 <step>
123 <para>
124 If the token is three digits
125 and a year has already been read, then interpret as day of year.
126 </para>
127 </step>
128
129 <step>
130 <para>
131 If four or six digits and a year has already been read, then
132 interpret as a time (<literal>HHMM</literal> or <literal>HHMMSS</literal>).
133 </para>
134 </step>
135
136 <step>
137 <para>
138 If three or more digits and no date fields have yet been found,
139 interpret as a year (this forces yy-mm-dd ordering of the remaining
140 date fields).
141 </para>
142 </step>
143
144 <step>
145 <para>
146 Otherwise the date field ordering is assumed to follow the
147 <varname>DateStyle</varname> setting: mm-dd-yy, dd-mm-yy, or yy-mm-dd.
148 Throw an error if a month or day field is found to be out of range.
149 </para>
150 </step>
151 </substeps>
152 </step>
153
154 <step>
155 <para>
156 If BC has been specified, negate the year and add one for
157 internal storage. (There is no year zero in the Gregorian
158 calendar, so numerically 1 BC becomes year zero.)
159 </para>
160 </step>
161
162 <step>
163 <para>
164 If BC was not specified, and if the year field was two digits in length,
165 then adjust the year to four digits. If the field is less than 70, then
166 add 2000, otherwise add 1900.
167
168 <tip>
169 <para>
170 Gregorian years AD 1&ndash;99 can be entered by using 4 digits with leading
171 zeros (e.g., <literal>0099</literal> is AD 99).
172 </para>
173 </tip>
174 </para>
175 </step>
176 </procedure>
177 </sect1>
178
179
180 <sect1 id="datetime-invalid-input">
181 <title>Handling of Invalid or Ambiguous Timestamps</title>
182
183 <para>
184 Ordinarily, if a date/time string is syntactically valid but contains
185 out-of-range field values, an error will be thrown. For example, input
186 specifying the 31st of February will be rejected.
187 </para>
188
189 <para>
190 During a daylight-savings-time transition, it is possible for a
191 seemingly valid timestamp string to represent a nonexistent or ambiguous
192 timestamp. Such cases are not rejected; the ambiguity is resolved by
193 determining which UTC offset to apply. For example, supposing that the
194 <xref linkend="guc-timezone"/> parameter is set
195 to <literal>America/New_York</literal>, consider
196 <programlisting>
197 =&gt; SELECT '2018-03-11 02:30'::timestamptz;
198 timestamptz
199 ------------------------
200 2018-03-11 03:30:00-04
201 (1 row)
202 </programlisting>
203 Because that day was a spring-forward transition date in that time zone,
204 there was no civil time instant 2:30AM; clocks jumped forward from 2AM
205 EST to 3AM EDT. <productname>PostgreSQL</productname> interprets the
206 given time as if it were standard time (UTC-5), which then renders as
207 3:30AM EDT (UTC-4).
208 </para>
209
210 <para>
211 Conversely, consider the behavior during a fall-back transition:
212 <programlisting>
213 =&gt; SELECT '2018-11-04 01:30'::timestamptz;
214 timestamptz
215 ------------------------
216 2018-11-04 01:30:00-05
217 (1 row)
218 </programlisting>
219 On that date, there were two possible interpretations of 1:30AM; there
220 was 1:30AM EDT, and then an hour later after clocks jumped back from
221 2AM EDT to 1AM EST, there was 1:30AM EST.
222 Again, <productname>PostgreSQL</productname> interprets the given time
223 as if it were standard time (UTC-5). We can force the other
224 interpretation by specifying daylight-savings time:
225 <programlisting>
226 =&gt; SELECT '2018-11-04 01:30 EDT'::timestamptz;
227 timestamptz
228 ------------------------
229 2018-11-04 01:30:00-04
230 (1 row)
231 </programlisting>
232 </para>
233
234 <para>
235 The precise rule that is applied in such cases is that an invalid
236 timestamp that appears to fall within a jump-forward daylight savings
237 transition is assigned the UTC offset that prevailed in the time zone
238 just before the transition, while an ambiguous timestamp that could fall
239 on either side of a jump-back transition is assigned the UTC offset that
240 prevailed just after the transition. In most time zones this is
241 equivalent to saying that <quote>the standard-time interpretation is
242 preferred when in doubt</quote>.
243 </para>
244
245 <para>
246 In all cases, the UTC offset associated with a timestamp can be
247 specified explicitly, using either a numeric UTC offset or a time zone
248 abbreviation that corresponds to a fixed UTC offset. The rule just
249 given applies only when it is necessary to infer a UTC offset for a time
250 zone in which the offset varies.
251 </para>
252 </sect1>
253
254
255 <sect1 id="datetime-keywords">
256 <title>Date/Time Key Words</title>
257
258 <para>
259 <xref linkend="datetime-month-table"/> shows the tokens that are
260 recognized as names of months.
261 </para>
262
263 <table id="datetime-month-table">
264 <title>Month Names</title>
265 <tgroup cols="2">
266 <thead>
267 <row>
268 <entry>Month</entry>
269 <entry>Abbreviations</entry>
270 </row>
271 </thead>
272 <tbody>
273 <row>
274 <entry>January</entry>
275 <entry>Jan</entry>
276 </row>
277 <row>
278 <entry>February</entry>
279 <entry>Feb</entry>
280 </row>
281 <row>
282 <entry>March</entry>
283 <entry>Mar</entry>
284 </row>
285 <row>
286 <entry>April</entry>
287 <entry>Apr</entry>
288 </row>
289 <row>
290 <entry>May</entry>
291 <entry></entry>
292 </row>
293 <row>
294 <entry>June</entry>
295 <entry>Jun</entry>
296 </row>
297 <row>
298 <entry>July</entry>
299 <entry>Jul</entry>
300 </row>
301 <row>
302 <entry>August</entry>
303 <entry>Aug</entry>
304 </row>
305 <row>
306 <entry>September</entry>
307 <entry>Sep, Sept</entry>
308 </row>
309 <row>
310 <entry>October</entry>
311 <entry>Oct</entry>
312 </row>
313 <row>
314 <entry>November</entry>
315 <entry>Nov</entry>
316 </row>
317 <row>
318 <entry>December</entry>
319 <entry>Dec</entry>
320 </row>
321 </tbody>
322 </tgroup>
323 </table>
324
325 <para>
326 <xref linkend="datetime-dow-table"/> shows the tokens that are
327 recognized as names of days of the week.
328 </para>
329
330 <table id="datetime-dow-table">
331 <title>Day of the Week Names</title>
332 <tgroup cols="2">
333 <thead>
334 <row>
335 <entry>Day</entry>
336 <entry>Abbreviations</entry>
337 </row>
338 </thead>
339 <tbody>
340 <row>
341 <entry>Sunday</entry>
342 <entry>Sun</entry>
343 </row>
344 <row>
345 <entry>Monday</entry>
346 <entry>Mon</entry>
347 </row>
348 <row>
349 <entry>Tuesday</entry>
350 <entry>Tue, Tues</entry>
351 </row>
352 <row>
353 <entry>Wednesday</entry>
354 <entry>Wed, Weds</entry>
355 </row>
356 <row>
357 <entry>Thursday</entry>
358 <entry>Thu, Thur, Thurs</entry>
359 </row>
360 <row>
361 <entry>Friday</entry>
362 <entry>Fri</entry>
363 </row>
364 <row>
365 <entry>Saturday</entry>
366 <entry>Sat</entry>
367 </row>
368 </tbody>
369 </tgroup>
370 </table>
371
372 <para>
373 <xref linkend="datetime-mod-table"/> shows the tokens that serve
374 various modifier purposes.
375 </para>
376
377 <table id="datetime-mod-table">
378 <title>Date/Time Field Modifiers</title>
379 <tgroup cols="2">
380 <thead>
381 <row>
382 <entry>Identifier</entry>
383 <entry>Description</entry>
384 </row>
385 </thead>
386 <tbody>
387 <row>
388 <entry><literal>AM</literal></entry>
389 <entry>Time is before 12:00</entry>
390 </row>
391 <row>
392 <entry><literal>AT</literal></entry>
393 <entry>Ignored</entry>
394 </row>
395 <row>
396 <entry><literal>JULIAN</literal>, <literal>JD</literal>, <literal>J</literal></entry>
397 <entry>Next field is Julian Date</entry>
398 </row>
399 <row>
400 <entry><literal>ON</literal></entry>
401 <entry>Ignored</entry>
402 </row>
403 <row>
404 <entry><literal>PM</literal></entry>
405 <entry>Time is on or after 12:00</entry>
406 </row>
407 <row>
408 <entry><literal>T</literal></entry>
409 <entry>Next field is time</entry>
410 </row>
411 </tbody>
412 </tgroup>
413 </table>
414 </sect1>
415
416 <sect1 id="datetime-config-files">
417 <title>Date/Time Configuration Files</title>
418
419 <indexterm>
420 <primary>time zone</primary>
421 <secondary>input abbreviations</secondary>
422 </indexterm>
423
424 <para>
425 Since timezone abbreviations are not well standardized,
426 <productname>PostgreSQL</productname> provides a means to customize
427 the set of abbreviations accepted in datetime input.
428 There are two sources for these abbreviations:
429
430 <orderedlist>
431 <listitem>
432 <para>
433 The <xref linkend="guc-timezone"/> run-time parameter is usually
434 set to the name of an entry in the IANA time zone database.
435 If that zone has widely-used zone abbreviations, they will appear
436 in the IANA data, and <productname>PostgreSQL</productname> will
437 preferentially recognize those abbreviations with the meanings
438 given in the IANA data.
439 For example, if <varname>timezone</varname> is set
440 to <literal>America/New_York</literal> then <literal>EST</literal>
441 will be understood as UTC-5 and <literal>EDT</literal> will be
442 understood as UTC-4. (These IANA abbreviations will also be used
443 in datetime output, if <xref linkend="guc-datestyle"/> is set to a
444 style that prefers non-numeric zone abbreviations.)
445 </para>
446 </listitem>
447
448 <listitem>
449 <para>
450 If an abbreviation is not found in the current IANA time zone,
451 it is sought in the list specified by the
452 <xref linkend="guc-timezone-abbreviations"/> run-time parameter.
453 The <varname>timezone_abbreviations</varname> list is primarily
454 useful for allowing datetime input to recognize abbreviations for
455 time zones other than the current zone. (These abbreviations will
456 not be used in datetime output.)
457 </para>
458 </listitem>
459 </orderedlist>
460 </para>
461
462 <para>
463 While the <varname>timezone_abbreviations</varname> parameter
464 can be altered by any database user, the possible values for it
465 are under the control of the database administrator &mdash; they
466 are in fact names of configuration files stored in
467 <filename>.../share/timezonesets/</filename> of the installation directory.
468 By adding or altering files in that directory, the administrator
469 can set local policy for timezone abbreviations.
470 </para>
471
472 <para>
473 <varname>timezone_abbreviations</varname> can be set to any file name
474 found in <filename>.../share/timezonesets/</filename>, if the file's name
475 is entirely alphabetic. (The prohibition against non-alphabetic
476 characters in <varname>timezone_abbreviations</varname> prevents reading
477 files outside the intended directory, as well as reading editor
478 backup files and other extraneous files.)
479 </para>
480
481 <para>
482 A timezone abbreviation file can contain blank lines and comments
483 beginning with <literal>#</literal>. Non-comment lines must have one of
484 these formats:
485
486 <synopsis>
487 <replaceable>zone_abbreviation</replaceable> <replaceable>offset</replaceable>
488 <replaceable>zone_abbreviation</replaceable> <replaceable>offset</replaceable> D
489 <replaceable>zone_abbreviation</replaceable> <replaceable>time_zone_name</replaceable>
490 @INCLUDE <replaceable>file_name</replaceable>
491 @OVERRIDE
492 </synopsis>
493 </para>
494
495 <para>
496 A <replaceable>zone_abbreviation</replaceable> is just the abbreviation
497 being defined. An <replaceable>offset</replaceable> is an integer giving
498 the equivalent offset in seconds from UTC, positive being east from
499 Greenwich and negative being west. For example, -18000 would be five
500 hours west of Greenwich, or North American east coast standard time.
501 <literal>D</literal> indicates that the zone name represents local
502 daylight-savings time rather than standard time.
503 </para>
504
505 <para>
506 Alternatively, a <replaceable>time_zone_name</replaceable> can be given, referencing
507 a zone name defined in the IANA timezone database. The zone's definition
508 is consulted to see whether the abbreviation is or has been in use in
509 that zone, and if so, the appropriate meaning is used &mdash; that is,
510 the meaning that was currently in use at the timestamp whose value is
511 being determined, or the meaning in use immediately before that if it
512 wasn't current at that time, or the oldest meaning if it was used only
513 after that time. This behavior is essential for dealing with
514 abbreviations whose meaning has historically varied. It is also allowed
515 to define an abbreviation in terms of a zone name in which that
516 abbreviation does not appear; then using the abbreviation is just
517 equivalent to writing out the zone name.
518 </para>
519
520 <tip>
521 <para>
522 Using a simple integer <replaceable>offset</replaceable> is preferred
523 when defining an abbreviation whose offset from UTC has never changed,
524 as such abbreviations are much cheaper to process than those that
525 require consulting a time zone definition.
526 </para>
527 </tip>
528
529 <para>
530 The <literal>@INCLUDE</literal> syntax allows inclusion of another file in the
531 <filename>.../share/timezonesets/</filename> directory. Inclusion can be nested,
532 to a limited depth.
533 </para>
534
535 <para>
536 The <literal>@OVERRIDE</literal> syntax indicates that subsequent entries in the
537 file can override previous entries (typically, entries obtained from
538 included files). Without this, conflicting definitions of the same
539 timezone abbreviation are considered an error.
540 </para>
541
542 <para>
543 In an unmodified installation, the file <filename>Default</filename> contains
544 all the non-conflicting time zone abbreviations for most of the world.
545 Additional files <filename>Australia</filename> and <filename>India</filename> are
546 provided for those regions: these files first include the
547 <literal>Default</literal> file and then add or modify abbreviations as needed.
548 </para>
549
550 <para>
551 For reference purposes, a standard installation also contains files
552 <filename>Africa.txt</filename>, <filename>America.txt</filename>, etc., containing
553 information about every time zone abbreviation known to be in use
554 according to the IANA timezone database. The zone name
555 definitions found in these files can be copied and pasted into a custom
556 configuration file as needed. Note that these files cannot be directly
557 referenced as <varname>timezone_abbreviations</varname> settings, because of
558 the dot embedded in their names.
559 </para>
560
561 <note>
562 <para>
563 If an error occurs while reading the time zone abbreviation set, no new
564 value is applied and the old set is kept. If the error occurs while
565 starting the database, startup fails.
566 </para>
567 </note>
568
569 <caution>
570 <para>
571 Time zone abbreviations defined in the configuration file override
572 non-timezone meanings built into <productname>PostgreSQL</productname>.
573 For example, the <filename>Australia</filename> configuration file defines
574 <literal>SAT</literal> (for South Australian Standard Time). When this
575 file is active, <literal>SAT</literal> will not be recognized as an abbreviation
576 for Saturday.
577 </para>
578 </caution>
579
580 <caution>
581 <para>
582 If you modify files in <filename>.../share/timezonesets/</filename>,
583 it is up to you to make backups &mdash; a normal database dump
584 will not include this directory.
585 </para>
586 </caution>
587
588 </sect1>
589
590 <sect1 id="datetime-posix-timezone-specs">
591 <title><acronym>POSIX</acronym> Time Zone Specifications</title>
592
593 <indexterm zone="datetime-posix-timezone-specs">
594 <primary>time zone</primary>
595 <secondary><acronym>POSIX</acronym>-style specification</secondary>
596 </indexterm>
597
598 <para>
599 <productname>PostgreSQL</productname> can accept time zone specifications
600 that are written according to the <acronym>POSIX</acronym> standard's rules
601 for the <varname>TZ</varname> environment
602 variable. <acronym>POSIX</acronym> time zone specifications are
603 inadequate to deal with the complexity of real-world time zone history,
604 but there are sometimes reasons to use them.
605 </para>
606
607 <para>
608 A POSIX time zone specification has the form
609 <synopsis>
610 <replaceable>STD</replaceable> <replaceable>offset</replaceable> <optional> <replaceable>DST</replaceable> <optional> <replaceable>dstoffset</replaceable> </optional> <optional> , <replaceable>rule</replaceable> </optional> </optional>
611 </synopsis>
612 (For readability, we show spaces between the fields, but spaces should
613 not be used in practice.) The fields are:
614 <itemizedlist>
615 <listitem>
616 <para>
617 <replaceable>STD</replaceable> is the zone abbreviation to be used
618 for standard time.
619 </para>
620 </listitem>
621 <listitem>
622 <para>
623 <replaceable>offset</replaceable> is the zone's standard-time offset
624 from UTC.
625 </para>
626 </listitem>
627 <listitem>
628 <para>
629 <replaceable>DST</replaceable> is the zone abbreviation to be used
630 for daylight-savings time. If this field and the following ones are
631 omitted, the zone uses a fixed UTC offset with no daylight-savings
632 rule.
633 </para>
634 </listitem>
635 <listitem>
636 <para>
637 <replaceable>dstoffset</replaceable> is the daylight-savings offset
638 from UTC. This field is typically omitted, since it defaults to one
639 hour less than the standard-time <replaceable>offset</replaceable>,
640 which is usually the right thing.
641 </para>
642 </listitem>
643 <listitem>
644 <para>
645 <replaceable>rule</replaceable> defines the rule for when daylight
646 savings is in effect, as described below.
647 </para>
648 </listitem>
649 </itemizedlist>
650 </para>
651
652 <para>
653 In this syntax, a zone abbreviation can be a string of letters, such
654 as <literal>EST</literal>, or an arbitrary string surrounded by angle
655 brackets, such as <literal>&lt;UTC-05&gt;</literal>.
656 Note that the zone abbreviations given here are only used for output,
657 and even then only in some timestamp output formats. The zone
658 abbreviations recognized in timestamp input are determined as explained
659 in <xref linkend="datetime-config-files"/>.
660 </para>
661
662 <para>
663 The offset fields specify the hours, and optionally minutes and seconds,
664 difference from UTC. They have the format
665 <replaceable>hh</replaceable><optional><literal>:</literal><replaceable>mm</replaceable><optional><literal>:</literal><replaceable>ss</replaceable></optional></optional>
666 optionally with a leading sign (<literal>+</literal>
667 or <literal>-</literal>). The positive sign is used for
668 zones <emphasis>west</emphasis> of Greenwich. (Note that this is the
669 opposite of the ISO-8601 sign convention used elsewhere in
670 <productname>PostgreSQL</productname>.) <replaceable>hh</replaceable>
671 can have one or two digits; <replaceable>mm</replaceable>
672 and <replaceable>ss</replaceable> (if used) must have two.
673 </para>
674
675 <para>
676 The daylight-savings transition <replaceable>rule</replaceable> has the
677 format
678 <synopsis>
679 <replaceable>dstdate</replaceable> <optional> <literal>/</literal> <replaceable>dsttime</replaceable> </optional> <literal>,</literal> <replaceable>stddate</replaceable> <optional> <literal>/</literal> <replaceable>stdtime</replaceable> </optional>
680 </synopsis>
681 (As before, spaces should not be included in practice.)
682 The <replaceable>dstdate</replaceable>
683 and <replaceable>dsttime</replaceable> fields define when daylight-savings
684 time starts, while <replaceable>stddate</replaceable>
685 and <replaceable>stdtime</replaceable> define when standard time
686 starts. (In some cases, notably in zones south of the equator, the
687 former might be later in the year than the latter.) The date fields
688 have one of these formats:
689 <variablelist>
690 <varlistentry>
691 <term><replaceable>n</replaceable></term>
692 <listitem>
693 <para>
694 A plain integer denotes a day of the year, counting from zero to
695 364, or to 365 in leap years.
696 </para>
697 </listitem>
698 </varlistentry>
699 <varlistentry>
700 <term><literal>J</literal><replaceable>n</replaceable></term>
701 <listitem>
702 <para>
703 In this form, <replaceable>n</replaceable> counts from 1 to 365,
704 and February 29 is not counted even if it is present. (Thus, a
705 transition occurring on February 29 could not be specified this
706 way. However, days after February have the same numbers whether
707 it's a leap year or not, so that this form is usually more useful
708 than the plain-integer form for transitions on fixed dates.)
709 </para>
710 </listitem>
711 </varlistentry>
712 <varlistentry>
713 <term><literal>M</literal><replaceable>m</replaceable><literal>.</literal><replaceable>n</replaceable><literal>.</literal><replaceable>d</replaceable></term>
714 <listitem>
715 <para>
716 This form specifies a transition that always happens during the same
717 month and on the same day of the week. <replaceable>m</replaceable>
718 identifies the month, from 1 to 12. <replaceable>n</replaceable>
719 specifies the <replaceable>n</replaceable>'th occurrence of the
720 weekday identified by <replaceable>d</replaceable>.
721 <replaceable>n</replaceable> is a number between 1 and 4, or 5
722 meaning the last occurrence of that weekday in the month (which
723 could be the fourth or the fifth). <replaceable>d</replaceable> is
724 a number between 0 and 6, with 0 indicating Sunday.
725 For example, <literal>M3.2.0</literal> means <quote>the second
726 Sunday in March</quote>.
727 </para>
728 </listitem>
729 </varlistentry>
730 </variablelist>
731 </para>
732
733 <note>
734 <para>
735 The <literal>M</literal> format is sufficient to describe many common
736 daylight-savings transition laws. But note that none of these variants
737 can deal with daylight-savings law changes, so in practice the
738 historical data stored for named time zones (in the IANA time zone
739 database) is necessary to interpret past time stamps correctly.
740 </para>
741 </note>
742
743 <para>
744 The time fields in a transition rule have the same format as the offset
745 fields described previously, except that they cannot contain signs.
746 They define the current local time at which the change to the other
747 time occurs. If omitted, they default to <literal>02:00:00</literal>.
748 </para>
749
750 <para>
751 If a daylight-savings abbreviation is given but the
752 transition <replaceable>rule</replaceable> field is omitted,
753 the fallback behavior is to use the
754 rule <literal>M3.2.0,M11.1.0</literal>, which corresponds to USA
755 practice as of 2020 (that is, spring forward on the second Sunday of
756 March, fall back on the first Sunday of November, both transitions
757 occurring at 2AM prevailing time). Note that this rule does not
758 give correct USA transition dates for years before 2007.
759 </para>
760
761 <para>
762 As an example, <literal>CET-1CEST,M3.5.0,M10.5.0/3</literal> describes
763 the current (as of 2020) timekeeping practice in Paris. This specification
764 says that standard time has the abbreviation <literal>CET</literal> and
765 is one hour ahead (east) of UTC; daylight savings time has the
766 abbreviation <literal>CEST</literal> and is implicitly two hours ahead
767 of UTC; daylight savings time begins on the last Sunday in March at 2AM
768 CET and ends on the last Sunday in October at 3AM CEST.
769 </para>
770
771 <para>
772 The four timezone names <literal>EST5EDT</literal>,
773 <literal>CST6CDT</literal>, <literal>MST7MDT</literal>,
774 and <literal>PST8PDT</literal> look like they are POSIX zone
775 specifications. However, they actually are treated as named time zones
776 because (for historical reasons) there are files by those names in the
777 IANA time zone database. The practical implication of this is that
778 these zone names will produce valid historical USA daylight-savings
779 transitions, even when a plain POSIX specification would not.
780 </para>
781
782 <para>
783 One should be wary that it is easy to misspell a POSIX-style time zone
784 specification, since there is no check on the reasonableness of the
785 zone abbreviation(s). For example, <literal>SET TIMEZONE TO
786 FOOBAR0</literal> will work, leaving the system effectively using a
787 rather peculiar abbreviation for UTC.
788 </para>
789
790 </sect1>
791
792 <sect1 id="datetime-units-history">
793 <title>History of Units</title>
794
795 <indexterm zone="datetime-units-history">
796 <primary>Gregorian calendar</primary>
797 </indexterm>
798
799 <para>
800 The SQL standard states that <quote>Within the definition of a
801 <quote>datetime literal</quote>, the <quote>datetime
802 values</quote> are constrained by the natural rules for dates and
803 times according to the Gregorian calendar</quote>.
804 <productname>PostgreSQL</productname> follows the SQL
805 standard's lead by counting dates exclusively in the Gregorian
806 calendar, even for years before that calendar was in use.
807 This rule is known as the <firstterm>proleptic Gregorian calendar</firstterm>.
808 </para>
809
810 <para>
811 The Julian calendar was introduced by Julius Caesar in 45 BC.
812 It was in common use in the Western world
813 until the year 1582, when countries started changing to the Gregorian
814 calendar. In the Julian calendar, the tropical year is
815 approximated as 365 1/4 days = 365.25 days. This gives an error of
816 about 1 day in 128 years.
817 </para>
818
819 <para>
820 The accumulating calendar error prompted
821 Pope Gregory XIII to reform the calendar in accordance with
822 instructions from the Council of Trent.
823 In the Gregorian calendar, the tropical year is approximated as
824 365 + 97 / 400 days = 365.2425 days. Thus it takes approximately 3300
825 years for the tropical year to shift one day with respect to the
826 Gregorian calendar.
827 </para>
828
829 <para>
830 The approximation 365+97/400 is achieved by having 97 leap years
831 every 400 years, using the following rules:
832
833 <simplelist>
834 <member>
835 Every year divisible by 4 is a leap year.
836 </member>
837 <member>
838 However, every year divisible by 100 is not a leap year.
839 </member>
840 <member>
841 However, every year divisible by 400 is a leap year after all.
842 </member>
843 </simplelist>
844
845 So, 1700, 1800, 1900, 2100, and 2200 are not leap years. But 1600,
846 2000, and 2400 are leap years.
847
848 By contrast, in the older Julian calendar all years divisible by 4 are leap
849 years.
850 </para>
851
852 <para>
853 The papal bull of February 1582 decreed that 10 days should be dropped
854 from October 1582 so that 15 October should follow immediately after
855 4 October.
856 This was observed in Italy, Poland, Portugal, and Spain. Other Catholic
857 countries followed shortly after, but Protestant countries were
858 reluctant to change, and the Greek Orthodox countries didn't change
859 until the start of the 20th century.
860
861 The reform was observed by Great Britain and its dominions (including what
862 is now the USA) in 1752.
863 Thus 2 September 1752 was followed by 14 September 1752.
864
865 This is why Unix systems that have the <command>cal</command> program
866 produce the following:
867
868 <screen>
869 $ <userinput>cal 9 1752</userinput>
870 September 1752
871 S M Tu W Th F S
872 1 2 14 15 16
873 17 18 19 20 21 22 23
874 24 25 26 27 28 29 30
875 </screen>
876
877 But, of course, this calendar is only valid for Great Britain and
878 dominions, not other places.
879 Since it would be difficult and confusing to try to track the actual
880 calendars that were in use in various places at various times,
881 <productname>PostgreSQL</productname> does not try, but rather follows the Gregorian
882 calendar rules for all dates, even though this method is not historically
883 accurate.
884 </para>
885
886 <para>
887 Different calendars have been developed in various parts of the
888 world, many predating the Gregorian system.
889
890 For example,
891 the beginnings of the Chinese calendar can be traced back to the 14th
892 century BC. Legend has it that the Emperor Huangdi invented that
893 calendar in 2637 BC.
894
895 The People's Republic of China uses the Gregorian calendar
896 for civil purposes. The Chinese calendar is used for determining
897 festivals.
898 </para>
899
900 </sect1>
901
902 <sect1 id="datetime-julian-dates">
903 <title>Julian Dates</title>
904
905 <indexterm zone="datetime-julian-dates">
906 <primary>Julian date</primary>
907 </indexterm>
908
909 <para>
910 The <firstterm>Julian Date</firstterm> system is a method for
911 numbering days. It is
912 unrelated to the Julian calendar, though it is confusingly
913 named similarly to that calendar.
914 The Julian Date system was invented by the French scholar
915 Joseph Justus Scaliger (1540&ndash;1609)
916 and probably takes its name from Scaliger's father,
917 the Italian scholar Julius Caesar Scaliger (1484&ndash;1558).
918 </para>
919
920 <para>
921 In the Julian Date system, each day has a sequential number, starting
922 from JD 0 (which is sometimes called <emphasis>the</emphasis> Julian Date).
923 JD 0 corresponds to 1 January 4713 BC in the Julian calendar, or
924 24 November 4714 BC in the Gregorian calendar. Julian Date counting
925 is most often used by astronomers for labeling their nightly observations,
926 and therefore a date runs from noon UTC to the next noon UTC, rather than
927 from midnight to midnight: JD 0 designates the 24 hours from noon UTC on
928 24 November 4714 BC to noon UTC on 25 November 4714 BC.
929 </para>
930
931 <para>
932 Although <productname>PostgreSQL</productname> supports Julian Date notation for
933 input and output of dates (and also uses Julian dates for some internal
934 datetime calculations), it does not observe the nicety of having dates
935 run from noon to noon. <productname>PostgreSQL</productname> treats a Julian Date
936 as running from local midnight to local midnight, the same as a normal
937 date.
938 </para>
939
940 <para>
941 This definition does, however, provide a way to obtain the astronomical
942 definition when you need it: do the arithmetic in time
943 zone <literal>UTC+12</literal>. For example,
944 <programlisting>
945 =&gt; SELECT extract(julian from '2021-06-23 7:00:00-04'::timestamptz at time zone 'UTC+12');
946 extract
947 ------------------------------
948 2459388.95833333333333333333
949 (1 row)
950 =&gt; SELECT extract(julian from '2021-06-23 8:00:00-04'::timestamptz at time zone 'UTC+12');
951 extract
952 --------------------------------------
953 2459389.0000000000000000000000000000
954 (1 row)
955 =&gt; SELECT extract(julian from date '2021-06-23');
956 extract
957 ---------
958 2459389
959 (1 row)
960 </programlisting>
961 </para>
962
963 </sect1>
964 </appendix>