datatype.sgml

PostgreSQL7.4.6 for Linux

    either a three digit octal value or escaping another backslash.
    For example, a string literal passed to the server as
    <literal>'\\001'</literal> becomes <literal>\001</literal> after
    passing through the string-literal parser. The
    <literal>\001</literal> is then sent to the <type>bytea</type>
    input function, where it is converted to a single octet with a
    decimal value of 1.  Note that the apostrophe character is not
    treated specially by <type>bytea</type>, so it follows the normal
    rules for string literals.  (See also <xref
    linkend="sql-syntax-strings">.)
   </para>

   <para>
    <type>Bytea</type> octets are also escaped in the output. In general, each
    <quote>non-printable</quote> octet is converted into
    its equivalent three-digit octal value and preceded by one backslash.
    Most <quote>printable</quote> octets are represented by their standard
    representation in the client character set. The octet with decimal
    value 92 (backslash) has a special alternative output representation.
    Details are in <xref linkend="datatype-binary-resesc">.
   </para>

   <table id="datatype-binary-resesc">
    <title><type>bytea</> Output Escaped Octets</title>
    <tgroup cols="5">
     <thead>
      <row>
       <entry>Decimal Octet Value</entry>
       <entry>Description</entry>
       <entry>Escaped Output Representation</entry>
       <entry>Example</entry>
       <entry>Output Result</entry>
      </row>
     </thead>

     <tbody>

      <row>
       <entry>92</entry>
       <entry>backslash</entry>
       <entry><literal>\\</literal></entry>
       <entry><literal>SELECT '\\134'::bytea;</literal></entry>
       <entry><literal>\\</literal></entry>
      </row>

      <row>
       <entry>0 to 31 and 127 to 255</entry>
       <entry><quote>non-printable</quote> octets</entry>
       <entry><literal>\<replaceable>xxx</></literal> (octal value)</entry>
       <entry><literal>SELECT '\\001'::bytea;</literal></entry>
       <entry><literal>\001</literal></entry>
      </row>

      <row>
       <entry>32 to 126</entry>
       <entry><quote>printable</quote> octets</entry>
       <entry>client character set representation</entry>
       <entry><literal>SELECT '\\176'::bytea;</literal></entry>
       <entry><literal>~</literal></entry>
      </row>

     </tbody>
    </tgroup>
   </table>

   <para>
    Depending on the front end to <productname>PostgreSQL</> you use,
    you may have additional work to do in terms of escaping and
    unescaping <type>bytea</type> strings. For example, you may also
    have to escape line feeds and carriage returns if your interface
    automatically translates these.
   </para>

   <para>
        The <acronym>SQL</acronym> standard defines a different binary
        string type, called <type>BLOB</type> or <type>BINARY LARGE
         OBJECT</type>.  The input format is different compared to
        <type>bytea</type>, but the provided functions and operators are
        mostly the same.
   </para>
 </sect1>


  <sect1 id="datatype-datetime">
   <title>Date/Time Types</title>

   <indexterm zone="datatype-datetime">
    <primary>date</primary>
   </indexterm>
   <indexterm zone="datatype-datetime">
    <primary>time</primary>
   </indexterm>
   <indexterm zone="datatype-datetime">
    <primary>time without time zone</primary>
   </indexterm>
   <indexterm zone="datatype-datetime">
    <primary>time with time zone</primary>
   </indexterm>
   <indexterm zone="datatype-datetime">
    <primary>timestamp</primary>
   </indexterm>
   <indexterm zone="datatype-datetime">
    <primary>timestamp with time zone</primary>
   </indexterm>
   <indexterm zone="datatype-datetime">
    <primary>timestamp without time zone</primary>
   </indexterm>
   <indexterm zone="datatype-datetime">
    <primary>interval</primary>
   </indexterm>
   <indexterm zone="datatype-datetime">
    <primary>time span</primary>
   </indexterm>

   <para>
    <productname>PostgreSQL</productname> supports the full set of
    <acronym>SQL</acronym> date and time types, shown in <xref
    linkend="datatype-datetime-table">.
   </para>

    <table id="datatype-datetime-table">
     <title>Date/Time Types</title>
     <tgroup cols="6">
      <thead>
       <row>
        <entry>Name</entry>
        <entry>Storage Size</entry>
        <entry>Description</entry>
        <entry>Low Value</entry>
        <entry>High Value</entry>
        <entry>Resolution</entry>
       </row>
      </thead>
      <tbody>
       <row>
        <entry><type>timestamp [ (<replaceable>p</replaceable>) ] [ without time zone ]</type></entry>
        <entry>8 bytes</entry>
        <entry>both date and time</entry>
        <entry>4713 BC</entry>
        <entry>5874897 AD</entry>
        <entry>1 microsecond / 14 digits</entry>
       </row>
       <row>
        <entry><type>timestamp [ (<replaceable>p</replaceable>) ] with time zone</type></entry>
        <entry>8 bytes</entry>
        <entry>both date and time, with time zone</entry>
        <entry>4713 BC</entry>
        <entry>5874897 AD</entry>
        <entry>1 microsecond / 14 digits</entry>
       </row>
       <row>
        <entry><type>interval [ (<replaceable>p</replaceable>) ]</type></entry>
        <entry>12 bytes</entry>
        <entry>time intervals</entry>
        <entry>-178000000 years</entry>
        <entry>178000000 years</entry>
        <entry>1 microsecond</entry>
       </row>
       <row>
        <entry><type>date</type></entry>
        <entry>4 bytes</entry>
        <entry>dates only</entry>
        <entry>4713 BC</entry>
        <entry>32767 AD</entry>
        <entry>1 day</entry>
       </row>
       <row>
        <entry><type>time [ (<replaceable>p</replaceable>) ] [ without time zone ]</type></entry>
        <entry>8 bytes</entry>
        <entry>times of day only</entry>
        <entry>00:00:00.00</entry>
        <entry>23:59:59.99</entry>
        <entry>1 microsecond</entry>
       </row>
       <row>
        <entry><type>time [ (<replaceable>p</replaceable>) ] with time zone</type></entry>
        <entry>12 bytes</entry>
        <entry>times of day only, with time zone</entry>
        <entry>00:00:00.00+12</entry>
        <entry>23:59:59.99-12</entry>
        <entry>1 microsecond</entry>
       </row>
      </tbody>
     </tgroup>
    </table>

   <note>
    <para>
     Prior to <productname>PostgreSQL</productname> 7.3, writing just
     <type>timestamp</type> was equivalent to <type>timestamp with
     time zone</type>.  This was changed for SQL compliance.
    </para>
   </note>

   <para>
    <type>time</type>, <type>timestamp</type>, and
    <type>interval</type> accept an optional precision value
    <replaceable>p</replaceable> which specifies the number of
    fractional digits retained in the seconds field. By default, there
    is no explicit bound on precision.  The allowed range of
    <replaceable>p</replaceable> is from 0 to 6 for the
    <type>timestamp</type> and <type>interval</type> types.
   </para>

   <note>
   <para>
    When <type>timestamp</> values are stored as double precision floating-point
    numbers (currently the default), the effective limit of precision
    may be less than 6. <type>timestamp</type> values are stored as seconds
    before or after midnight 2000-01-01.  Microsecond precision is achieved for
    dates within a few years of 2000-01-01, but the precision degrades for
    dates further away.  When <type>timestamp</type> values are stored as
    eight-byte integers (a compile-time
    option), microsecond precision is available over the full range of
    values. However eight-byte integer timestamps have a more limited range of
    dates than shown above: from 4713 BC up to 294276 AD.
   </para>
   </note>

   <para>
    For the <type>time</type> types, the allowed range of
    <replaceable>p</replaceable> is from 0 to 6 when eight-byte integer
    storage is used, or from 0 to 10 when floating-point storage is used.
   </para>

   <para>
    The type <type>time with time zone</type> is defined by the SQL
    standard, but the definition exhibits properties which lead to
    questionable usefulness. In most cases, a combination of
    <type>date</type>, <type>time</type>, <type>timestamp without time
    zone</type>, and <type>timestamp with time zone</type> should
    provide a complete range of date/time functionality required by
    any application.
   </para>

   <para>
    The types <type>abstime</type>
    and <type>reltime</type> are lower precision types which are used internally.
    You are discouraged from using these types in new
    applications and are encouraged to move any old
    ones over when appropriate. Any or all of these internal types
    might disappear in a future release.
   </para>

   <sect2 id="datatype-datetime-input">
    <title>Date/Time Input</title>

    <para>
     Date and time input is accepted in almost any reasonable format, including
     ISO 8601, <acronym>SQL</acronym>-compatible, 
     traditional <productname>POSTGRES</productname>, and others.
     For some formats, ordering of month, day, and year in date input is
     ambiguous and there is support for specifying the expected
     ordering of these fields.  Set the <varname>datestyle</> parameter
     to <literal>MDY</> to select month-day-year interpretation,
     <literal>DMY</> to select day-month-year interpretation, or
     <literal>YMD</> to select year-month-day interpretation.
    </para>

    <para>
     <productname>PostgreSQL</productname> is more flexible in
     handling date/time input than the
     <acronym>SQL</acronym> standard requires.
     See <xref linkend="datetime-appendix">
     for the exact parsing rules of date/time input and for the
     recognized text fields including months, days of the week, and
     time zones. 
    </para>

    <para>
     Remember that any date or time literal input needs to be enclosed
     in single quotes, like text strings.  Refer to 
     <xref linkend="sql-syntax-constants-generic"> for more
     information.
     <acronym>SQL</acronym> requires the following syntax
<synopsis>
<replaceable>type</replaceable> [ (<replaceable>p</replaceable>) ] '<replaceable>value</replaceable>'
</synopsis>
     where <replaceable>p</replaceable> in the optional precision
     specification is an integer corresponding to the number of
     fractional digits in the seconds field. Precision can be
     specified for <type>time</type>, <type>timestamp</type>, and
     <type>interval</type> types.  The allowed values are mentioned
     above.  If no precision is specified in a constant specification,
     it defaults to the precision of the literal value.
    </para>

    <sect3>
    <title>Dates</title>

    <indexterm>
     <primary>date</primary>
    </indexterm>
 
    <para>
     <xref linkend="datatype-datetime-date-table"> shows some possible
     inputs for the <type>date</type> type.
    </para>

     <table id="datatype-datetime-date-table">
      <title>Date Input</title>
      <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>