<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h2 class="title">
      <a name="expressions">
      </a>
      11.5 Expressions
     </h2>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045218613088">
  </a>
  <p>
   This section lists the grammar rules that expressions must follow
      in MySQL and provides additional information about the types of
      terms that may appear in expressions.
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="xref" href="expressions.html#expression-syntax" title="Expression Syntax">
       Expression Syntax
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="expressions.html#expression-term-notes" title="Expression Term Notes">
       Expression Term Notes
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="expressions.html#temporal-intervals" title="Temporal Intervals">
       Temporal Intervals
      </a>
     </p>
    </li>
   </ul>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h3 class="title">
       <a name="expression-syntax">
       </a>
       Expression Syntax
      </h3>
     </div>
    </div>
   </div>
   <p>
    The following grammar rules define expression syntax in MySQL.
        The grammar shown here is based on that given in the
    <code class="filename">
     sql/sql_yacc.yy
    </code>
    file of MySQL source
        distributions. For additional information about some of the
        expression terms, see
    <a class="xref" href="expressions.html#expression-term-notes" title="Expression Term Notes">
     Expression Term Notes
    </a>
    .
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa22441447"><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"><em class="replaceable">expr</em>:
    <em class="replaceable">expr</em> <span class="token operator">OR</span> <em class="replaceable">expr</em>
  <span class="token operator">|</span> <em class="replaceable">expr</em> <span class="token operator">||</span> <em class="replaceable">expr</em>
  <span class="token operator">|</span> <em class="replaceable">expr</em> <span class="token keyword">XOR</span> <em class="replaceable">expr</em>
  <span class="token operator">|</span> <em class="replaceable">expr</em> <span class="token operator">AND</span> <em class="replaceable">expr</em>
  <span class="token operator">|</span> <em class="replaceable">expr</em> <span class="token operator">&amp;&amp;</span> <em class="replaceable">expr</em>
  <span class="token operator">|</span> <span class="token operator">NOT</span> <em class="replaceable">expr</em>
  <span class="token operator">|</span> <span class="token operator">!</span> <em class="replaceable">expr</em>
  <span class="token operator">|</span> <em class="replaceable">boolean_primary</em> <span class="token operator">IS</span> <span class="token punctuation">[</span><span class="token operator">NOT</span><span class="token punctuation">]</span> {<span class="token boolean">TRUE</span> <span class="token operator">|</span> <span class="token boolean">FALSE</span> <span class="token operator">|</span> <span class="token keyword">UNKNOWN</span>}
  <span class="token operator">|</span> <em class="replaceable">boolean_primary</em>

<em class="replaceable">boolean_primary</em>:
    <em class="replaceable">boolean_primary</em> <span class="token operator">IS</span> <span class="token punctuation">[</span><span class="token operator">NOT</span><span class="token punctuation">]</span> <span class="token boolean">NULL</span>
  <span class="token operator">|</span> <em class="replaceable">boolean_primary</em> <span class="token operator">&lt;=&gt;</span> <em class="replaceable">predicate</em>
  <span class="token operator">|</span> <em class="replaceable">boolean_primary</em> <em class="replaceable">comparison_operator</em> <em class="replaceable">predicate</em>
  <span class="token operator">|</span> <em class="replaceable">boolean_primary</em> <em class="replaceable">comparison_operator</em> {<span class="token keyword">ALL</span> <span class="token operator">|</span> <span class="token keyword">ANY</span>} <span class="token punctuation">(</span><em class="replaceable">subquery</em><span class="token punctuation">)</span>
  <span class="token operator">|</span> <em class="replaceable">predicate</em>

<em class="replaceable">comparison_operator</em>: <span class="token operator">=</span> <span class="token operator">|</span> <span class="token operator">&gt;=</span> <span class="token operator">|</span> <span class="token operator">&gt;</span> <span class="token operator">|</span> <span class="token operator">&lt;=</span> <span class="token operator">|</span> <span class="token operator">&lt;</span> <span class="token operator">|</span> <span class="token operator">&lt;&gt;</span> <span class="token operator">|</span> <span class="token operator">!=</span>

<em class="replaceable">predicate</em>:
    <em class="replaceable">bit_expr</em> <span class="token punctuation">[</span><span class="token operator">NOT</span><span class="token punctuation">]</span> <span class="token keyword">IN</span> <span class="token punctuation">(</span><em class="replaceable">subquery</em><span class="token punctuation">)</span>
  <span class="token operator">|</span> <em class="replaceable">bit_expr</em> <span class="token punctuation">[</span><span class="token operator">NOT</span><span class="token punctuation">]</span> <span class="token keyword">IN</span> <span class="token punctuation">(</span><em class="replaceable">expr</em> <span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">expr</em><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 operator">|</span> <em class="replaceable">bit_expr</em> <span class="token punctuation">[</span><span class="token operator">NOT</span><span class="token punctuation">]</span> <span class="token operator">BETWEEN</span> <em class="replaceable">bit_expr</em> <span class="token operator">AND</span> <em class="replaceable">predicate</em>
  <span class="token operator">|</span> <em class="replaceable">bit_expr</em> <span class="token keyword">SOUNDS</span> <span class="token operator">LIKE</span> <em class="replaceable">bit_expr</em>
  <span class="token operator">|</span> <em class="replaceable">bit_expr</em> <span class="token punctuation">[</span><span class="token operator">NOT</span><span class="token punctuation">]</span> <span class="token operator">LIKE</span> <em class="replaceable">simple_expr</em> <span class="token punctuation">[</span><span class="token keyword">ESCAPE</span> <em class="replaceable">simple_expr</em><span class="token punctuation">]</span>
  <span class="token operator">|</span> <em class="replaceable">bit_expr</em> <span class="token punctuation">[</span><span class="token operator">NOT</span><span class="token punctuation">]</span> <span class="token operator">REGEXP</span> <em class="replaceable">bit_expr</em>
  <span class="token operator">|</span> <em class="replaceable">bit_expr</em>

<em class="replaceable">bit_expr</em>:
    <em class="replaceable">bit_expr</em> <span class="token operator">|</span> <em class="replaceable">bit_expr</em>
  <span class="token operator">|</span> <em class="replaceable">bit_expr</em> <span class="token operator">&amp;</span> <em class="replaceable">bit_expr</em>
  <span class="token operator">|</span> <em class="replaceable">bit_expr</em> <span class="token operator">&lt;&lt;</span> <em class="replaceable">bit_expr</em>
  <span class="token operator">|</span> <em class="replaceable">bit_expr</em> <span class="token operator">&gt;&gt;</span> <em class="replaceable">bit_expr</em>
  <span class="token operator">|</span> <em class="replaceable">bit_expr</em> <span class="token operator">+</span> <em class="replaceable">bit_expr</em>
  <span class="token operator">|</span> <em class="replaceable">bit_expr</em> <span class="token operator">-</span> <em class="replaceable">bit_expr</em>
  <span class="token operator">|</span> <em class="replaceable">bit_expr</em> <span class="token operator">*</span> <em class="replaceable">bit_expr</em>
  <span class="token operator">|</span> <em class="replaceable">bit_expr</em> <span class="token operator">/</span> <em class="replaceable">bit_expr</em>
  <span class="token operator">|</span> <em class="replaceable">bit_expr</em> <span class="token operator">DIV</span> <em class="replaceable">bit_expr</em>
  <span class="token operator">|</span> <em class="replaceable">bit_expr</em> <span class="token operator">MOD</span> <em class="replaceable">bit_expr</em>
  <span class="token operator">|</span> <em class="replaceable">bit_expr</em> <span class="token operator">%</span> <em class="replaceable">bit_expr</em>
  <span class="token operator">|</span> <em class="replaceable">bit_expr</em> <span class="token operator">^</span> <em class="replaceable">bit_expr</em>
  <span class="token operator">|</span> <em class="replaceable">bit_expr</em> <span class="token operator">+</span> <em class="replaceable">interval_expr</em>
  <span class="token operator">|</span> <em class="replaceable">bit_expr</em> <span class="token operator">-</span> <em class="replaceable">interval_expr</em>
  <span class="token operator">|</span> <em class="replaceable">simple_expr</em>

<em class="replaceable">simple_expr</em>:
    <em class="replaceable">literal</em>
  <span class="token operator">|</span> <em class="replaceable">identifier</em>
  <span class="token operator">|</span> <em class="replaceable">function_call</em>
  <span class="token operator">|</span> <em class="replaceable">simple_expr</em> <span class="token keyword">COLLATE</span> <em class="replaceable">collation_name</em>
  <span class="token operator">|</span> <em class="replaceable">param_marker</em>
  <span class="token operator">|</span> <em class="replaceable">variable</em>
  <span class="token operator">|</span> <em class="replaceable">simple_expr</em> <span class="token operator">||</span> <em class="replaceable">simple_expr</em>
  <span class="token operator">|</span> <span class="token operator">+</span> <em class="replaceable">simple_expr</em>
  <span class="token operator">|</span> <span class="token operator">-</span> <em class="replaceable">simple_expr</em>
  <span class="token operator">|</span> <span class="token operator">~</span> <em class="replaceable">simple_expr</em>
  <span class="token operator">|</span> <span class="token operator">!</span> <em class="replaceable">simple_expr</em>
  <span class="token operator">|</span> <span class="token datatype">BINARY</span> <em class="replaceable">simple_expr</em>
  <span class="token operator">|</span> <span class="token punctuation">(</span><em class="replaceable">expr</em> <span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">expr</em><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 operator">|</span> <span class="token keyword">ROW</span> <span class="token punctuation">(</span><em class="replaceable">expr</em><span class="token punctuation">,</span> <em class="replaceable">expr</em> <span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">expr</em><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 operator">|</span> <span class="token punctuation">(</span><em class="replaceable">subquery</em><span class="token punctuation">)</span>
  <span class="token operator">|</span> <span class="token keyword">EXISTS</span> <span class="token punctuation">(</span><em class="replaceable">subquery</em><span class="token punctuation">)</span>
  <span class="token operator">|</span> {<em class="replaceable">identifier</em> <em class="replaceable">expr</em>}
  <span class="token operator">|</span> <em class="replaceable">match_expr</em>
  <span class="token operator">|</span> <em class="replaceable">case_expr</em>
  <span class="token operator">|</span> <em class="replaceable">interval_expr</em></code></pre>
   </div>
   <p>
    For operator precedence, see
    <a class="xref" href="operator-precedence.html" title="14.4.1 Operator Precedence">
     Section 14.4.1, “Operator Precedence”
    </a>
    . The precedence and
        meaning of some operators depends on the SQL mode:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       By default,
       <a class="link" href="logical-operators.html#operator_or">
        <code class="literal">
         ||
        </code>
       </a>
       is a logical
       <a class="link" href="logical-operators.html#operator_or">
        <code class="literal">
         OR
        </code>
       </a>
       operator. With
       <a class="link" href="sql-mode.html#sqlmode_pipes_as_concat">
        <code class="literal">
         PIPES_AS_CONCAT
        </code>
       </a>
       enabled,
       <a class="link" href="logical-operators.html#operator_or">
        <code class="literal">
         ||
        </code>
       </a>
       is string
            concatenation, with a precedence between
       <a class="link" href="bit-functions.html#operator_bitwise-xor">
        <code class="literal">
         ^
        </code>
       </a>
       and
            the unary operators.
      </p>
     </li>
     <li class="listitem">
      <p>
       By default,
       <a class="link" href="logical-operators.html#operator_not">
        <code class="literal">
         !
        </code>
       </a>
       has a higher precedence than
       <code class="literal">
        NOT
       </code>
       . With
       <a class="link" href="sql-mode.html#sqlmode_high_not_precedence">
        <code class="literal">
         HIGH_NOT_PRECEDENCE
        </code>
       </a>
       enabled,
       <a class="link" href="logical-operators.html#operator_not">
        <code class="literal">
         !
        </code>
       </a>
       and
       <code class="literal">
        NOT
       </code>
       have the same precedence.
      </p>
     </li>
    </ul>
   </div>
   <p>
    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>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h3 class="title">
       <a name="expression-term-notes">
       </a>
       Expression Term Notes
      </h3>
     </div>
    </div>
   </div>
   <p>
    For literal value syntax, see
    <a class="xref" href="literals.html" title="11.1 Literal Values">
     Section 11.1, “Literal Values”
    </a>
    .
   </p>
   <p>
    For identifier syntax, see
    <a class="xref" href="identifiers.html" title="11.2 Schema Object Names">
     Section 11.2, “Schema Object Names”
    </a>
    .
   </p>
   <p>
    Variables can be user variables, system variables, or stored
        program local variables or parameters:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       User variables:
       <a class="xref" href="user-variables.html" title="11.4 User-Defined Variables">
        Section 11.4, “User-Defined Variables”
       </a>
      </p>
     </li>
     <li class="listitem">
      <p>
       System variables:
       <a class="xref" href="using-system-variables.html" title="7.1.9 Using System Variables">
        Section 7.1.9, “Using System Variables”
       </a>
      </p>
     </li>
     <li class="listitem">
      <p>
       Stored program local variables:
       <a class="xref" href="declare-local-variable.html" title="15.6.4.1 Local Variable DECLARE Statement">
        Section 15.6.4.1, “Local Variable DECLARE Statement”
       </a>
      </p>
     </li>
     <li class="listitem">
      <p>
       Stored program parameters:
       <a class="xref" href="create-procedure.html" title="15.1.17 CREATE PROCEDURE and CREATE FUNCTION Statements">
        Section 15.1.17, “CREATE PROCEDURE and CREATE FUNCTION Statements”
       </a>
      </p>
     </li>
    </ul>
   </div>
   <p>
    <em class="replaceable">
     <code>
      param_marker
     </code>
    </em>
    is
    <code class="literal">
     ?
    </code>
    as used in prepared statements for placeholders. See
    <a class="xref" href="prepare.html" title="15.5.1 PREPARE Statement">
     Section 15.5.1, “PREPARE Statement”
    </a>
    .
   </p>
   <p>
    <code class="literal">
     (
     <em class="replaceable">
      <code>
       subquery
      </code>
     </em>
     )
    </code>
    indicates a subquery that returns a single value; that is, a
        scalar subquery. See
    <a class="xref" href="scalar-subqueries.html" title="15.2.15.1 The Subquery as Scalar Operand">
     Section 15.2.15.1, “The Subquery as Scalar Operand”
    </a>
    .
   </p>
   <p>
    <code class="literal">
     {
     <em class="replaceable">
      <code>
       identifier
      </code>
     </em>
     <em class="replaceable">
      <code>
       expr
      </code>
     </em>
     }
    </code>
    is ODBC escape syntax
        and is accepted for ODBC compatibility. The value is
    <em class="replaceable">
     <code>
      expr
     </code>
    </em>
    . The
    <code class="literal">
     {
    </code>
    and
    <code class="literal">
     }
    </code>
    curly braces in the syntax should be
        written literally; they are not metasyntax as used elsewhere in
        syntax descriptions.
   </p>
   <p>
    <em class="replaceable">
     <code>
      match_expr
     </code>
    </em>
    indicates a
    <a class="link" href="fulltext-search.html#function_match">
     <code class="literal">
      MATCH
     </code>
    </a>
    expression. See
    <a class="xref" href="fulltext-search.html" title="14.9 Full-Text Search Functions">
     Section 14.9, “Full-Text Search Functions”
    </a>
    .
   </p>
   <p>
    <em class="replaceable">
     <code>
      case_expr
     </code>
    </em>
    indicates a
    <a class="link" href="flow-control-functions.html#operator_case">
     <code class="literal">
      CASE
     </code>
    </a>
    expression. See
    <a class="xref" href="flow-control-functions.html" title="14.5 Flow Control Functions">
     Section 14.5, “Flow Control Functions”
    </a>
    .
   </p>
   <p>
    <em class="replaceable">
     <code>
      interval_expr
     </code>
    </em>
    represents a temporal
        interval. See
    <a class="xref" href="expressions.html#temporal-intervals" title="Temporal Intervals">
     Temporal Intervals
    </a>
    .
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h3 class="title">
       <a name="temporal-intervals">
       </a>
       Temporal Intervals
      </h3>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045218514928">
   </a>
   <a class="indexterm" name="idm46045218513424">
   </a>
   <a class="indexterm" name="idm46045218512336">
   </a>
   <p>
    <em class="replaceable">
     <code>
      interval_expr
     </code>
    </em>
    in expressions
        represents a temporal interval. Intervals have this syntax:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa67918453"><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">INTERVAL</span> <em class="replaceable">expr</em> <em class="replaceable">unit</em></code></pre>
   </div>
   <p>
    <em class="replaceable">
     <code>
      expr
     </code>
    </em>
    represents a quantity.
    <em class="replaceable">
     <code>
      unit
     </code>
    </em>
    represents the unit for
        interpreting the quantity; it is a specifier such as
    <code class="literal">
     HOUR
    </code>
    ,
    <code class="literal">
     DAY
    </code>
    , or
    <code class="literal">
     WEEK
    </code>
    . The
    <code class="literal">
     INTERVAL
    </code>
    keyword
        and the
    <em class="replaceable">
     <code>
      unit
     </code>
    </em>
    specifier are not
        case-sensitive.
   </p>
   <p>
    The following table shows the expected form of the
    <em class="replaceable">
     <code>
      expr
     </code>
    </em>
    argument for each
    <em class="replaceable">
     <code>
      unit
     </code>
    </em>
    value.
   </p>
   <div class="table">
    <a name="temporal-interval-arguments">
    </a>
    <p class="title">
     <b>
      Table 11.2 Temporal Interval Expression and Unit Arguments
     </b>
    </p>
    <div class="table-contents">
     <table summary="unit values and the expected expr argument for each unit value.">
      <colgroup>
       <col style="width: 50%"/>
       <col style="width: 50%"/>
      </colgroup>
      <thead>
       <tr>
        <th>
         <em class="replaceable">
          <code>
           unit
          </code>
         </em>
         Value
        </th>
        <th>
         Expected
         <em class="replaceable">
          <code>
           expr
          </code>
         </em>
         Format
        </th>
       </tr>
      </thead>
      <tbody>
       <tr>
        <td>
         <code class="literal">
          MICROSECOND
         </code>
        </td>
        <td>
         <code class="literal">
          MICROSECONDS
         </code>
        </td>
       </tr>
       <tr>
        <td>
         <code class="literal">
          SECOND
         </code>
        </td>
        <td>
         <code class="literal">
          SECONDS
         </code>
        </td>
       </tr>
       <tr>
        <td>
         <code class="literal">
          MINUTE
         </code>
        </td>
        <td>
         <code class="literal">
          MINUTES
         </code>
        </td>
       </tr>
       <tr>
        <td>
         <code class="literal">
          HOUR
         </code>
        </td>
        <td>
         <code class="literal">
          HOURS
         </code>
        </td>
       </tr>
       <tr>
        <td>
         <code class="literal">
          DAY
         </code>
        </td>
        <td>
         <code class="literal">
          DAYS
         </code>
        </td>
       </tr>
       <tr>
        <td>
         <code class="literal">
          WEEK
         </code>
        </td>
        <td>
         <code class="literal">
          WEEKS
         </code>
        </td>
       </tr>
       <tr>
        <td>
         <code class="literal">
          MONTH
         </code>
        </td>
        <td>
         <code class="literal">
          MONTHS
         </code>
        </td>
       </tr>
       <tr>
        <td>
         <code class="literal">
          QUARTER
         </code>
        </td>
        <td>
         <code class="literal">
          QUARTERS
         </code>
        </td>
       </tr>
       <tr>
        <td>
         <code class="literal">
          YEAR
         </code>
        </td>
        <td>
         <code class="literal">
          YEARS
         </code>
        </td>
       </tr>
       <tr>
        <td>
         <code class="literal">
          SECOND_MICROSECOND
         </code>
        </td>
        <td>
         <code class="literal">
          'SECONDS.MICROSECONDS'
         </code>
        </td>
       </tr>
       <tr>
        <td>
         <code class="literal">
          MINUTE_MICROSECOND
         </code>
        </td>
        <td>
         <code class="literal">
          'MINUTES:SECONDS.MICROSECONDS'
         </code>
        </td>
       </tr>
       <tr>
        <td>
         <code class="literal">
          MINUTE_SECOND
         </code>
        </td>
        <td>
         <code class="literal">
          'MINUTES:SECONDS'
         </code>
        </td>
       </tr>
       <tr>
        <td>
         <code class="literal">
          HOUR_MICROSECOND
         </code>
        </td>
        <td>
         <code class="literal">
          'HOURS:MINUTES:SECONDS.MICROSECONDS'
         </code>
        </td>
       </tr>
       <tr>
        <td>
         <code class="literal">
          HOUR_SECOND
         </code>
        </td>
        <td>
         <code class="literal">
          'HOURS:MINUTES:SECONDS'
         </code>
        </td>
       </tr>
       <tr>
        <td>
         <code class="literal">
          HOUR_MINUTE
         </code>
        </td>
        <td>
         <code class="literal">
          'HOURS:MINUTES'
         </code>
        </td>
       </tr>
       <tr>
        <td>
         <code class="literal">
          DAY_MICROSECOND
         </code>
        </td>
        <td>
         <code class="literal">
          'DAYS HOURS:MINUTES:SECONDS.MICROSECONDS'
         </code>
        </td>
       </tr>
       <tr>
        <td>
         <code class="literal">
          DAY_SECOND
         </code>
        </td>
        <td>
         <code class="literal">
          'DAYS HOURS:MINUTES:SECONDS'
         </code>
        </td>
       </tr>
       <tr>
        <td>
         <code class="literal">
          DAY_MINUTE
         </code>
        </td>
        <td>
         <code class="literal">
          'DAYS HOURS:MINUTES'
         </code>
        </td>
       </tr>
       <tr>
        <td>
         <code class="literal">
          DAY_HOUR
         </code>
        </td>
        <td>
         <code class="literal">
          'DAYS HOURS'
         </code>
        </td>
       </tr>
       <tr>
        <td>
         <code class="literal">
          YEAR_MONTH
         </code>
        </td>
        <td>
         <code class="literal">
          'YEARS-MONTHS'
         </code>
        </td>
       </tr>
      </tbody>
     </table>
    </div>
    <div class="table-contents">
     <table cellpadding="0" cellspacing="0" style="position: fixed; top: 0px; display: none; left: 401px; width: 681px;">
      <thead>
       <tr>
        <th style="width: 340.141px;">
         <em class="replaceable">
          <code>
           unit
          </code>
         </em>
         Value
        </th>
        <th style="width: 340.141px;">
         Expected
         <em class="replaceable">
          <code>
           expr
          </code>
         </em>
         Format
        </th>
       </tr>
      </thead>
     </table>
    </div>
   </div>
   <br class="table-break"/>
   <p>
    MySQL permits any punctuation delimiter in the
    <em class="replaceable">
     <code>
      expr
     </code>
    </em>
    format. Those shown in the table
        are the suggested delimiters.
   </p>
   <p>
    Temporal intervals are used for certain functions, such as
    <a class="link" href="date-and-time-functions.html#function_date-add">
     <code class="literal">
      DATE_ADD()
     </code>
    </a>
    and
    <a class="link" href="date-and-time-functions.html#function_date-sub">
     <code class="literal">
      DATE_SUB()
     </code>
    </a>
    :
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa98324662"><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> <span class="token function">DATE_ADD</span><span class="token punctuation">(</span><span class="token string">'2018-05-01'</span><span class="token punctuation">,</span><span class="token keyword">INTERVAL</span> <span class="token number">1</span> <span class="token keyword">DAY</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token prompt">        -&gt;</span> <span class="token string">'2018-05-02'</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token function">DATE_SUB</span><span class="token punctuation">(</span><span class="token string">'2018-05-01'</span><span class="token punctuation">,</span><span class="token keyword">INTERVAL</span> <span class="token number">1</span> <span class="token datatype">YEAR</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token prompt">        -&gt;</span> <span class="token string">'2017-05-01'</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token function">DATE_ADD</span><span class="token punctuation">(</span><span class="token string">'2020-12-31 23:59:59'</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>                 <span class="token keyword">INTERVAL</span> <span class="token number">1</span> <span class="token keyword">SECOND</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token prompt">        -&gt;</span> <span class="token string">'2021-01-01 00:00:00'</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token function">DATE_ADD</span><span class="token punctuation">(</span><span class="token string">'2018-12-31 23:59:59'</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>                 <span class="token keyword">INTERVAL</span> <span class="token number">1</span> <span class="token keyword">DAY</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token prompt">        -&gt;</span> <span class="token string">'2019-01-01 23:59:59'</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token function">DATE_ADD</span><span class="token punctuation">(</span><span class="token string">'2100-12-31 23:59:59'</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>                 <span class="token keyword">INTERVAL</span> <span class="token string">'1:1'</span> <span class="token keyword">MINUTE_SECOND</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token prompt">        -&gt;</span> <span class="token string">'2101-01-01 00:01:00'</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token function">DATE_SUB</span><span class="token punctuation">(</span><span class="token string">'2025-01-01 00:00:00'</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>                 <span class="token keyword">INTERVAL</span> <span class="token string">'1 1:1:1'</span> <span class="token keyword">DAY_SECOND</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token prompt">        -&gt;</span> <span class="token string">'2024-12-30 22:58:59'</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token function">DATE_ADD</span><span class="token punctuation">(</span><span class="token string">'1900-01-01 00:00:00'</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>                 <span class="token keyword">INTERVAL</span> <span class="token string">'-1 10'</span> <span class="token keyword">DAY_HOUR</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token prompt">        -&gt;</span> <span class="token string">'1899-12-30 14:00:00'</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token function">DATE_SUB</span><span class="token punctuation">(</span><span class="token string">'1998-01-02'</span><span class="token punctuation">,</span> <span class="token keyword">INTERVAL</span> <span class="token number">31</span> <span class="token keyword">DAY</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token prompt">        -&gt;</span> <span class="token string">'1997-12-02'</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token function">DATE_ADD</span><span class="token punctuation">(</span><span class="token string">'1992-12-31 23:59:59.000002'</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>            <span class="token keyword">INTERVAL</span> <span class="token string">'1.999999'</span> <span class="token keyword">SECOND_MICROSECOND</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token prompt">        -&gt;</span> <span class="token string">'1993-01-01 00:00:01.000001'</span></code></pre>
   </div>
   <p>
    Temporal arithmetic also can be performed in expressions using
    <code class="literal">
     INTERVAL
    </code>
    together with the
    <a class="link" href="arithmetic-functions.html#operator_plus">
     <code class="literal">
      +
     </code>
    </a>
    or
    <a class="link" href="arithmetic-functions.html#operator_minus">
     <code class="literal">
      -
     </code>
    </a>
    operator:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa53990518"><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 datatype">date</span> <span class="token operator">+</span> <span class="token keyword">INTERVAL</span> <em class="replaceable">expr</em> <em class="replaceable">unit</em>
<span class="token datatype">date</span> <span class="token operator">-</span> <span class="token keyword">INTERVAL</span> <em class="replaceable">expr</em> <em class="replaceable">unit</em></code></pre>
   </div>
   <p>
    <code class="literal">
     INTERVAL
     <em class="replaceable">
      <code>
       expr
      </code>
     </em>
     <em class="replaceable">
      <code>
       unit
      </code>
     </em>
    </code>
    is permitted on either
        side of the
    <a class="link" href="arithmetic-functions.html#operator_plus">
     <code class="literal">
      +
     </code>
    </a>
    operator if the expression on the other side is a date or
        datetime value. For the
    <a class="link" href="arithmetic-functions.html#operator_minus">
     <code class="literal">
      -
     </code>
    </a>
    operator,
    <code class="literal">
     INTERVAL
     <em class="replaceable">
      <code>
       expr
      </code>
     </em>
     <em class="replaceable">
      <code>
       unit
      </code>
     </em>
    </code>
    is permitted only on
        the right side, because it makes no sense to subtract a date or
        datetime value from an interval.
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa85998782"><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> <span class="token string">'2018-12-31 23:59:59'</span> <span class="token operator">+</span> <span class="token keyword">INTERVAL</span> <span class="token number">1</span> <span class="token keyword">SECOND</span><span class="token punctuation">;</span>
<span class="token prompt">        -&gt;</span> <span class="token string">'2019-01-01 00:00:00'</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token keyword">INTERVAL</span> <span class="token number">1</span> <span class="token keyword">DAY</span> <span class="token operator">+</span> <span class="token string">'2018-12-31'</span><span class="token punctuation">;</span>
<span class="token prompt">        -&gt;</span> <span class="token string">'2019-01-01'</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token string">'2025-01-01'</span> <span class="token operator">-</span> <span class="token keyword">INTERVAL</span> <span class="token number">1</span> <span class="token keyword">SECOND</span><span class="token punctuation">;</span>
<span class="token prompt">        -&gt;</span> <span class="token string">'2024-12-31 23:59:59'</span></code></pre>
   </div>
   <p>
    The
    <a class="link" href="date-and-time-functions.html#function_extract">
     <code class="literal">
      EXTRACT()
     </code>
    </a>
    function uses the
        same kinds of
    <em class="replaceable">
     <code>
      unit
     </code>
    </em>
    specifiers as
    <a class="link" href="date-and-time-functions.html#function_date-add">
     <code class="literal">
      DATE_ADD()
     </code>
    </a>
    or
    <a class="link" href="date-and-time-functions.html#function_date-sub">
     <code class="literal">
      DATE_SUB()
     </code>
    </a>
    , but extracts parts
        from the date rather than performing date arithmetic:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa86685915"><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> <span class="token function">EXTRACT</span><span class="token punctuation">(</span><span class="token datatype">YEAR</span> <span class="token keyword">FROM</span> <span class="token string">'2019-07-02'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token prompt">        -&gt;</span> <span class="token number">2019</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token function">EXTRACT</span><span class="token punctuation">(</span><span class="token keyword">YEAR_MONTH</span> <span class="token keyword">FROM</span> <span class="token string">'2019-07-02 01:02:03'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token prompt">        -&gt;</span> <span class="token number">201907</span></code></pre>
   </div>
   <p>
    Temporal intervals can be used in
    <a class="link" href="create-event.html" title="15.1.13 CREATE EVENT Statement">
     <code class="literal">
      CREATE
        EVENT
     </code>
    </a>
    statements:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa66032978"><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">EVENT</span> myevent
    <span class="token keyword">ON</span> <span class="token keyword">SCHEDULE</span> <span class="token keyword">AT</span> <span class="token keyword">CURRENT_TIMESTAMP</span> <span class="token operator">+</span> <span class="token keyword">INTERVAL</span> <span class="token number">1</span> <span class="token keyword">HOUR</span>
    <span class="token keyword">DO</span>
      <span class="token keyword">UPDATE</span> myschema<span class="token punctuation">.</span>mytable <span class="token keyword">SET</span> mycol <span class="token operator">=</span> mycol <span class="token operator">+</span> <span class="token number">1</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    If you specify an interval value that is too short (does not
        include all the interval parts that would be expected from the
    <em class="replaceable">
     <code>
      unit
     </code>
    </em>
    keyword), MySQL assumes that you
        have left out the leftmost parts of the interval value. For
        example, if you specify a
    <em class="replaceable">
     <code>
      unit
     </code>
    </em>
    of
    <code class="literal">
     DAY_SECOND
    </code>
    , the value of
    <em class="replaceable">
     <code>
      expr
     </code>
    </em>
    is expected to have days, hours,
        minutes, and seconds parts. If you specify a value like
    <code class="literal">
     '1:10'
    </code>
    , MySQL assumes that the days and hours
        parts are missing and the value represents minutes and seconds.
        In other words,
    <code class="literal">
     '1:10' DAY_SECOND
    </code>
    is
        interpreted in such a way that it is equivalent to
    <code class="literal">
     '1:10' MINUTE_SECOND
    </code>
    . This is analogous to
        the way that MySQL interprets
    <a class="link" href="time.html" title="13.2.3 The TIME Type">
     <code class="literal">
      TIME
     </code>
    </a>
    values as representing
        elapsed time rather than as a time of day.
   </p>
   <p>
    <em class="replaceable">
     <code>
      expr
     </code>
    </em>
    is treated as a string, so be
        careful if you specify a nonstring value with
    <code class="literal">
     INTERVAL
    </code>
    . For example, with an interval
        specifier of
    <code class="literal">
     HOUR_MINUTE
    </code>
    , '6/4' is treated as
        6 hours, four minutes, whereas
    <code class="literal">
     6/4
    </code>
    evaluates
        to
    <code class="literal">
     1.5000
    </code>
    and is treated as 1 hour, 5000
        minutes:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa60546875"><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> <span class="token string">'6/4'</span><span class="token punctuation">,</span> <span class="token number">6</span><span class="token operator">/</span><span class="token number">4</span><span class="token punctuation">;</span>
<span class="token prompt">        -&gt;</span> <span class="token number">1.5000</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token function">DATE_ADD</span><span class="token punctuation">(</span><span class="token string">'2019-01-01'</span><span class="token punctuation">,</span> <span class="token keyword">INTERVAL</span> <span class="token string">'6/4'</span> <span class="token keyword">HOUR_MINUTE</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token prompt">        -&gt;</span> <span class="token string">'2019-01-01 06:04:00'</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token function">DATE_ADD</span><span class="token punctuation">(</span><span class="token string">'2019-01-01'</span><span class="token punctuation">,</span> <span class="token keyword">INTERVAL</span> <span class="token number">6</span><span class="token operator">/</span><span class="token number">4</span> <span class="token keyword">HOUR_MINUTE</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token prompt">        -&gt;</span> <span class="token string">'2019-01-04 12:20:00'</span></code></pre>
   </div>
   <p>
    To ensure interpretation of the interval value as you expect, a
    <a class="link" href="cast-functions.html#function_cast">
     <code class="literal">
      CAST()
     </code>
    </a>
    operation may be used. To
        treat
    <code class="literal">
     6/4
    </code>
    as 1 hour, 5 minutes, cast it to a
    <a class="link" href="fixed-point-types.html" title="13.1.3 Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC">
     <code class="literal">
      DECIMAL
     </code>
    </a>
    value with a single
        fractional digit:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa29285044"><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> <span class="token function">CAST</span><span class="token punctuation">(</span><span class="token number">6</span><span class="token operator">/</span><span class="token number">4</span> <span class="token keyword">AS</span> <span class="token datatype">DECIMAL</span><span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">,</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token prompt">        -&gt;</span> <span class="token number">1.5</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token function">DATE_ADD</span><span class="token punctuation">(</span><span class="token string">'1970-01-01 12:00:00'</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>                 <span class="token keyword">INTERVAL</span> <span class="token function">CAST</span><span class="token punctuation">(</span><span class="token number">6</span><span class="token operator">/</span><span class="token number">4</span> <span class="token keyword">AS</span> <span class="token datatype">DECIMAL</span><span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">,</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token keyword">HOUR_MINUTE</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token prompt">        -&gt;</span> <span class="token string">'1970-01-01 13:05:00'</span></code></pre>
   </div>
   <p>
    If you add to or subtract from a date value something that
        contains a time part, the result is automatically converted to a
        datetime value:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa47125288"><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> <span class="token function">DATE_ADD</span><span class="token punctuation">(</span><span class="token string">'2023-01-01'</span><span class="token punctuation">,</span> <span class="token keyword">INTERVAL</span> <span class="token number">1</span> <span class="token keyword">DAY</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token prompt">        -&gt;</span> <span class="token string">'2023-01-02'</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token function">DATE_ADD</span><span class="token punctuation">(</span><span class="token string">'2023-01-01'</span><span class="token punctuation">,</span> <span class="token keyword">INTERVAL</span> <span class="token number">1</span> <span class="token keyword">HOUR</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token prompt">        -&gt;</span> <span class="token string">'2023-01-01 01:00:00'</span></code></pre>
   </div>
   <p>
    If you add
    <code class="literal">
     MONTH
    </code>
    ,
    <code class="literal">
     YEAR_MONTH
    </code>
    , or
    <code class="literal">
     YEAR
    </code>
    and
        the resulting date has a day that is larger than the maximum day
        for the new month, the day is adjusted to the maximum days in
        the new month:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa51841626"><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> <span class="token function">DATE_ADD</span><span class="token punctuation">(</span><span class="token string">'2019-01-30'</span><span class="token punctuation">,</span> <span class="token keyword">INTERVAL</span> <span class="token number">1</span> <span class="token keyword">MONTH</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token prompt">        -&gt;</span> <span class="token string">'2019-02-28'</span></code></pre>
   </div>
   <p>
    Date arithmetic operations require complete dates and do not
        work with incomplete dates such as
    <code class="literal">
     '2016-07-00'
    </code>
    or badly malformed dates:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa46201224"><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> <span class="token function">DATE_ADD</span><span class="token punctuation">(</span><span class="token string">'2016-07-00'</span><span class="token punctuation">,</span> <span class="token keyword">INTERVAL</span> <span class="token number">1</span> <span class="token keyword">DAY</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token prompt">        -&gt;</span> <span class="token boolean">NULL</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token string">'2005-03-32'</span> <span class="token operator">+</span> <span class="token keyword">INTERVAL</span> <span class="token number">1</span> <span class="token keyword">MONTH</span><span class="token punctuation">;</span>
<span class="token prompt">        -&gt;</span> <span class="token boolean">NULL</span></code></pre>
   </div>
  </div>
 </div>
 <br/>
</div>
