diff options
Diffstat (limited to 'doc/src/sgml/datetime.sgml')
-rw-r--r-- | doc/src/sgml/datetime.sgml | 552 |
1 files changed, 278 insertions, 274 deletions
diff --git a/doc/src/sgml/datetime.sgml b/doc/src/sgml/datetime.sgml index 6296040d813..ff384533687 100644 --- a/doc/src/sgml/datetime.sgml +++ b/doc/src/sgml/datetime.sgml @@ -1,5 +1,5 @@ <!-- -$Header: /cvsroot/pgsql/doc/src/sgml/datetime.sgml,v 2.28 2002/08/04 06:15:45 thomas Exp $ +$Header: /cvsroot/pgsql/doc/src/sgml/datetime.sgml,v 2.29 2002/11/11 20:14:02 petere Exp $ Date/time details --> @@ -8,7 +8,7 @@ Date/time details <para> <productname>PostgreSQL</productname> uses an internal heuristic - parser for all date/time support. Dates and times are input as + parser for all date/time input support. Dates and times are input as strings, and are broken up into distinct fields with a preliminary determination of what kind of information may be in the field. Each field is interpreted and either assigned a numeric @@ -25,10 +25,203 @@ Date/time details </para> <sect1> - <title>Date/Time Keywords</title> + <title>Date/Time Input Interpretation</title> + + <para> + The date/time types are all decoded using a common set of routines. + </para> + + <procedure> + <title>Date/Time Input Interpretation</title> + + <step> + <para> + Break the input string into tokens and categorize each token as + a string, time, time zone, or number. + </para> + + <substeps> + <step> + <para> + If the numeric token contains a colon (<literal>:</>), this is + a time string. Include all subsequent digits and colons. + </para> + </step> + + <step> + <para> + If the numeric token contains a dash (<literal>-</>), slash + (<literal>/</>), or two or more dots (<literal>.</>), this is + a date string which may have a text month. + </para> + </step> + + <step> + <para> + If the token is numeric only, then it is either a single field + or an ISO 8601 concatenated date (e.g., + <literal>19990113</literal> for January 13, 1999) or time + (e.g. <literal>141516</literal> for 14:15:16). + </para> + </step> + + <step> + <para> + If the token starts with a plus (<literal>+</>) or minus + (<literal>-</>), then it is either a time zone or a special + field. + </para> + </step> + </substeps> + </step> + + <step> + <para> + If the token is a text string, match up with possible strings. + </para> + + <substeps> + <step> + <para> + Do a binary-search table lookup for the token + as either a special string (e.g., <literal>today</literal>), + day (e.g., <literal>Thursday</literal>), + month (e.g., <literal>January</literal>), + or noise word (e.g., <literal>at</literal>, <literal>on</literal>). + </para> + + <para> + Set field values and bit mask for fields. + For example, set year, month, day for <literal>today</literal>, + and additionally hour, minute, second for <literal>now</literal>. + </para> + </step> + + <step> + <para> + If not found, do a similar binary-search table lookup to match + the token with a time zone. + </para> + </step> + + <step> + <para> + If not found, throw an error. + </para> + </step> + </substeps> + </step> + + <step> + <para> + The token is a number or number field. + </para> + + <substeps> + <step> + <para> + If there are more than 4 digits, + and if no other date fields have been previously read, then interpret + as a <quote>concatenated date</quote> (e.g., <literal>19990118</literal>). 8 + and 6 digits are interpreted as year, month, and day, while 7 + and 5 digits are interpreted as year, day of year, respectively. + </para> + </step> + + <step> + <para> + If the token is three digits + and a year has already been decoded, then interpret as day of year. + </para> + </step> + + <step> + <para> + If four or six digits and a year has already been read, then + interpret as a time. + </para> + </step> + + <step> + <para> + If four or more digits, then interpret as a year. + </para> + </step> + + <step> + <para> + If in European date mode, and if the day field has not yet been read, + and if the value is less than or equal to 31, then interpret as a day. + </para> + </step> + + <step> + <para> + If the month field has not yet been read, + and if the value is less than or equal to 12, then interpret as a month. + </para> + </step> + + <step> + <para> + If the day field has not yet been read, + and if the value is less than or equal to 31, then interpret as a day. + </para> + </step> + + <step> + <para> + If two digits or four or more digits, then interpret as a year. + </para> + </step> + + <step> + <para> + Otherwise, throw an error. + </para> + </step> + </substeps> + </step> + + <step> + <para> + If BC has been specified, negate the year and add one for + internal storage. (There is no year zero in the Gregorian + calendar, so numerically <literal>1BC</literal> becomes year + zero.) + </para> + </step> + + <step> + <para> + If BC was not specified, and if the year field was two digits in length, then + adjust the year to 4 digits. If the field was less than 70, then add 2000; + otherwise, add 1900. + + <tip> + <para> + Gregorian years AD 1-99 may be entered by using 4 digits with leading + zeros (e.g., <literal>0099</> is AD 99). Previous versions of + <productname>PostgreSQL</productname> accepted years with three + digits and with single digits, but as of version 7.0 the rules have + been tightened up to reduce the possibility of ambiguity. + </para> + </tip> + </para> + </step> + </procedure> + </sect1> + + + <sect1> + <title>Date/Time Key Words</title> <para> - <table tocentry="1"> + <xref linkend="datetime-month-table"> shows the tokens that are + permissible as abbreviations for the names of the month. + </para> + + <table id="datetime-month-table"> <title>Month Abbreviations</title> <tgroup cols="2"> <thead> @@ -88,13 +281,17 @@ Date/time details <note> <para> - The month <literal>May</literal> has no explicit abbreviation, for obvious reasons. + The month May has no explicit abbreviation, for obvious reasons. </para> </note> - </para> <para> - <table tocentry="1"> + <xref linkend="datetime-dow-table"> shows the tokens that are + permissible as abbreviations for the names of the days of the + week. + </para> + + <table id="datetime-dow-table"> <title>Day of the Week Abbreviations</title> <tgroup cols="2"> <thead> @@ -135,12 +332,14 @@ Date/time details </tbody> </tgroup> </table> - </para> <para> - <table tocentry="1"> - <title><productname>PostgreSQL</productname> Field Modifiers</title> - <titleabbrev>Field Modifiers</titleabbrev> + <xref linkend="datetime-mod-table"> shows the tokens that serve + various modifier purposes. + </para> + + <table id="datetime-mod-table"> + <title>Date/Time Field Modifiers</title> <tgroup cols="2"> <thead> <row> @@ -151,7 +350,7 @@ Date/time details <tbody> <row> <entry><literal>ABSTIME</literal></entry> - <entry>Keyword ignored</entry> + <entry>Key word ignored</entry> </row> <row> <entry><literal>AM</literal></entry> @@ -159,7 +358,7 @@ Date/time details </row> <row> <entry><literal>AT</literal></entry> - <entry>Keyword ignored</entry> + <entry>Key word ignored</entry> </row> <row> <entry><literal>JULIAN</>, <literal>JD</>, <literal>J</></entry> @@ -167,7 +366,7 @@ Date/time details </row> <row> <entry><literal>ON</literal></entry> - <entry>Keyword ignored</entry> + <entry>Key word ignored</entry> </row> <row> <entry><literal>PM</literal></entry> @@ -180,44 +379,40 @@ Date/time details </tbody> </tgroup> </table> - </para> <para> - The keyword <literal>ABSTIME</literal> is ignored for historical + The key word <literal>ABSTIME</literal> is ignored for historical reasons; in very old releases of - <productname>PostgreSQL</productname> invalid <type>ABSTIME</type> - fields were emitted as <literal>Invalid Abstime</literal>. This is no - longer the case however and this keyword will likely be dropped in + <productname>PostgreSQL</productname> invalid fields of type <type>abstime</type> + were emitted as <literal>Invalid Abstime</literal>. This is no + longer the case however and this key word will likely be dropped in a future release. </para> - </sect1> - <sect1 id="timezones"> - <title>Time Zones</title> - - <indexterm zone="timezones"> + <indexterm> <primary>time zones</primary> </indexterm> <para> + <xref linkend="datetime-timezone-table"> shows the time zone + abbreviations recognized by <productname>PostgreSQL</productname>. <productname>PostgreSQL</productname> contains internal tabular - information for time zone decoding, since there is no *nix standard - system interface to provide access to general, cross-timezone - information. The underlying OS <emphasis>is</emphasis> used to - provide time zone information for <emphasis>output</emphasis>, however. + information for time zone decoding, since there is no standard + operating system interface to provide access to general, + cross-time zone information. The underlying operating system + <emphasis>is</emphasis> used to provide time zone information for + <emphasis>output</emphasis>, however. </para> <para> - The following table of time zones recognized by - <productname>PostgreSQL</productname> is organized by time - zone offset from UTC, rather than alphabetically; this is intended - to facilitate + The table is organized by time zone offset from <acronym>UTC</>, + rather than alphabetically; this is intended to facilitate matching local usage with recognized abbreviations for cases where these might differ. + </para> - <table tocentry="1"> - <title><productname>PostgreSQL</productname> Recognized Time Zones</title> - <titleabbrev>Time Zones</titleabbrev> + <table id="datetime-timezone-table"> + <title>Time Zone Abbreviations</title> <tgroup cols="3"> <thead> <row> @@ -749,31 +944,29 @@ Date/time details </tbody> </tgroup> </table> - </para> - <sect2> + <formalpara> <title>Australian Time Zones</title> <para> - Australian time zones and their naming variants - account for fully one quarter of all time zones in the - <productname>PostgreSQL</productname> time zone lookup table. - There are two naming conflicts with time zones commonly used - in the United States, <literal>CST</literal> and <literal>EST</literal>. + There are three naming conflicts between Australian time zone + names with time zones commonly used in North and South America: + <literal>ACST</literal>, <literal>CST</literal>, and + <literal>EST</literal>. If the run-time option + <varname>AUSTRALIAN_TIMEZONES</varname> is set to true then + <literal>ACST</literal>, <literal>CST</literal>, + <literal>EST</literal>, and <literal>SAT</literal> are interpreted + as Australian time zone names, as shown in <xref + linkend="datetime-oztz-table">. If it is false (which is the + default), then <literal>ACST</literal>, <literal>CST</literal>, + and <literal>EST</literal> are taken as American time zone names, + and <literal>SAT</literal> is interpreted as a noise word + indicating Saturday. </para> + </formalpara> - <para> - If the run-time option <literal>AUSTRALIAN_TIMEZONES</literal> is set - then <literal>CST</literal>, <literal>EST</literal>, and - <literal>SAT</literal> will be - interpreted as Australian timezone names. Without this option, - <literal>CST</literal> and <literal>EST</literal> are taken as - American timezone names, while <literal>SAT</literal> is interpreted as a - noise word indicating <literal>Saturday</literal>. - - <table tocentry="1"> - <title><productname>PostgreSQL</productname> Australian Time Zones</title> - <titleabbrev>Australian Time Zones</titleabbrev> + <table id="datetime-oztz-table"> + <title>Australian Time Zone Abbreviations</title> <tgroup cols="3"> <thead> <row> @@ -806,196 +999,10 @@ Date/time details </tbody> </tgroup> </table> - </para> - </sect2> - - <sect2> - <title>Date/Time Input Interpretation</title> - <para> - The date/time types are all decoded using a common set of routines. - </para> - - <procedure> - <title>Date/Time Input Interpretation</title> - - <step> - <para> - Break the input string into tokens and categorize each token as - a string, time, time zone, or number. - </para> - - <substeps> - <step> - <para> - If the numeric token contains a colon (":"), this is a time - string. Include all subsequent digits and colons. - </para> - </step> - - <step> - <para> - If the numeric token contains a dash ("-"), slash ("/"), or - two or more dots ("."), - this is a date string which may have a text month. - </para> - </step> - - <step> - <para> - If the token is numeric only, then it is either a single field - or an ISO-8601 concatenated date - (e.g. <literal>19990113</literal> for January 13, 1999) - or time (e.g. 141516 for 14:15:16). - </para> - </step> - <step> - <para> - If the token starts with a plus ("+") or minus ("-"), - then it is either a time zone or a special field. - </para> - </step> - </substeps> - </step> - - <step> - <para> - If the token is a text string, match up with possible strings. - </para> - - <substeps> - <step> - <para> - Do a binary-search table lookup for the token - as either a special string (e.g. <literal>today</literal>), - day (e.g. <literal>Thursday</literal>), - month (e.g. <literal>January</literal>), - or noise word (e.g. <literal>at</literal>, <literal>on</literal>). - </para> - <para> - Set field values and bit mask for fields. - For example, set year, month, day for <literal>today</literal>, - and additionally hour, minute, second for <literal>now</literal>. - </para> - </step> - - <step> - <para> - If not found, do a similar binary-search table lookup to match - the token with a time zone. - </para> - </step> - - <step> - <para> - If not found, throw an error. - </para> - </step> - </substeps> - </step> - - <step> - <para> - The token is a number or number field. - </para> - - <substeps> - <step> - <para> - If there are more than 4 digits, - and if no other date fields have been previously read, then interpret - as a <quote>concatenated date</quote> (e.g. <literal>19990118</literal>). 8 - and 6 digits are interpreted as year, month, and day, while 7 - and 5 digits are interpreted as year, day of year, respectively. - </para> - </step> - - <step> - <para> - If the token is three digits - and a year has already been decoded, then interpret as day of year. - </para> - </step> - - <step> - <para> - If four or six digits and a year has already been read, then - interpret as a time. - </para> - </step> - - <step> - <para> - If four or more digits, then interpret as a year. - </para> - </step> - - <step> - <para> - If in European date mode, and if the day field has not yet been read, - and if the value is less than or equal to 31, then interpret as a day. - </para> - </step> - - <step> - <para> - If the month field has not yet been read, - and if the value is less than or equal to 12, then interpret as a month. - </para> - </step> - - <step> - <para> - If the day field has not yet been read, - and if the value is less than or equal to 31, then interpret as a day. - </para> - </step> - - <step> - <para> - If two digits or four or more digits, then interpret as a year. - </para> - </step> - - <step> - <para> - Otherwise, throw an error. - </para> - </step> - </substeps> - </step> - - <step> - <para> - If BC has been specified, negate the year and add one for - internal storage - (there is no year zero in the Gregorian calendar, so numerically - <literal>1BC</literal> becomes year zero). - </para> - </step> - - <step> - <para> - If BC was not specified, and if the year field was two digits in length, then - adjust the year to 4 digits. If the field was less than 70, then add 2000; - otherwise, add 1900. - - <tip> - <para> - Gregorian years 1-99AD may be entered by using 4 digits with leading - zeros (e.g. 0099 is 99AD). Previous versions of - <productname>PostgreSQL</productname> accepted years with three - digits and with single digits, but as of version 7.0 the rules have - been tightened up to reduce the possibility of ambiguity. - </para> - </tip> - </para> - </step> - </procedure> - </sect2> - </sect1> + </sect1> - <sect1 id="units-history"> + <sect1 id="units-history"> <title>History of Units</title> <note> @@ -1015,22 +1022,20 @@ Date/time details to noon UTC on 2 January 4713 BC. </para> - <para> - <quote>Julian Day</quote> is different from <quote>Julian Date</quote>. - - The Julian calendar was introduced by Julius Caesar in 45 BC. It was - in common use until the 1582, when countries started changing to the - Gregorian calendar. - - In the Julian calendar, the tropical year is approximated as 365 1/4 - days = 365.25 days. This gives an error of about 1 day in - 128 years. - The accumulating calendar error prompted Pope Gregory XIII - to reform the calendar in accordance with instructions - from the Council of Trent. + <para> + The <quote>Julian Day</quote> is different from the <quote>Julian + Date</quote>. The Julian date refers to the Julian calendar, which + was introduced by Julius Caesar in 45 BC. It was in common use + until the 1582, when countries started changing to the Gregorian + calendar. In the Julian calendar, the tropical year is + approximated as 365 1/4 days = 365.25 days. This gives an error of + about 1 day in 128 years. </para> <para> + The accumulating calendar error prompted + Pope Gregory XIII to reform the calendar in accordance with + instructions from the Council of Trent. In the Gregorian calendar, the tropical year is approximated as 365 + 97 / 400 days = 365.2425 days. Thus it takes approximately 3300 years for the tropical year to shift one day with respect to the @@ -1066,37 +1071,36 @@ Date/time details This was observed in Italy, Poland, Portugal, and Spain. Other Catholic countries followed shortly after, but Protestant countries were reluctant to change, and the Greek orthodox countries didn't change - until the start of this century. + until the start of the 20th century. The reform was observed by Great Britain and Dominions (including what is now the USA) in 1752. - Thus 2 Sep 1752 was followed by 14 Sep 1752. + Thus 2 September 1752 was followed by 14 September 1752. - This is why Unix systems have <application>cal</application> + This is why Unix systems have the <command>cal</command> program produce the following: - <programlisting> -% cal 9 1752 +<screen> +$ <userinput>cal 9 1752</userinput> September 1752 S M Tu W Th F S 1 2 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 - </programlisting> +</screen> </para> - <note> - <para> - SQL92 states that - <quote>Within the definition of a <quote>datetime literal</quote>, - the <quote>datetime value</quote>s are constrained by the - natural rules for dates and times - according to the Gregorian calendar</quote>. - Dates between 1752-09-03 and 1752-09-13, although eliminated in - some countries by Papal fiat, conform to - <quote>natural rules</quote> and are hence valid dates. - </para> - </note> + <note> + <para> + The SQL standard states that <quote>Within the definition of a + <quote>datetime literal</quote>, the <quote>datetime + value</quote>s are constrained by the natural rules for dates and + times according to the Gregorian calendar</quote>. Dates between + 1752-09-03 and 1752-09-13, although eliminated in some countries + by Papal fiat, conform to <quote>natural rules</quote> and are + hence valid dates. + </para> + </note> <para> Different calendars have been developed in various parts of the @@ -1108,7 +1112,7 @@ Date/time details calendar in 2637 BC. The People's Republic of China uses the Gregorian calendar - for civil purposes. Chinese calendar is used for determining + for civil purposes. The Chinese calendar is used for determining festivals. </para> </sect1> |