<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="datetime">
      </a>
      13.2.2 The DATE, DATETIME, and TIMESTAMP Types
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045213646144">
  </a>
  <a class="indexterm" name="idm46045213645104">
  </a>
  <a class="indexterm" name="idm46045213644032">
  </a>
  <a class="indexterm" name="idm46045213642960">
  </a>
  <a class="indexterm" name="idm46045213641472">
  </a>
  <a class="indexterm" name="idm46045213639984">
  </a>
  <p>
   The
   <code class="literal">
    DATE
   </code>
   ,
   <code class="literal">
    DATETIME
   </code>
   , and
   <code class="literal">
    TIMESTAMP
   </code>
   types are related. This section
        describes their characteristics, how they are similar, and how
        they differ. MySQL recognizes
   <code class="literal">
    DATE
   </code>
   ,
   <code class="literal">
    DATETIME
   </code>
   , and
   <code class="literal">
    TIMESTAMP
   </code>
   values in several formats, described in
   <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 the
   <code class="literal">
    DATE
   </code>
   and
   <code class="literal">
    DATETIME
   </code>
   range
        descriptions,
   <span class="quote">
    “
    <span class="quote">
     supported
    </span>
    ”
   </span>
   means that although
        earlier values might work, there is no guarantee.
  </p>
  <p>
   The
   <code class="literal">
    DATE
   </code>
   type is used for values with a date
        part but no time part. MySQL retrieves and displays
   <code class="literal">
    DATE
   </code>
   values in
   <code class="literal">
    '
    <em class="replaceable">
     <code>
      YYYY-MM-DD
     </code>
    </em>
    '
   </code>
   format. The supported range is
   <code class="literal">
    '1000-01-01'
   </code>
   to
   <code class="literal">
    '9999-12-31'
   </code>
   .
  </p>
  <p>
   The
   <code class="literal">
    DATETIME
   </code>
   type is used for values that
        contain both date and time parts. MySQL retrieves and displays
   <code class="literal">
    DATETIME
   </code>
   values in
   <code class="literal">
    '
    <em class="replaceable">
     <code>
      YYYY-MM-DD
        hh:mm:ss
     </code>
    </em>
    '
   </code>
   format. The supported range is
   <code class="literal">
    '1000-01-01 00:00:00'
   </code>
   to
   <code class="literal">
    '9999-12-31
        23:59:59'
   </code>
   .
  </p>
  <p>
   The
   <code class="literal">
    TIMESTAMP
   </code>
   data type is used for values
        that contain both date and time parts.
   <code class="literal">
    TIMESTAMP
   </code>
   has a range of
   <code class="literal">
    '1970-01-01
        00:00:01'
   </code>
   UTC to
   <code class="literal">
    '2038-01-19
        03:14:07'
   </code>
   UTC.
  </p>
  <p>
   A
   <code class="literal">
    DATETIME
   </code>
   or
   <code class="literal">
    TIMESTAMP
   </code>
   value can include a trailing fractional seconds part in up to
        microseconds (6 digits) precision. In particular, any fractional
        part in a value inserted into a
   <code class="literal">
    DATETIME
   </code>
   or
   <code class="literal">
    TIMESTAMP
   </code>
   column is stored rather than
        discarded. With the fractional part included, the format for
        these values is
   <code class="literal">
    '
    <em class="replaceable">
     <code>
      YYYY-MM-DD
        hh:mm:ss
     </code>
    </em>
    [.
    <em class="replaceable">
     <code>
      fraction
     </code>
    </em>
    ]'
   </code>
   ,
        the range for
   <code class="literal">
    DATETIME
   </code>
   values is
   <code class="literal">
    '1000-01-01 00:00:00.000000'
   </code>
   to
   <code class="literal">
    '9999-12-31 23:59:59.499999'
   </code>
   , and the range
        for
   <code class="literal">
    TIMESTAMP
   </code>
   values is
   <code class="literal">
    '1970-01-01
        00:00:01.000000'
   </code>
   to
   <code class="literal">
    '2038-01-19
        03:14:07.499999'
   </code>
   . The fractional part should always be
        separated from the rest of the time by a decimal point; no other
        fractional seconds delimiter is recognized. 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>
   .
  </p>
  <p>
   The
   <code class="literal">
    TIMESTAMP
   </code>
   and
   <code class="literal">
    DATETIME
   </code>
   data types offer automatic initialization and updating to the
        current date and time. For more information, see
   <a class="xref" href="timestamp-initialization.html" title="13.2.5 Automatic Initialization and Updating for TIMESTAMP and DATETIME">
    Section 13.2.5, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”
   </a>
   .
  </p>
  <p>
   MySQL converts
   <code class="literal">
    TIMESTAMP
   </code>
   values from the
        current time zone to UTC for storage, and back from UTC to the
        current time zone for retrieval. (This does not occur for other
        types such as
   <code class="literal">
    DATETIME
   </code>
   .) By default, the
        current time zone for each connection is the server's time. The
        time zone can be set on a per-connection basis. As long as the
        time zone setting remains constant, you get back the same value
        you store. If you store a
   <code class="literal">
    TIMESTAMP
   </code>
   value,
        and then change the time zone and retrieve the value, the
        retrieved value is different from the value you stored. This
        occurs because the same time zone was not used for conversion in
        both directions. The current time zone is available as the value
        of the
   <a class="link" href="server-system-variables.html#sysvar_time_zone">
    <code class="literal">
     time_zone
    </code>
   </a>
   system
        variable. For more information, see
   <a class="xref" href="time-zone-support.html" title="7.1.15 MySQL Server Time Zone Support">
    Section 7.1.15, “MySQL Server Time Zone Support”
   </a>
   .
  </p>
  <p>
   You can specify a time zone offset when inserting a
   <code class="literal">
    TIMESTAMP
   </code>
   or
   <code class="literal">
    DATETIME
   </code>
   value into a table. 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 more information
        and examples.
  </p>
  <p>
   Invalid
   <code class="literal">
    DATE
   </code>
   ,
   <code class="literal">
    DATETIME
   </code>
   , or
   <code class="literal">
    TIMESTAMP
   </code>
   values are converted to the
   <span class="quote">
    “
    <span class="quote">
     zero
    </span>
    ”
   </span>
   value of the appropriate type
        (
   <code class="literal">
    '0000-00-00'
   </code>
   or
   <code class="literal">
    '0000-00-00
        00:00:00'
   </code>
   ), if the SQL mode permits this conversion.
        The precise behavior depends on which if any of strict SQL mode
        and the
   <a class="link" href="sql-mode.html#sqlmode_no_zero_date">
    <code class="literal">
     NO_ZERO_DATE
    </code>
   </a>
   SQL mode
        are enabled; see
   <a class="xref" href="sql-mode.html" title="7.1.11 Server SQL Modes">
    Section 7.1.11, “Server SQL Modes”
   </a>
   .
  </p>
  <p>
   You can convert
   <code class="literal">
    TIMESTAMP
   </code>
   values to UTC
   <code class="literal">
    DATETIME
   </code>
   values when retrieving them using
   <a class="link" href="cast-functions.html#function_cast">
    <code class="literal">
     CAST()
    </code>
   </a>
   with the
   <code class="literal">
    AT TIME
        ZONE
   </code>
   operator, as shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa94411735"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> col<span class="token punctuation">,</span>
     <span class="token operator">&gt;</span>     <span class="token function">CAST</span><span class="token punctuation">(</span>col <span class="token keyword">AT</span> <span class="token datatype">TIME</span> ZONE <span class="token keyword">INTERVAL</span> <span class="token string">'+00:00'</span> <span class="token keyword">AS</span> <span class="token datatype">DATETIME</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> ut
     <span class="token operator">&gt;</span>     <span class="token keyword">FROM</span> ts <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> id<span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> col                 <span class="token punctuation">|</span> ut                  <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> 2020<span class="token punctuation">-</span>01<span class="token punctuation">-</span>01 10:10:10 <span class="token punctuation">|</span> 2020<span class="token punctuation">-</span>01<span class="token punctuation">-</span>01 15:10:10 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2019<span class="token punctuation">-</span>12<span class="token punctuation">-</span>31 23:40:10 <span class="token punctuation">|</span> 2020<span class="token punctuation">-</span>01<span class="token punctuation">-</span>01 04:40:10 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2020<span class="token punctuation">-</span>01<span class="token punctuation">-</span>01 13:10:10 <span class="token punctuation">|</span> 2020<span class="token punctuation">-</span>01<span class="token punctuation">-</span>01 18:10:10 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2020<span class="token punctuation">-</span>01<span class="token punctuation">-</span>01 10:10:10 <span class="token punctuation">|</span> 2020<span class="token punctuation">-</span>01<span class="token punctuation">-</span>01 15:10:10 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2020<span class="token punctuation">-</span>01<span class="token punctuation">-</span>01 04:40:10 <span class="token punctuation">|</span> 2020<span class="token punctuation">-</span>01<span class="token punctuation">-</span>01 09:40:10 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2020<span class="token punctuation">-</span>01<span class="token punctuation">-</span>01 18:10:10 <span class="token punctuation">|</span> 2020<span class="token punctuation">-</span>01<span class="token punctuation">-</span>01 23:10:10 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
  </div>
  <p>
   For complete information regarding syntax and additional
        examples, see the description of the
   <a class="link" href="cast-functions.html#function_cast">
    <code class="literal">
     CAST()
    </code>
   </a>
   function.
  </p>
  <a class="indexterm" name="idm46045213581760">
  </a>
  <a class="indexterm" name="idm46045213580272">
  </a>
  <p>
   Be aware of certain properties of date value interpretation in
        MySQL:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      MySQL permits a
      <span class="quote">
       “
       <span class="quote">
        relaxed
       </span>
       ”
      </span>
      format for values
            specified as strings, in which any punctuation character may
            be used as the delimiter between date parts or time parts.
            In some cases, this syntax can be deceiving. For example, a
            value such as
      <code class="literal">
       '10:11:12'
      </code>
      might look like
            a time value because of the
      <code class="literal">
       :
      </code>
      , but is
            interpreted as the year
      <code class="literal">
       '2010-11-12'
      </code>
      if
            used in date context. The value
      <code class="literal">
       '10:45:15'
      </code>
      is converted to
      <code class="literal">
       '0000-00-00'
      </code>
      because
      <code class="literal">
       '45'
      </code>
      is not a valid month.
     </p>
     <p>
      The only delimiter recognized between a date and time part
            and a fractional seconds part is the decimal point.
     </p>
    </li>
    <li class="listitem">
     <p>
      The server requires that month and day values be valid, and
            not merely in the range 1 to 12 and 1 to 31, respectively.
            With strict mode disabled, invalid dates such as
      <code class="literal">
       '2004-04-31'
      </code>
      are converted to
      <code class="literal">
       '0000-00-00'
      </code>
      and a warning is generated.
            With strict mode enabled, invalid dates generate an error.
            To permit such dates, enable
      <a class="link" href="sql-mode.html#sqlmode_allow_invalid_dates">
       <code class="literal">
        ALLOW_INVALID_DATES
       </code>
      </a>
      . See
      <a class="xref" href="sql-mode.html" title="7.1.11 Server SQL Modes">
       Section 7.1.11, “Server SQL Modes”
      </a>
      , for more information.
     </p>
    </li>
    <li class="listitem">
     <p>
      MySQL does not accept
      <code class="literal">
       TIMESTAMP
      </code>
      values
            that include a zero in the day or month column or values
            that are not a valid date. The sole exception to this rule
            is the special
      <span class="quote">
       “
       <span class="quote">
        zero
       </span>
       ”
      </span>
      value
      <code class="literal">
       '0000-00-00 00:00:00'
      </code>
      , if the SQL mode
            permits this value. The precise behavior depends on which if
            any of strict SQL mode and the
      <a class="link" href="sql-mode.html#sqlmode_no_zero_date">
       <code class="literal">
        NO_ZERO_DATE
       </code>
      </a>
      SQL mode are
            enabled; see
      <a class="xref" href="sql-mode.html" title="7.1.11 Server SQL Modes">
       Section 7.1.11, “Server SQL Modes”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Dates containing 2-digit year values are ambiguous because
            the century is unknown. MySQL interprets 2-digit year values
            using these rules:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         Year values in the range
         <code class="literal">
          00-69
         </code>
         become
         <code class="literal">
          2000-2069
         </code>
         .
        </p>
       </li>
       <li class="listitem">
        <p>
         Year values in the range
         <code class="literal">
          70-99
         </code>
         become
         <code class="literal">
          1970-1999
         </code>
         .
        </p>
       </li>
      </ul>
     </div>
     <p>
      See also
      <a class="xref" href="two-digit-years.html" title="13.2.9 2-Digit Years in Dates">
       Section 13.2.9, “2-Digit Years in Dates”
      </a>
      .
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
