Home • Explore • Blog • CI
☰



postgresql
26th chunk of `doc/src/sgml/datatype.sgml`
09b4c19ca438868d8ec4efae907328a92e4e73cfa585a2320000000100000faa
 PST</literal></entry>
          <entry>time zone specified by abbreviation</entry>
         </row>
         <row>
          <entry><literal>2003-04-12 04:05:06 America/New_York</literal></entry>
          <entry>time zone specified by full 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>Abbreviation (for Pacific Standard Time)</entry>
         </row>
         <row>
          <entry><literal>America/New_York</literal></entry>
          <entry>Full time zone name</entry>
         </row>
         <row>
          <entry><literal>PST8PDT</literal></entry>
          <entry>POSIX-style time zone specification</entry>
         </row>
         <row>
          <entry><literal>-8:00:00</literal></entry>
          <entry>UTC offset for PST</entry>
         </row>
         <row>
          <entry><literal>-8:00</literal></entry>
          <entry>UTC offset for PST (ISO 8601 extended format)</entry>
         </row>
         <row>
          <entry><literal>-800</literal></entry>
          <entry>UTC offset for PST (ISO 8601 basic format)</entry>
         </row>
         <row>
          <entry><literal>-8</literal></entry>
          <entry>UTC offset for PST (ISO 8601 basic format)</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> (also in ISO 8601)</entry>
         </row>
        </tbody>
       </tgroup>
      </table>

     <para>
     Refer to <xref linkend="datatype-timezones"/> for more information on how
     to specify time zones.
    </para>
    </sect3>

    <sect3 id="datatype-datetime-input-time-stamps">
    <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 the 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 common 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> symbol and time zone offset after
      the time.  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>,
Title: Time Stamp Input Formats
Summary
PostgreSQL supports various input formats for time stamps, including the ISO 8601 standard and common formats like 'January 8 04:05:06 1999 PST'. Time stamps can be specified with or without a time zone, and the database differentiates between 'timestamp without time zone' and 'timestamp with time zone' literals. However, PostgreSQL treats all literal strings as 'timestamp without time zone' unless explicitly specified as 'timestamp with time zone'.