<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="date-and-time-type-syntax">
      </a>
      13.2.1 Date and Time Data Type Syntax
     </h3>
    </div>
   </div>
  </div>
  <p>
   The date and time data types for representing temporal values
        are
   <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
    <code class="literal">
     DATE
    </code>
   </a>
   ,
   <a class="link" href="time.html" title="13.2.3 The TIME Type">
    <code class="literal">
     TIME
    </code>
   </a>
   ,
   <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
    <code class="literal">
     DATETIME
    </code>
   </a>
   ,
   <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
    <code class="literal">
     TIMESTAMP
    </code>
   </a>
   , and
   <a class="link" href="year.html" title="13.2.4 The YEAR Type">
    <code class="literal">
     YEAR
    </code>
   </a>
   .
  </p>
  <p>
   For the
   <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
    <code class="literal">
     DATE
    </code>
   </a>
   and
   <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
    <code class="literal">
     DATETIME
    </code>
   </a>
   range descriptions,
   <span class="quote">
    “
    <span class="quote">
     supported
    </span>
    ”
   </span>
   means that although earlier values
        might work, there is no guarantee.
  </p>
  <a class="indexterm" name="idm46045213780064">
  </a>
  <a class="indexterm" name="idm46045213778976">
  </a>
  <p>
   MySQL permits fractional seconds for
   <a class="link" href="time.html" title="13.2.3 The TIME Type">
    <code class="literal">
     TIME
    </code>
   </a>
   ,
   <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
    <code class="literal">
     DATETIME
    </code>
   </a>
   , and
   <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
    <code class="literal">
     TIMESTAMP
    </code>
   </a>
   values, with up to
        microseconds (6 digits) precision. To define a column that
        includes a fractional seconds part, use the syntax
   <code class="literal">
    <em class="replaceable">
     <code>
      type_name
     </code>
    </em>
    (
    <em class="replaceable">
     <code>
      fsp
     </code>
    </em>
    )
   </code>
   ,
        where
   <em class="replaceable">
    <code>
     type_name
    </code>
   </em>
   is
   <a class="link" href="time.html" title="13.2.3 The TIME Type">
    <code class="literal">
     TIME
    </code>
   </a>
   ,
   <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
    <code class="literal">
     DATETIME
    </code>
   </a>
   , or
   <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
    <code class="literal">
     TIMESTAMP
    </code>
   </a>
   , and
   <em class="replaceable">
    <code>
     fsp
    </code>
   </em>
   is the fractional seconds
        precision. For example:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa42011298"><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 keyword">CREATE</span> <span class="token keyword">TABLE</span> t1 <span class="token punctuation">(</span>t <span class="token function">TIME</span><span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">)</span><span class="token punctuation">,</span> dt <span class="token datatype">DATETIME</span><span class="token punctuation">(</span><span class="token number">6</span><span class="token punctuation">)</span><span class="token punctuation">,</span> ts <span class="token function">TIMESTAMP</span><span class="token punctuation">(</span><span class="token number">0</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   The
   <em class="replaceable">
    <code>
     fsp
    </code>
   </em>
   value, if given, must be in
        the range 0 to 6. A value of 0 signifies that there is no
        fractional part. If omitted, the default precision is 0. (This
        differs from the standard SQL default of 6, for compatibility
        with previous MySQL versions.)
  </p>
  <p>
   Any
   <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
    <code class="literal">
     TIMESTAMP
    </code>
   </a>
   or
   <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
    <code class="literal">
     DATETIME
    </code>
   </a>
   column in a table can
        have automatic initialization and updating properties; 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>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="indexterm" name="idm46045213758688">
      </a>
      <a class="indexterm" name="idm46045213757616">
      </a>
      <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
       <code class="literal">
        DATE
       </code>
      </a>
     </p>
     <p>
      A date. The supported range is
      <code class="literal">
       '1000-01-01'
      </code>
      to
      <code class="literal">
       '9999-12-31'
      </code>
      . MySQL displays
      <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
       <code class="literal">
        DATE
       </code>
      </a>
      values in
      <code class="literal">
       '
       <em class="replaceable">
        <code>
         YYYY-MM-DD
        </code>
       </em>
       '
      </code>
      format, but permits assignment of values to
      <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
       <code class="literal">
        DATE
       </code>
      </a>
      columns using either
            strings or numbers.
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="indexterm" name="idm46045213746720">
      </a>
      <a class="indexterm" name="idm46045213745648">
      </a>
      <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
       <code class="literal">
        DATETIME[(
        <em class="replaceable">
         <code>
          fsp
         </code>
        </em>
        )]
       </code>
      </a>
     </p>
     <p>
      A date and time combination. The supported range is
      <code class="literal">
       '1000-01-01 00:00:00.000000'
      </code>
      to
      <code class="literal">
       '9999-12-31 23:59:59.499999'
      </code>
      . MySQL
            displays
      <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
       <code class="literal">
        DATETIME
       </code>
      </a>
      values in
      <code class="literal">
       '
       <em class="replaceable">
        <code>
         YYYY-MM-DD
            hh:mm:ss
        </code>
       </em>
       [.
       <em class="replaceable">
        <code>
         fraction
        </code>
       </em>
       ]'
      </code>
      format, but permits assignment of values to
      <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
       <code class="literal">
        DATETIME
       </code>
      </a>
      columns using either
            strings or numbers.
     </p>
     <p>
      An optional
      <em class="replaceable">
       <code>
        fsp
       </code>
      </em>
      value in the
            range from 0 to 6 may be given to specify fractional seconds
            precision. A value of 0 signifies that there is no
            fractional part. If omitted, the default precision is 0.
     </p>
     <p>
      Automatic initialization and updating to the current date
            and time for
      <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
       <code class="literal">
        DATETIME
       </code>
      </a>
      columns
            can be specified using
      <code class="literal">
       DEFAULT
      </code>
      and
      <code class="literal">
       ON UPDATE
      </code>
      column definition clauses, as
            described in
      <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>
    </li>
    <li class="listitem">
     <p>
      <a class="indexterm" name="idm46045213728176">
      </a>
      <a class="indexterm" name="idm46045213727104">
      </a>
      <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
       <code class="literal">
        TIMESTAMP[(
        <em class="replaceable">
         <code>
          fsp
         </code>
        </em>
        )]
       </code>
      </a>
     </p>
     <a class="indexterm" name="idm46045213723904">
     </a>
     <p>
      A timestamp. The range is
      <code class="literal">
       '1970-01-01
            00:00:01.000000'
      </code>
      UTC to
      <code class="literal">
       '2038-01-19
            03:14:07.499999'
      </code>
      UTC.
      <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
       <code class="literal">
        TIMESTAMP
       </code>
      </a>
      values are stored
            as the number of seconds since the epoch
            (
      <code class="literal">
       '1970-01-01 00:00:00'
      </code>
      UTC). A
      <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
       <code class="literal">
        TIMESTAMP
       </code>
      </a>
      cannot represent
            the value
      <code class="literal">
       '1970-01-01 00:00:00'
      </code>
      because
            that is equivalent to 0 seconds from the epoch and the value
            0 is reserved for representing
      <code class="literal">
       '0000-00-00
            00:00:00'
      </code>
      , the
      <span class="quote">
       “
       <span class="quote">
        zero
       </span>
       ”
      </span>
      <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
       <code class="literal">
        TIMESTAMP
       </code>
      </a>
      value.
     </p>
     <p>
      An optional
      <em class="replaceable">
       <code>
        fsp
       </code>
      </em>
      value in the
            range from 0 to 6 may be given to specify fractional seconds
            precision. A value of 0 signifies that there is no
            fractional part. If omitted, the default precision is 0.
     </p>
     <p>
      The way the server handles
      <code class="literal">
       TIMESTAMP
      </code>
      definitions depends on the value of the
      <a class="link" href="server-system-variables.html#sysvar_explicit_defaults_for_timestamp">
       <code class="literal">
        explicit_defaults_for_timestamp
       </code>
      </a>
      system variable (see
      <a class="xref" href="server-system-variables.html" title="7.1.8 Server System Variables">
       Section 7.1.8, “Server System Variables”
      </a>
      ).
     </p>
     <p>
      If
      <a class="link" href="server-system-variables.html#sysvar_explicit_defaults_for_timestamp">
       <code class="literal">
        explicit_defaults_for_timestamp
       </code>
      </a>
      is enabled, there is no automatic assignment of the
      <code class="literal">
       DEFAULT CURRENT_TIMESTAMP
      </code>
      or
      <code class="literal">
       ON
            UPDATE CURRENT_TIMESTAMP
      </code>
      attributes to any
      <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
       <code class="literal">
        TIMESTAMP
       </code>
      </a>
      column. They must
            be included explicitly in the column definition. Also, any
      <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
       <code class="literal">
        TIMESTAMP
       </code>
      </a>
      not explicitly
            declared as
      <code class="literal">
       NOT NULL
      </code>
      permits
      <code class="literal">
       NULL
      </code>
      values.
     </p>
     <p>
      If
      <a class="link" href="server-system-variables.html#sysvar_explicit_defaults_for_timestamp">
       <code class="literal">
        explicit_defaults_for_timestamp
       </code>
      </a>
      is disabled, the server handles
      <code class="literal">
       TIMESTAMP
      </code>
      as follows:
     </p>
     <p>
      Unless specified otherwise, the first
      <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
       <code class="literal">
        TIMESTAMP
       </code>
      </a>
      column in a table
            is defined to be automatically set to the date and time of
            the most recent modification if not explicitly assigned a
            value. This makes
      <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
       <code class="literal">
        TIMESTAMP
       </code>
      </a>
      useful for recording the timestamp of an
      <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
       <code class="literal">
        INSERT
       </code>
      </a>
      or
      <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
       <code class="literal">
        UPDATE
       </code>
      </a>
      operation. You can
            also set any
      <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
       <code class="literal">
        TIMESTAMP
       </code>
      </a>
      column
            to the current date and time by assigning it a
      <code class="literal">
       NULL
      </code>
      value, unless it has been defined
            with the
      <code class="literal">
       NULL
      </code>
      attribute to permit
      <code class="literal">
       NULL
      </code>
      values.
     </p>
     <p>
      Automatic initialization and updating to the current date
            and time can be specified using
      <code class="literal">
       DEFAULT
            CURRENT_TIMESTAMP
      </code>
      and
      <code class="literal">
       ON UPDATE
            CURRENT_TIMESTAMP
      </code>
      column definition clauses. By
            default, the first
      <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
       <code class="literal">
        TIMESTAMP
       </code>
      </a>
      column has these properties, as previously noted. However,
            any
      <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
       <code class="literal">
        TIMESTAMP
       </code>
      </a>
      column in a
            table can be defined to have these properties.
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="indexterm" name="idm46045213683376">
      </a>
      <a class="indexterm" name="idm46045213682304">
      </a>
      <a class="link" href="time.html" title="13.2.3 The TIME Type">
       <code class="literal">
        TIME[(
        <em class="replaceable">
         <code>
          fsp
         </code>
        </em>
        )]
       </code>
      </a>
     </p>
     <p>
      A time. The range is
      <code class="literal">
       '-838:59:59.000000'
      </code>
      to
      <code class="literal">
       '838:59:59.000000'
      </code>
      . MySQL displays
      <a class="link" href="time.html" title="13.2.3 The TIME Type">
       <code class="literal">
        TIME
       </code>
      </a>
      values in
      <code class="literal">
       '
       <em class="replaceable">
        <code>
         hh:mm:ss
        </code>
       </em>
       [.
       <em class="replaceable">
        <code>
         fraction
        </code>
       </em>
       ]'
      </code>
      format, but permits assignment of values to
      <a class="link" href="time.html" title="13.2.3 The TIME Type">
       <code class="literal">
        TIME
       </code>
      </a>
      columns using either
            strings or numbers.
     </p>
     <p>
      An optional
      <em class="replaceable">
       <code>
        fsp
       </code>
      </em>
      value in the
            range from 0 to 6 may be given to specify fractional seconds
            precision. A value of 0 signifies that there is no
            fractional part. If omitted, the default precision is 0.
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="indexterm" name="idm46045213669584">
      </a>
      <a class="indexterm" name="idm46045213668512">
      </a>
      <a class="link" href="year.html" title="13.2.4 The YEAR Type">
       <code class="literal">
        YEAR[(4)]
       </code>
      </a>
     </p>
     <p>
      A year in 4-digit format. MySQL displays
      <a class="link" href="year.html" title="13.2.4 The YEAR Type">
       <code class="literal">
        YEAR
       </code>
      </a>
      values in
      <em class="replaceable">
       <code>
        YYYY
       </code>
      </em>
      format, but permits
            assignment of values to
      <a class="link" href="year.html" title="13.2.4 The YEAR Type">
       <code class="literal">
        YEAR
       </code>
      </a>
      columns using either strings or numbers. Values display as
      <code class="literal">
       1901
      </code>
      to
      <code class="literal">
       2155
      </code>
      , or
      <code class="literal">
       0000
      </code>
      .
     </p>
     <p>
      For additional information about
      <a class="link" href="year.html" title="13.2.4 The YEAR Type">
       <code class="literal">
        YEAR
       </code>
      </a>
      display format and
            interpretation of input values, see
      <a class="xref" href="year.html" title="13.2.4 The YEAR Type">
       Section 13.2.4, “The YEAR Type”
      </a>
      .
     </p>
     <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
      <div class="admon-title">
       Note
      </div>
      <p>
       The
       <a class="link" href="year.html" title="13.2.4 The YEAR Type">
        <code class="literal">
         YEAR(4)
        </code>
       </a>
       data type with
              an explicit display width is deprecated; you should expect
              support for it to be removed in a future version of MySQL.
              Instead, use
       <a class="link" href="year.html" title="13.2.4 The YEAR Type">
        <code class="literal">
         YEAR
        </code>
       </a>
       without a
              display width, which has the same meaning.
      </p>
     </div>
    </li>
   </ul>
  </div>
  <p>
   The
   <a class="link" href="aggregate-functions.html#function_sum">
    <code class="literal">
     SUM()
    </code>
   </a>
   and
   <a class="link" href="aggregate-functions.html#function_avg">
    <code class="literal">
     AVG()
    </code>
   </a>
   aggregate functions do not
        work with temporal values. (They convert the values to numbers,
        losing everything after the first nonnumeric character.) To work
        around this problem, convert to numeric units, perform the
        aggregate operation, and convert back to a temporal value.
        Examples:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa52181519"><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 keyword">SELECT</span> <span class="token function">SEC_TO_TIME</span><span class="token punctuation">(</span><span class="token function">SUM</span><span class="token punctuation">(</span><span class="token function">TIME_TO_SEC</span><span class="token punctuation">(</span><em class="replaceable">time_col</em><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token keyword">FROM</span> <em class="replaceable">tbl_name</em><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token function">FROM_DAYS</span><span class="token punctuation">(</span><span class="token function">SUM</span><span class="token punctuation">(</span><span class="token function">TO_DAYS</span><span class="token punctuation">(</span><em class="replaceable">date_col</em><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token keyword">FROM</span> <em class="replaceable">tbl_name</em><span class="token punctuation">;</span></code></pre>
  </div>
 </div>
 <br/>
</div>
