datatype.sgml

PostgreSQL 8.1.4的源码 适用于Linux下的开源数据库系统

      <tgroup cols="2">
       <thead>
        <row>
         <entry>Example</entry>
         <entry>Description</entry>
        </row>
       </thead>
       <tbody>
        <row>
         <entry>January 8, 1999</entry>
         <entry>unambiguous in any <varname>datestyle</varname> input mode</entry>
        </row>
        <row>
         <entry>1999-01-08</entry>
         <entry>ISO 8601; January 8 in any mode
         (recommended format)</entry>
        </row>
        <row>
         <entry>1/8/1999</entry>
         <entry>January 8 in <literal>MDY</> mode;
          August 1 in <literal>DMY</> mode</entry>
        </row>
        <row>
         <entry>1/18/1999</entry>
         <entry>January 18 in <literal>MDY</> mode;
          rejected in other modes</entry>
        </row>
        <row>
         <entry>01/02/03</entry>
         <entry>January 2, 2003 in <literal>MDY</> mode;
          February 1, 2003 in <literal>DMY</> mode;
          February 3, 2001 in <literal>YMD</> mode
         </entry>
        </row>
        <row>
         <entry>1999-Jan-08</entry>
         <entry>January 8 in any mode</entry>
        </row>
        <row>
         <entry>Jan-08-1999</entry>
         <entry>January 8 in any mode</entry>
        </row>
        <row>
         <entry>08-Jan-1999</entry>
         <entry>January 8 in any mode</entry>
        </row>
        <row>
         <entry>99-Jan-08</entry>
         <entry>January 8 in <literal>YMD</> mode, else error</entry>
        </row>
        <row>
         <entry>08-Jan-99</entry>
         <entry>January 8, except error in <literal>YMD</> mode</entry>
        </row>
        <row>
         <entry>Jan-08-99</entry>
         <entry>January 8, except error in <literal>YMD</> mode</entry>
        </row>
        <row>
         <entry>19990108</entry>
         <entry>ISO 8601; January 8, 1999 in any mode</entry>
        </row>
        <row>
         <entry>990108</entry>
         <entry>ISO 8601; January 8, 1999 in any mode</entry>
        </row>
        <row>
         <entry>1999.008</entry>
         <entry>year and day of year</entry>
        </row>
        <row>
         <entry>J2451187</entry>
         <entry>Julian day</entry>
        </row>
        <row>
         <entry>January 8, 99 BC</entry>
         <entry>year 99 before the Common Era</entry>
        </row>
       </tbody>
      </tgroup>
     </table>
    </sect3>

    <sect3>
     <title>Times</title>

     <indexterm>
      <primary>time</primary>
     </indexterm>
     <indexterm>
      <primary>time without time zone</primary>
     </indexterm>
     <indexterm>
      <primary>time with time zone</primary>
     </indexterm>

     <para>
      The time-of-day types are <type>time [
      (<replaceable>p</replaceable>) ] without time zone</type> and
      <type>time [ (<replaceable>p</replaceable>) ] with time
      zone</type>.  Writing just <type>time</type> is equivalent to
      <type>time without time zone</type>.
     </para>

     <para>
      Valid input for these types consists of a time of day followed
      by an optional time zone. (See <xref
      linkend="datatype-datetime-time-table"> 
      and <xref linkend="datatype-timezone-table">.)  If a time zone is
      specified in the input for <type>time without time zone</type>,
      it is silently ignored.
     </para>

      <table id="datatype-datetime-time-table">
       <title>Time Input</title>
       <tgroup cols="2">
        <thead>
         <row>
          <entry>Example</entry>
          <entry>Description</entry>
         </row>
        </thead>
        <tbody>
         <row>
          <entry><literal>04:05:06.789</literal></entry>
          <entry>ISO 8601</entry>
         </row>
         <row>
          <entry><literal>04:05:06</literal></entry>
          <entry>ISO 8601</entry>
         </row>
         <row>
          <entry><literal>04:05</literal></entry>
          <entry>ISO 8601</entry>
         </row>
         <row>
          <entry><literal>040506</literal></entry>
          <entry>ISO 8601</entry>
         </row>
         <row>
          <entry><literal>04:05 AM</literal></entry>
          <entry>same as 04:05; AM does not affect value</entry>
         </row>
         <row>
          <entry><literal>04:05 PM</literal></entry>
          <entry>same as 16:05; input hour must be &lt;= 12</entry>
         </row>
         <row>
          <entry><literal>04:05:06.789-8</literal></entry>
          <entry>ISO 8601</entry>
         </row>
         <row>
          <entry><literal>04:05:06-08:00</literal></entry>
          <entry>ISO 8601</entry>
         </row>
         <row>
          <entry><literal>04:05-08:00</literal></entry>
          <entry>ISO 8601</entry>
         </row>
         <row>
          <entry><literal>040506-08</literal></entry>
          <entry>ISO 8601</entry>
         </row>
         <row>
          <entry><literal>04:05:06 PST</literal></entry>
          <entry>time zone specified by name</entry>
         </row>
        </tbody>
       </tgroup>
      </table>

      <table tocentry="1" id="datatype-timezone-table">
       <title>Time Zone Input</title>
       <tgroup cols="2">
        <thead>
         <row>
          <entry>Example</entry>
          <entry>Description</entry>
         </row>
        </thead>
        <tbody>
         <row>
          <entry><literal>PST</literal></entry>
          <entry>Pacific Standard Time</entry>
         </row>
         <row>
          <entry><literal>-8:00</literal></entry>
          <entry>ISO-8601 offset for PST</entry>
         </row>
         <row>
          <entry><literal>-800</literal></entry>
          <entry>ISO-8601 offset for PST</entry>
         </row>
         <row>
          <entry><literal>-8</literal></entry>
          <entry>ISO-8601 offset for PST</entry>
         </row>
         <row>
          <entry><literal>zulu</literal></entry>
          <entry>Military abbreviation for UTC</entry>
         </row>
         <row>
          <entry><literal>z</literal></entry>
          <entry>Short form of <literal>zulu</literal></entry>
         </row>
        </tbody>
       </tgroup>
      </table>

     <para>
     Refer to <xref linkend="datetime-appendix"> for a list of
     time zone names that are recognized for input.
    </para>
    </sect3>

    <sect3>
    <title>Time Stamps</title>

    <indexterm>
     <primary>timestamp</primary>
    </indexterm>

    <indexterm>
     <primary>timestamp with time zone</primary>
    </indexterm>

    <indexterm>
     <primary>timestamp without time zone</primary>
    </indexterm>

     <para>
      Valid input for the time stamp types consists of a concatenation
      of a date and a time, followed by an optional time zone,
      followed by an optional <literal>AD</literal> or <literal>BC</literal>.
      (Alternatively, <literal>AD</literal>/<literal>BC</literal> can appear
      before the time zone, but this is not the preferred ordering.)
      Thus

<programlisting>
1999-01-08 04:05:06
</programlisting>
      and
<programlisting>
1999-01-08 04:05:06 -8:00
</programlisting>

      are valid values, which follow the <acronym>ISO</acronym> 8601
      standard.  In addition, the wide-spread format

<programlisting>
January 8 04:05:06 1999 PST
</programlisting>
      is supported.
     </para>

     <para>
      The <acronym>SQL</acronym> standard differentiates <type>timestamp without time zone</type> 
      and <type>timestamp with time zone</type> literals by the presence of a 
      <quote>+</quote> or <quote>-</quote>. Hence, according to the standard, 
      <programlisting>TIMESTAMP '2004-10-19 10:23:54'</programlisting>
      is a <type>timestamp without time zone</type>, while
      <programlisting>TIMESTAMP '2004-10-19 10:23:54+02'</programlisting>
      is a <type>timestamp with time zone</type>.
      <productname>PostgreSQL</productname> never examines the content of a
      literal string before determining its type, and therefore will treat
      both of the above as <type>timestamp without time zone</type>.  To
      ensure that a literal is treated as <type>timestamp with time
      zone</type>, give it the correct explicit type:
      <programlisting>TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'</programlisting>
      In a literal that has been decided to be <type>timestamp without time
      zone</type>, <productname>PostgreSQL</productname> will silently ignore
      any time zone indication.
      That is, the resulting value is derived from the date/time
      fields in the input value, and is not adjusted for time zone.
     </para>

     <para>
      For <type>timestamp with time zone</type>, the internally stored
      value is always in UTC (Universal
      Coordinated Time, traditionally known as Greenwich Mean Time,
      <acronym>GMT</>).  An input value that has an explicit
      time zone specified is converted to UTC using the appropriate offset
      for that time zone.  If no time zone is stated in the input string,
      then it is assumed to be in the time zone indicated by the system's
      <xref linkend="guc-timezone"> parameter, and is converted to UTC using the
      offset for the <varname>timezone</> zone.
     </para>

     <para>
      When a <type>timestamp with time
      zone</type> value is output, it is always converted from UTC to the
      current <varname>timezone</> zone, and displayed as local time in that
      zone.  To see the time in another time zone, either change
      <varname>timezone</> or use the <literal>AT TIME ZONE</> construct
      (see <xref linkend="functions-datetime-zoneconvert">).
     </para>

     <para>
      Conversions between <type>timestamp without time zone</type> and
      <type>timestamp with time zone</type> normally assume that the
      <type>timestamp without time zone</type> value should be taken or given
      as <varname>timezone</> local time.  A different zone reference can
      be specified for the conversion using <literal>AT TIME ZONE</>.
     </para>
    </sect3>

    <sect3>
     <title>Intervals</title>

     <indexterm>
      <primary>interval</primary>
     </indexterm>

      <para>
       <type>interval</type> values can be written with the following syntax:

<programlisting>
<optional>@</> <replaceable>quantity</> <replaceable>unit</> <optional><replaceable>quantity</> <replaceable>unit</>...</> <optional><replaceable>direction</></optional>
</programlisting>

      Where: <replaceable>quantity</> is a number (possibly signed);
      <replaceable>unit</> is <literal>second</literal>,
      <literal>minute</literal>, <literal>hour</literal>, <literal>day</literal>,
      <literal>week</literal>, <literal>month</literal>, <literal>year</literal>,
      <literal>decade</literal>, <literal>century</literal>, <literal>millennium</literal>,
      or abbreviations or plurals of these units;
      <replaceable>direction</> can be <literal>ago</literal> or
      empty.  The at sign (<literal>@</>) is optional noise.  The amounts
      of different units are implicitly added up with appropriate
      sign accounting.
     </para>

     <para>
      Quantities of days, hours, minutes, and seconds can be specified without
      explicit unit markings.  For example, <literal>'1 12:59:10'</> is read
      the same as <literal>'1 day 12 hours 59 min 10 sec'</>.
     </para>

     <para>
      The optional subsecond precision <replaceable>p</replaceable> should 
      be between 0 and 6, and defaults to the precision of the input literal.
     </para>

     <para>
      Internally <type>interval</> values are stored as months, days,
      and seconds. This is done because the number of days in a month
      varies, and a day can have 23 or 25 hours if a daylight savings
      time adjustment is involved. Because intervals are usually created
      from constant strings or <type>timestamp</> subtraction, this
      storage method works well in most cases. Functions
      <function>justify_days</> and <function>justify_hours</> are
      available for adjusting days and hours that overflow their normal
      periods.
     </para>
    </sect3>

    <sect3>
     <title>Special Values</title>

     <indexterm>
      <primary>time</primary>
      <secondary>constants</secondary>
     </indexterm>

     <indexterm>
      <primary>date</primary>
      <secondary>constants</secondary>
     </indexterm>

     <para>
      <productname>PostgreSQL</productname> supports several
      special date/time input values for convenience, as shown in <xref
      linkend="datatype-datetime-special-table">.  The values
      <literal>infinity</literal> and <literal>-infinity</literal>
      are specially represented inside the system and will be displa