Home • Explore • Blog • CI
☰



postgresql
35th chunk of `doc/src/sgml/datatype.sgml`
6f5c5db519caa877833003b067ba0050f66cf4d9953c36d70000000100000fb2
 as ISO 8601 time intervals, using
     either the <quote>format with designators</quote> of the standard's section
     4.4.3.2 or the <quote>alternative format</quote> of section 4.4.3.3.  The
     format with designators looks like this:
<synopsis>
P <replaceable>quantity</replaceable> <replaceable>unit</replaceable> <optional> <replaceable>quantity</replaceable> <replaceable>unit</replaceable> ...</optional> <optional> T <optional> <replaceable>quantity</replaceable> <replaceable>unit</replaceable> ...</optional></optional>
</synopsis>
      The string must start with a <literal>P</literal>, and may include a
      <literal>T</literal> that introduces the time-of-day units.  The
      available unit abbreviations are given in <xref
      linkend="datatype-interval-iso8601-units"/>.  Units may be
      omitted, and may be specified in any order, but units smaller than
      a day must appear after <literal>T</literal>.  In particular, the meaning of
      <literal>M</literal> depends on whether it is before or after
      <literal>T</literal>.
     </para>

     <table id="datatype-interval-iso8601-units">
      <title>ISO 8601 Interval Unit Abbreviations</title>
     <tgroup cols="2">
       <thead>
        <row>
         <entry>Abbreviation</entry>
         <entry>Meaning</entry>
        </row>
       </thead>
       <tbody>
        <row>
         <entry>Y</entry>
         <entry>Years</entry>
        </row>
        <row>
         <entry>M</entry>
         <entry>Months (in the date part)</entry>
        </row>
        <row>
         <entry>W</entry>
         <entry>Weeks</entry>
        </row>
        <row>
         <entry>D</entry>
         <entry>Days</entry>
        </row>
        <row>
         <entry>H</entry>
         <entry>Hours</entry>
        </row>
        <row>
         <entry>M</entry>
         <entry>Minutes (in the time part)</entry>
        </row>
        <row>
         <entry>S</entry>
         <entry>Seconds</entry>
        </row>
       </tbody>
      </tgroup>
     </table>

     <para>
      In the alternative format:
<synopsis>
P <optional> <replaceable>years</replaceable>-<replaceable>months</replaceable>-<replaceable>days</replaceable> </optional> <optional> T <replaceable>hours</replaceable>:<replaceable>minutes</replaceable>:<replaceable>seconds</replaceable> </optional>
</synopsis>
      the string must begin with <literal>P</literal>, and a
      <literal>T</literal> separates the date and time parts of the interval.
      The values are given as numbers similar to ISO 8601 dates.
    </para>

    <para>
     When writing an interval constant with a <replaceable>fields</replaceable>
     specification, or when assigning a string to an interval column that was
     defined with a <replaceable>fields</replaceable> specification, the interpretation of
     unmarked quantities depends on the <replaceable>fields</replaceable>.  For
     example <literal>INTERVAL '1' YEAR</literal> is read as 1 year, whereas
     <literal>INTERVAL '1'</literal> means 1 second.  Also, field values
     <quote>to the right</quote> of the least significant field allowed by the
     <replaceable>fields</replaceable> specification are silently discarded.  For
     example, writing <literal>INTERVAL '1 day 2:03:04' HOUR TO MINUTE</literal>
     results in dropping the seconds field, but not the day field.
    </para>

    <para>
     According to the <acronym>SQL</acronym> standard all fields of an interval
     value must have the same sign, so a leading negative sign applies to all
     fields; for example the negative sign in the interval literal
     <literal>'-1 2:03:04'</literal> applies to both the days and hour/minute/second
     parts.  <productname>PostgreSQL</productname> allows the fields to have different
     signs, and traditionally treats each field in the textual representation
     as independently signed, so that the hour/minute/second part is
     considered positive in this example.  If <varname>IntervalStyle</varname>
Title: ISO 8601 Interval Format and Interval Constants
Summary
The ISO 8601 interval format is used to represent intervals in PostgreSQL, with two possible formats: the format with designators and the alternative format. The format with designators uses a 'P' prefix, followed by quantities and units, and an optional 'T' to introduce time-of-day units. The alternative format uses a 'P' prefix, followed by years, months, and days, and an optional 'T' to separate the date and time parts. Interval constants can be written with a fields specification, which affects the interpretation of unmarked quantities, and PostgreSQL allows fields to have different signs, unlike the SQL standard.