<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="time">
      </a>
      13.2.3 The TIME Type
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045213553680">
  </a>
  <a class="indexterm" name="idm46045213552608">
  </a>
  <p>
   MySQL retrieves and displays
   <code class="literal">
    TIME
   </code>
   values in
   <em class="replaceable">
    <code>
     'hh:mm:ss'
    </code>
   </em>
   format (or
   <em class="replaceable">
    <code>
     'hhh:mm:ss'
    </code>
   </em>
   format for large hours
        values).
   <code class="literal">
    TIME
   </code>
   values may range from
   <code class="literal">
    '-838:59:59'
   </code>
   to
   <code class="literal">
    '838:59:59'
   </code>
   . The hours part may be so large
        because the
   <code class="literal">
    TIME
   </code>
   type can be used not only to
        represent a time of day (which must be less than 24 hours), but
        also elapsed time or a time interval between two events (which
        may be much greater than 24 hours, or even negative).
  </p>
  <p>
   MySQL recognizes
   <code class="literal">
    TIME
   </code>
   values in several
        formats, some of which can include a trailing fractional seconds
        part in up to microseconds (6 digits) precision. See
   <a class="xref" href="date-and-time-literals.html" title="11.1.3 Date and Time Literals">
    Section 11.1.3, “Date and Time Literals”
   </a>
   . For information about
        fractional seconds support in MySQL, see
   <a class="xref" href="fractional-seconds.html" title="13.2.6 Fractional Seconds in Time Values">
    Section 13.2.6, “Fractional Seconds in Time Values”
   </a>
   . In particular, any
        fractional part in a value inserted into a
   <code class="literal">
    TIME
   </code>
   column is stored rather than discarded.
        With the fractional part included, the range for
   <code class="literal">
    TIME
   </code>
   values is
   <code class="literal">
    '-838:59:59.000000'
   </code>
   to
   <code class="literal">
    '838:59:59.000000'
   </code>
   .
  </p>
  <p>
   Be careful about assigning abbreviated values to a
   <code class="literal">
    TIME
   </code>
   column. MySQL interprets abbreviated
   <code class="literal">
    TIME
   </code>
   values with colons as time of the day.
        That is,
   <code class="literal">
    '11:12'
   </code>
   means
   <code class="literal">
    '11:12:00'
   </code>
   , not
   <code class="literal">
    '00:11:12'
   </code>
   . MySQL interprets abbreviated
        values without colons using the assumption that the two
        rightmost digits represent seconds (that is, as elapsed time
        rather than as time of day). For example, you might think of
   <code class="literal">
    '1112'
   </code>
   and
   <code class="literal">
    1112
   </code>
   as meaning
   <code class="literal">
    '11:12:00'
   </code>
   (12 minutes after 11 o'clock), but
        MySQL interprets them as
   <code class="literal">
    '00:11:12'
   </code>
   (11
        minutes, 12 seconds). Similarly,
   <code class="literal">
    '12'
   </code>
   and
   <code class="literal">
    12
   </code>
   are interpreted as
   <code class="literal">
    '00:00:12'
   </code>
   .
  </p>
  <p>
   The only delimiter recognized between a time part and a
        fractional seconds part is the decimal point.
  </p>
  <p>
   By default, values that lie outside the
   <code class="literal">
    TIME
   </code>
   range but are otherwise valid are clipped to the closest
        endpoint of the range. For example,
   <code class="literal">
    '-850:00:00'
   </code>
   and
   <code class="literal">
    '850:00:00'
   </code>
   are converted to
   <code class="literal">
    '-838:59:59'
   </code>
   and
   <code class="literal">
    '838:59:59'
   </code>
   . Invalid
   <code class="literal">
    TIME
   </code>
   values are converted to
   <code class="literal">
    '00:00:00'
   </code>
   . Note that
        because
   <code class="literal">
    '00:00:00'
   </code>
   is itself a valid
   <code class="literal">
    TIME
   </code>
   value, there is no way to tell, from a
        value of
   <code class="literal">
    '00:00:00'
   </code>
   stored in a table,
        whether the original value was specified as
   <code class="literal">
    '00:00:00'
   </code>
   or whether it was invalid.
  </p>
  <p>
   For more restrictive treatment of invalid
   <code class="literal">
    TIME
   </code>
   values, enable strict SQL mode to cause
        errors to occur. See
   <a class="xref" href="sql-mode.html" title="7.1.11 Server SQL Modes">
    Section 7.1.11, “Server SQL Modes”
   </a>
   .
  </p>
 </div>
 <br/>
</div>
