| 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–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 |
=> 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 |
=> 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 |
=> 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 — 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 — 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 — 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><UTC-05></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–1609) |
| 916 |
and probably takes its name from Scaliger's father, |
| 917 |
the Italian scholar Julius Caesar Scaliger (1484–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 |
=> 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 |
=> 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 |
=> 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> |