diff options
Diffstat (limited to 'doc')
| -rw-r--r-- | doc/src/sgml/datatype.sgml | 159 | ||||
| -rw-r--r-- | doc/src/sgml/datetime.sgml | 237 |
2 files changed, 257 insertions, 139 deletions
diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml index 62117820f74..daabb71fb62 100644 --- a/doc/src/sgml/datatype.sgml +++ b/doc/src/sgml/datatype.sgml @@ -1,5 +1,5 @@ <!-- -$Header: /cvsroot/pgsql/doc/src/sgml/datatype.sgml,v 1.81 2001/12/29 18:35:46 thomas Exp $ +$Header: /cvsroot/pgsql/doc/src/sgml/datatype.sgml,v 1.82 2002/01/04 17:02:02 thomas Exp $ --> <chapter id="datatype"> @@ -1346,29 +1346,35 @@ SELECT b, char_length(b) FROM test2; <para> Time zones, and time-zone conventions, are influenced by - political decisions, not just geometry. Time zones around the + political decisions, not just earth geometry. Time zones around the world became somewhat standardized during the 1900's, but continue to be prone to arbitrary changes. <productname>PostgreSQL</productname> uses your operating - system's underlying features to provide time-zone + system's underlying features to provide output time-zone support, and these systems usually contain information for only the time period 1902 through 2038 (corresponding to the full range of conventional Unix system time). - <type>timestamp with time zone</type> will use time zone - information only within that year range, and assumes that times - are in UTC outside that range. + <type>timestamp with time zone</type> and <type>time with time + zone</type> will use time zone + information only within that year range, and assume that times + outside that range are in <acronym>UTC</acronym>. </para> <para> - To ensure compatibility to earlier versions of - <productname>PostgreSQL</productname> - we continue to provide <type>datetime</type> + To ensure an upgrade path from versions of + <productname>PostgreSQL</productname> earlier than 7.0, + we recognize <type>datetime</type> (equivalent to <type>timestamp</type>) and - <type>timespan</type> (equivalent to <type>interval</type>), - however support for these is now restricted to having an + <type>timespan</type> (equivalent to <type>interval</type>). + These types are + now restricted to having an implicit translation to <type>timestamp</type> and - <type>interval</type>, and these name mappings will be removed in - the next version. + <type>interval</type>, and + support for these will be removed in the next release of + <productname>PostgreSQL</productname> (likely named 7.3). + </para> + + <para> The types <type>abstime</type> and <type>reltime</type> are lower precision types which are used internally. You are discouraged from using any of these types in new @@ -1402,7 +1408,8 @@ SELECT b, char_length(b) FROM test2; <acronym>SQL</acronym> standard requires. See <xref linkend="datetime-appendix"> for the exact parsing rules of date/time input and for the - recognized time zones. + recognized text fields including months, days of the week, and + time zones. </para> <para> @@ -1431,7 +1438,7 @@ SELECT b, char_length(b) FROM test2; </indexterm> <para> - The following are possible inputs for the <type>date</type> type. + The following are some possible inputs for the <type>date</type> type. <table tocentry="1"> <title>Date Input</title> @@ -1480,118 +1487,12 @@ SELECT b, char_length(b) FROM test2; <entry>Year and day of year</entry> </row> <row> - <entry>January 8, 99 BC</entry> - <entry>Year 99 before the Common Era</entry> - </row> - </tbody> - </tgroup> - </table> - </para> - - <para> - <table tocentry="1"> - <title>Month Abbreviations</title> - <tgroup cols="2"> - <thead> - <row> - <entry>Month</entry> - <entry>Abbreviations</entry> - </row> - </thead> - <tbody> - <row> - <entry>April</entry> - <entry>Apr</entry> - </row> - <row> - <entry>August</entry> - <entry>Aug</entry> - </row> - <row> - <entry>December</entry> - <entry>Dec</entry> - </row> - <row> - <entry>February</entry> - <entry>Feb</entry> - </row> - <row> - <entry>January</entry> - <entry>Jan</entry> - </row> - <row> - <entry>July</entry> - <entry>Jul</entry> - </row> - <row> - <entry>June</entry> - <entry>Jun</entry> - </row> - <row> - <entry>March</entry> - <entry>Mar</entry> - </row> - <row> - <entry>November</entry> - <entry>Nov</entry> - </row> - <row> - <entry>October</entry> - <entry>Oct</entry> - </row> - <row> - <entry>September</entry> - <entry>Sep, Sept</entry> - </row> - </tbody> - </tgroup> - </table> - - <note> - <para> - The month <literal>May</literal> has no explicit abbreviation, for obvious reasons. - </para> - </note> - </para> - - <para> - <table tocentry="1"> - <title>Day of the Week Abbreviations</title> - <tgroup cols="2"> - <thead> - <row> - <entry>Day</entry> - <entry>Abbreviation</entry> + <entry>J2451187</entry> + <entry>Julian day</entry> </row> - </thead> - <tbody> <row> - <entry>Sunday</entry> - <entry>Sun</entry> - </row> - <row> - <entry>Monday</entry> - <entry>Mon</entry> - </row> - <row> - <entry>Tuesday</entry> - <entry>Tue, Tues</entry> - </row> - <row> - <entry>Wednesday</entry> - <entry>Wed, Weds</entry> - </row> - <row> - <entry>Thursday</entry> - <entry>Thu, Thur, Thurs</entry> - </row> - <row> - <entry>Friday</entry> - <entry>Fri</entry> - </row> - <row> - <entry>Saturday</entry> - <entry>Sat</entry> + <entry>January 8, 99 BC</entry> + <entry>Year 99 before the Common Era</entry> </row> </tbody> </tgroup> @@ -1656,14 +1557,6 @@ SELECT b, char_length(b) FROM test2; <entry>Same as 16:05; input hour must be <= 12</entry> </row> <row> - <entry>z</entry> - <entry>Same as 00:00:00</entry> - </row> - <row> - <entry>zulu</entry> - <entry>Same as 00:00:00</entry> - </row> - <row> <entry>allballs</entry> <entry>Same as 00:00:00</entry> </row> diff --git a/doc/src/sgml/datetime.sgml b/doc/src/sgml/datetime.sgml index cc1f121622f..32ddd2365b4 100644 --- a/doc/src/sgml/datetime.sgml +++ b/doc/src/sgml/datetime.sgml @@ -1,11 +1,197 @@ <!-- -$Header: /cvsroot/pgsql/doc/src/sgml/datetime.sgml,v 2.24 2001/12/29 18:35:47 thomas Exp $ +$Header: /cvsroot/pgsql/doc/src/sgml/datetime.sgml,v 2.25 2002/01/04 17:02:25 thomas Exp $ Date/time details --> <appendix id="datetime-appendix"> <title id="datetime-appendix-title">Date/Time Support</title> + <para> + <productname>PostgreSQL</productname> uses an internal heuristic + parser for all date/time 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 + value, ignored, or rejected. + The parser contains internal lookup tables for all textual fields, + including months, days of the week, and time + zones. + </para> + + <para> + This appendix includes information on the content of these + lookup tables and describes the steps used by the parser to decode + dates and times. + </para> + + <sect1> + <title>Date/Time Keywords</title> + + <para> + <table tocentry="1"> + <title>Month Abbreviations</title> + <tgroup cols="2"> + <thead> + <row> + <entry>Month</entry> + <entry>Abbreviations</entry> + </row> + </thead> + <tbody> + <row> + <entry>April</entry> + <entry>Apr</entry> + </row> + <row> + <entry>August</entry> + <entry>Aug</entry> + </row> + <row> + <entry>December</entry> + <entry>Dec</entry> + </row> + <row> + <entry>February</entry> + <entry>Feb</entry> + </row> + <row> + <entry>January</entry> + <entry>Jan</entry> + </row> + <row> + <entry>July</entry> + <entry>Jul</entry> + </row> + <row> + <entry>June</entry> + <entry>Jun</entry> + </row> + <row> + <entry>March</entry> + <entry>Mar</entry> + </row> + <row> + <entry>November</entry> + <entry>Nov</entry> + </row> + <row> + <entry>October</entry> + <entry>Oct</entry> + </row> + <row> + <entry>September</entry> + <entry>Sep, Sept</entry> + </row> + </tbody> + </tgroup> + </table> + + <note> + <para> + The month <literal>May</literal> has no explicit abbreviation, for obvious reasons. + </para> + </note> + </para> + + <para> + <table tocentry="1"> + <title>Day of the Week Abbreviations</title> + <tgroup cols="2"> + <thead> + <row> + <entry>Day</entry> + <entry>Abbreviation</entry> + </row> + </thead> + <tbody> + <row> + <entry>Sunday</entry> + <entry>Sun</entry> + </row> + <row> + <entry>Monday</entry> + <entry>Mon</entry> + </row> + <row> + <entry>Tuesday</entry> + <entry>Tue, Tues</entry> + </row> + <row> + <entry>Wednesday</entry> + <entry>Wed, Weds</entry> + </row> + <row> + <entry>Thursday</entry> + <entry>Thu, Thur, Thurs</entry> + </row> + <row> + <entry>Friday</entry> + <entry>Fri</entry> + </row> + <row> + <entry>Saturday</entry> + <entry>Sat</entry> + </row> + </tbody> + </tgroup> + </table> + </para> + + <para> + <table tocentry="1"> + <title><productname>PostgreSQL</productname> Field Modifiers</title> + <titleabbrev>Field Modifiers</titleabbrev> + <tgroup cols="2"> + <thead> + <row> + <entry>Identifier</entry> + <entry>Description</entry> + </row> + </thead> + <tbody> + <row> + <entry>ABSTIME</entry> + <entry>Keyword ignored</entry> + </row> + <row> + <entry>AM</entry> + <entry>Time is before 12:00</entry> + </row> + <row> + <entry>AT</entry> + <entry>Keyword ignored</entry> + </row> + <row> + <entry>JULIAN, JD, J</entry> + <entry>Next field is Julian Day</entry> + </row> + <row> + <entry>ON</entry> + <entry>Keyword ignored</entry> + </row> + <row> + <entry>PM</entry> + <entry>Time is on or after after 12:00</entry> + </row> + <row> + <entry>T</entry> + <entry>Next field is time</entry> + </row> + </tbody> + </tgroup> + </table> + </para> + + <para> + The keyword <literal>ABSTIME</literal> is ignored for historical + reasons; in very old releases of + <productname>PostgreSQL</productname> invalid <type>ABSTIME</type> + fields were emitted as <quote>Invalid Abstime</quote>. This is no + longer the case however and this keyword will likely be dropped in + a future release. + </para> + </sect1> + <sect1 id="timezones"> <title>Time Zones</title> @@ -19,6 +205,15 @@ Date/time details 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. + </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 faciliate + matching local usage with recognized abreviations for cases where + these might differ. <table tocentry="1"> <title><productname>PostgreSQL</productname> Recognized Time Zones</title> @@ -361,6 +556,26 @@ Date/time details <entry>Greenwich Mean Time</entry> </row> <row> + <entry>UT</entry> + <entry>+00:00</entry> + <entry>Universal Time</entry> + </row> + <row> + <entry>UTC</entry> + <entry>+00:00</entry> + <entry>Universal Time, Coordinated</entry> + </row> + <row> + <entry>Z</entry> + <entry>+00:00</entry> + <entry>Same as UTC</entry> + </row> + <row> + <entry>ZULU</entry> + <entry>+00:00</entry> + <entry>Same as UTC</entry> + </row> + <row> <entry>WET</entry> <entry>+00:00</entry> <entry>Western Europe</entry> @@ -603,13 +818,15 @@ Date/time details <substeps> <step> <para> - If the token contains a colon (":"), this is a time string. + If the numeric token contains a colon (":"), this is a time + string. Include all subsequent digits and colons. </para> </step> <step> <para> - If the token contains a dash ("-"), slash ("/"), or dot ("."), + 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> @@ -617,7 +834,8 @@ Date/time details <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 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> @@ -691,6 +909,13 @@ Date/time details <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> @@ -732,10 +957,10 @@ Date/time details <step> <para> - If BC has been specified, negate the year and offset by one for + 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 - 1BC becomes year zero). + <literal>1BC</literal> becomes year zero). </para> </step> |