<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="timestamp-initialization">
      </a>
      13.2.5 Automatic Initialization and Updating for TIMESTAMP and DATETIME
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045213469520">
  </a>
  <p>
   <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="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
    <code class="literal">
     DATETIME
    </code>
   </a>
   columns can be
        automatically initialized and updated to the current date and
        time (that is, the current timestamp).
  </p>
  <p>
   For 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, you
        can assign the current timestamp as the default value, the
        auto-update value, or both:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      An auto-initialized column is set to the current timestamp
            for inserted rows that specify no value for the column.
     </p>
    </li>
    <li class="listitem">
     <p>
      An auto-updated column is automatically updated to the
            current timestamp when the value of any other column in the
            row is changed from its current value. An auto-updated
            column remains unchanged if all other columns are set to
            their current values. To prevent an auto-updated column from
            updating when other columns change, explicitly set it to its
            current value. To update an auto-updated column even when
            other columns do not change, explicitly set it to the value
            it should have (for example, set it to
      <a class="link" href="date-and-time-functions.html#function_current-timestamp">
       <code class="literal">
        CURRENT_TIMESTAMP
       </code>
      </a>
      ).
     </p>
    </li>
   </ul>
  </div>
  <p>
   In addition, if 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 is disabled, you can initialize or update any
   <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
    <code class="literal">
     TIMESTAMP
    </code>
   </a>
   (but not
   <code class="literal">
    DATETIME
   </code>
   ) 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>
   To specify automatic properties, use the
   <code class="literal">
    DEFAULT
        CURRENT_TIMESTAMP
   </code>
   and
   <code class="literal">
    ON UPDATE
        CURRENT_TIMESTAMP
   </code>
   clauses in column definitions. The
        order of the clauses does not matter. If both are present in a
        column definition, either can occur first. Any of the synonyms
        for
   <a class="link" href="date-and-time-functions.html#function_current-timestamp">
    <code class="literal">
     CURRENT_TIMESTAMP
    </code>
   </a>
   have the
        same meaning as
   <a class="link" href="date-and-time-functions.html#function_current-timestamp">
    <code class="literal">
     CURRENT_TIMESTAMP
    </code>
   </a>
   . These are
   <a class="link" href="date-and-time-functions.html#function_current-timestamp">
    <code class="literal">
     CURRENT_TIMESTAMP()
    </code>
   </a>
   ,
   <a class="link" href="date-and-time-functions.html#function_now">
    <code class="literal">
     NOW()
    </code>
   </a>
   ,
   <a class="link" href="date-and-time-functions.html#function_localtime">
    <code class="literal">
     LOCALTIME
    </code>
   </a>
   ,
   <a class="link" href="date-and-time-functions.html#function_localtime">
    <code class="literal">
     LOCALTIME()
    </code>
   </a>
   ,
   <a class="link" href="date-and-time-functions.html#function_localtimestamp">
    <code class="literal">
     LOCALTIMESTAMP
    </code>
   </a>
   , and
   <a class="link" href="date-and-time-functions.html#function_localtimestamp">
    <code class="literal">
     LOCALTIMESTAMP()
    </code>
   </a>
   .
  </p>
  <p>
   Use of
   <code class="literal">
    DEFAULT CURRENT_TIMESTAMP
   </code>
   and
   <code class="literal">
    ON UPDATE CURRENT_TIMESTAMP
   </code>
   is specific to
   <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="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
    <code class="literal">
     DATETIME
    </code>
   </a>
   . The
   <code class="literal">
    DEFAULT
   </code>
   clause also can be used to specify a
        constant (nonautomatic) default value (for example,
   <code class="literal">
    DEFAULT 0
   </code>
   or
   <code class="literal">
    DEFAULT '2000-01-01
        00:00:00'
   </code>
   ).
  </p>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    The following examples use
    <code class="literal">
     DEFAULT 0
    </code>
    , a
          default that can produce warnings or errors depending on
          whether strict SQL mode or the
    <a class="link" href="sql-mode.html#sqlmode_no_zero_date">
     <code class="literal">
      NO_ZERO_DATE
     </code>
    </a>
    SQL mode is
          enabled. Be aware that the
    <a class="link" href="sql-mode.html#sqlmode_traditional">
     <code class="literal">
      TRADITIONAL
     </code>
    </a>
    SQL mode
          includes strict mode and
    <a class="link" href="sql-mode.html#sqlmode_no_zero_date">
     <code class="literal">
      NO_ZERO_DATE
     </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>
    .
   </p>
  </div>
  <p>
   <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 definitions can
        specify the current timestamp for both the default and
        auto-update values, for one but not the other, or for neither.
        Different columns can have different combinations of automatic
        properties. The following rules describe the possibilities:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      With both
      <code class="literal">
       DEFAULT CURRENT_TIMESTAMP
      </code>
      and
      <code class="literal">
       ON UPDATE CURRENT_TIMESTAMP
      </code>
      , the column
            has the current timestamp for its default value and is
            automatically updated to the current timestamp.
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa14260386"><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>
  ts <span class="token datatype">TIMESTAMP</span> <span class="token keyword">DEFAULT</span> <span class="token keyword">CURRENT_TIMESTAMP</span> <span class="token keyword">ON</span> <span class="token keyword">UPDATE</span> <span class="token keyword">CURRENT_TIMESTAMP</span><span class="token punctuation">,</span>
  dt <span class="token datatype">DATETIME</span> <span class="token keyword">DEFAULT</span> <span class="token keyword">CURRENT_TIMESTAMP</span> <span class="token keyword">ON</span> <span class="token keyword">UPDATE</span> <span class="token keyword">CURRENT_TIMESTAMP</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      With a
      <code class="literal">
       DEFAULT
      </code>
      clause but no
      <code class="literal">
       ON
            UPDATE CURRENT_TIMESTAMP
      </code>
      clause, the column has
            the given default value and is not automatically updated to
            the current timestamp.
     </p>
     <p>
      The default depends on whether the
      <code class="literal">
       DEFAULT
      </code>
      clause specifies
      <code class="literal">
       CURRENT_TIMESTAMP
      </code>
      or a constant value.
            With
      <code class="literal">
       CURRENT_TIMESTAMP
      </code>
      , the default is
            the current timestamp.
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa68622769"><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>
  ts <span class="token datatype">TIMESTAMP</span> <span class="token keyword">DEFAULT</span> <span class="token keyword">CURRENT_TIMESTAMP</span><span class="token punctuation">,</span>
  dt <span class="token datatype">DATETIME</span> <span class="token keyword">DEFAULT</span> <span class="token keyword">CURRENT_TIMESTAMP</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      With a constant, the default is the given value. In this
            case, the column has no automatic properties at all.
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa17415115"><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>
  ts <span class="token datatype">TIMESTAMP</span> <span class="token keyword">DEFAULT</span> <span class="token number">0</span><span class="token punctuation">,</span>
  dt <span class="token datatype">DATETIME</span> <span class="token keyword">DEFAULT</span> <span class="token number">0</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      With an
      <code class="literal">
       ON UPDATE CURRENT_TIMESTAMP
      </code>
      clause and a constant
      <code class="literal">
       DEFAULT
      </code>
      clause, the
            column is automatically updated to the current timestamp and
            has the given constant default value.
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa46358967"><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>
  ts <span class="token datatype">TIMESTAMP</span> <span class="token keyword">DEFAULT</span> <span class="token number">0</span> <span class="token keyword">ON</span> <span class="token keyword">UPDATE</span> <span class="token keyword">CURRENT_TIMESTAMP</span><span class="token punctuation">,</span>
  dt <span class="token datatype">DATETIME</span> <span class="token keyword">DEFAULT</span> <span class="token number">0</span> <span class="token keyword">ON</span> <span class="token keyword">UPDATE</span> <span class="token keyword">CURRENT_TIMESTAMP</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      With an
      <code class="literal">
       ON UPDATE CURRENT_TIMESTAMP
      </code>
      clause but no
      <code class="literal">
       DEFAULT
      </code>
      clause, the column
            is automatically updated to the current timestamp but does
            not have the current timestamp for its default value.
     </p>
     <p>
      The default in this case is type dependent.
      <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
       <code class="literal">
        TIMESTAMP
       </code>
      </a>
      has a default of 0
            unless defined with the
      <code class="literal">
       NULL
      </code>
      attribute,
            in which case the default is
      <code class="literal">
       NULL
      </code>
      .
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa97584137"><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>
  ts1 <span class="token datatype">TIMESTAMP</span> <span class="token keyword">ON</span> <span class="token keyword">UPDATE</span> <span class="token keyword">CURRENT_TIMESTAMP</span><span class="token punctuation">,</span>     <span class="token comment" spellcheck="true">-- default 0</span>
  ts2 <span class="token datatype">TIMESTAMP</span> <span class="token boolean">NULL</span> <span class="token keyword">ON</span> <span class="token keyword">UPDATE</span> <span class="token keyword">CURRENT_TIMESTAMP</span> <span class="token comment" spellcheck="true">-- default NULL</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
       <code class="literal">
        DATETIME
       </code>
      </a>
      has a default of
      <code class="literal">
       NULL
      </code>
      unless defined with the
      <code class="literal">
       NOT
            NULL
      </code>
      attribute, in which case the default is 0.
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa70115128"><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>
  dt1 <span class="token datatype">DATETIME</span> <span class="token keyword">ON</span> <span class="token keyword">UPDATE</span> <span class="token keyword">CURRENT_TIMESTAMP</span><span class="token punctuation">,</span>         <span class="token comment" spellcheck="true">-- default NULL</span>
  dt2 <span class="token datatype">DATETIME</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">ON</span> <span class="token keyword">UPDATE</span> <span class="token keyword">CURRENT_TIMESTAMP</span> <span class="token comment" spellcheck="true">-- default 0</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
     </div>
    </li>
   </ul>
  </div>
  <p>
   <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="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
    <code class="literal">
     DATETIME
    </code>
   </a>
   columns have no
        automatic properties unless they are specified explicitly, with
        this exception: If 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 is disabled, the
   <span class="emphasis">
    <em>
     first
    </em>
   </span>
   <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
    <code class="literal">
     TIMESTAMP
    </code>
   </a>
   column has both
   <code class="literal">
    DEFAULT CURRENT_TIMESTAMP
   </code>
   and
   <code class="literal">
    ON
        UPDATE CURRENT_TIMESTAMP
   </code>
   if neither is specified
        explicitly. To suppress automatic properties for 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, use one of
        these strategies:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Enable 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. In this case, the
      <code class="literal">
       DEFAULT
            CURRENT_TIMESTAMP
      </code>
      and
      <code class="literal">
       ON UPDATE
            CURRENT_TIMESTAMP
      </code>
      clauses that specify automatic
            initialization and updating are available, but are not
            assigned 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 unless explicitly included in the column definition.
     </p>
    </li>
    <li class="listitem">
     <p>
      Alternatively, 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, do either of the following:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         Define the column with a
         <code class="literal">
          DEFAULT
         </code>
         clause that specifies a constant default value.
        </p>
       </li>
       <li class="listitem">
        <p>
         Specify the
         <code class="literal">
          NULL
         </code>
         attribute. This also
                causes the column to permit
         <code class="literal">
          NULL
         </code>
         values, which means that you cannot assign the current
                timestamp by setting the column to
         <code class="literal">
          NULL
         </code>
         . Assigning
         <code class="literal">
          NULL
         </code>
         sets the column to
         <code class="literal">
          NULL
         </code>
         , not the current timestamp. To
                assign the current timestamp, set the column to
         <a class="link" href="date-and-time-functions.html#function_current-timestamp">
          <code class="literal">
           CURRENT_TIMESTAMP
          </code>
         </a>
         or a
                synonym such as
         <a class="link" href="date-and-time-functions.html#function_now">
          <code class="literal">
           NOW()
          </code>
         </a>
         .
        </p>
       </li>
      </ul>
     </div>
    </li>
   </ul>
  </div>
  <p>
   Consider these table definitions:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa33944871"><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>
  ts1 <span class="token datatype">TIMESTAMP</span> <span class="token keyword">DEFAULT</span> <span class="token number">0</span><span class="token punctuation">,</span>
  ts2 <span class="token datatype">TIMESTAMP</span> <span class="token keyword">DEFAULT</span> <span class="token keyword">CURRENT_TIMESTAMP</span>
                <span class="token keyword">ON</span> <span class="token keyword">UPDATE</span> <span class="token keyword">CURRENT_TIMESTAMP</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t2 <span class="token punctuation">(</span>
  ts1 <span class="token datatype">TIMESTAMP</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
  ts2 <span class="token datatype">TIMESTAMP</span> <span class="token keyword">DEFAULT</span> <span class="token keyword">CURRENT_TIMESTAMP</span>
                <span class="token keyword">ON</span> <span class="token keyword">UPDATE</span> <span class="token keyword">CURRENT_TIMESTAMP</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t3 <span class="token punctuation">(</span>
  ts1 <span class="token datatype">TIMESTAMP</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token number">0</span><span class="token punctuation">,</span>
  ts2 <span class="token datatype">TIMESTAMP</span> <span class="token keyword">DEFAULT</span> <span class="token keyword">CURRENT_TIMESTAMP</span>
                <span class="token keyword">ON</span> <span class="token keyword">UPDATE</span> <span class="token keyword">CURRENT_TIMESTAMP</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   The tables have these properties:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      In each table definition, 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 no
            automatic initialization or updating.
     </p>
    </li>
    <li class="listitem">
     <p>
      The tables differ in how the
      <code class="literal">
       ts1
      </code>
      column
            handles
      <code class="literal">
       NULL
      </code>
      values. For
      <code class="literal">
       t1
      </code>
      ,
      <code class="literal">
       ts1
      </code>
      is
      <code class="literal">
       NOT NULL
      </code>
      and assigning it a value of
      <code class="literal">
       NULL
      </code>
      sets it to the current timestamp.
            For
      <code class="literal">
       t2
      </code>
      and
      <code class="literal">
       t3
      </code>
      ,
      <code class="literal">
       ts1
      </code>
      permits
      <code class="literal">
       NULL
      </code>
      and
            assigning it a value of
      <code class="literal">
       NULL
      </code>
      sets it to
      <code class="literal">
       NULL
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       t2
      </code>
      and
      <code class="literal">
       t3
      </code>
      differ in
            the default value for
      <code class="literal">
       ts1
      </code>
      . For
      <code class="literal">
       t2
      </code>
      ,
      <code class="literal">
       ts1
      </code>
      is defined to
            permit
      <code class="literal">
       NULL
      </code>
      , so the default is also
      <code class="literal">
       NULL
      </code>
      in the absence of an explicit
      <code class="literal">
       DEFAULT
      </code>
      clause. For
      <code class="literal">
       t3
      </code>
      ,
      <code class="literal">
       ts1
      </code>
      permits
      <code class="literal">
       NULL
      </code>
      but has an explicit default of 0.
     </p>
    </li>
   </ul>
  </div>
  <p>
   If a
   <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 definition
        includes an explicit fractional seconds precision value
        anywhere, the same value must be used throughout the column
        definition. This is permitted:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa84244707"><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>
  ts <span class="token function">TIMESTAMP</span><span class="token punctuation">(</span><span class="token number">6</span><span class="token punctuation">)</span> <span class="token keyword">DEFAULT</span> <span class="token function">CURRENT_TIMESTAMP</span><span class="token punctuation">(</span><span class="token number">6</span><span class="token punctuation">)</span> <span class="token keyword">ON</span> <span class="token keyword">UPDATE</span> <span class="token function">CURRENT_TIMESTAMP</span><span class="token punctuation">(</span><span class="token number">6</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   This is not permitted:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa56110514"><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>
  ts <span class="token function">TIMESTAMP</span><span class="token punctuation">(</span><span class="token number">6</span><span class="token punctuation">)</span> <span class="token keyword">DEFAULT</span> <span class="token keyword">CURRENT_TIMESTAMP</span> <span class="token keyword">ON</span> <span class="token keyword">UPDATE</span> <span class="token function">CURRENT_TIMESTAMP</span><span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <h4>
   <a name="idm46045213344320">
   </a>
   TIMESTAMP Initialization and the NULL Attribute
  </h4>
  <p>
   If 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 is disabled,
   <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
    <code class="literal">
     TIMESTAMP
    </code>
   </a>
   columns by default are
   <code class="literal">
    NOT NULL
   </code>
   , cannot contain
   <code class="literal">
    NULL
   </code>
   values, and assigning
   <code class="literal">
    NULL
   </code>
   assigns the current timestamp. To permit
        a
   <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
    <code class="literal">
     TIMESTAMP
    </code>
   </a>
   column to contain
   <code class="literal">
    NULL
   </code>
   , explicitly declare it with the
   <code class="literal">
    NULL
   </code>
   attribute. In this case, the default
        value also becomes
   <code class="literal">
    NULL
   </code>
   unless overridden
        with a
   <code class="literal">
    DEFAULT
   </code>
   clause that specifies a
        different default value.
   <code class="literal">
    DEFAULT NULL
   </code>
   can be
        used to explicitly specify
   <code class="literal">
    NULL
   </code>
   as the
        default value. (For a
   <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
    <code class="literal">
     TIMESTAMP
    </code>
   </a>
   column not declared with the
   <code class="literal">
    NULL
   </code>
   attribute,
   <code class="literal">
    DEFAULT NULL
   </code>
   is invalid.) If a
   <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
    <code class="literal">
     TIMESTAMP
    </code>
   </a>
   column permits
   <code class="literal">
    NULL
   </code>
   values, assigning
   <code class="literal">
    NULL
   </code>
   sets it to
   <code class="literal">
    NULL
   </code>
   , not
        to the current timestamp.
  </p>
  <p>
   The following table contains several
   <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
    <code class="literal">
     TIMESTAMP
    </code>
   </a>
   columns that permit
   <code class="literal">
    NULL
   </code>
   values:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa66195118"><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> t
<span class="token punctuation">(</span>
  ts1 <span class="token datatype">TIMESTAMP</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
  ts2 <span class="token datatype">TIMESTAMP</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token number">0</span><span class="token punctuation">,</span>
  ts3 <span class="token datatype">TIMESTAMP</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token keyword">CURRENT_TIMESTAMP</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   A
   <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
    <code class="literal">
     TIMESTAMP
    </code>
   </a>
   column that permits
   <code class="literal">
    NULL
   </code>
   values does
   <span class="emphasis">
    <em>
     not
    </em>
   </span>
   take on the current timestamp at insert time except under one of
        the following conditions:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Its default value is defined as
      <a class="link" href="date-and-time-functions.html#function_current-timestamp">
       <code class="literal">
        CURRENT_TIMESTAMP
       </code>
      </a>
      and no
            value is specified for the column
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="date-and-time-functions.html#function_current-timestamp">
       <code class="literal">
        CURRENT_TIMESTAMP
       </code>
      </a>
      or any of
            its synonyms such as
      <a class="link" href="date-and-time-functions.html#function_now">
       <code class="literal">
        NOW()
       </code>
      </a>
      is
            explicitly inserted into the column
     </p>
    </li>
   </ul>
  </div>
  <p>
   In other words, a
   <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
    <code class="literal">
     TIMESTAMP
    </code>
   </a>
   column defined to permit
   <code class="literal">
    NULL
   </code>
   values
        auto-initializes only if its definition includes
   <code class="literal">
    DEFAULT CURRENT_TIMESTAMP
   </code>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa42803089"><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> t <span class="token punctuation">(</span>ts <span class="token datatype">TIMESTAMP</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token keyword">CURRENT_TIMESTAMP</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   If the
   <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
    <code class="literal">
     TIMESTAMP
    </code>
   </a>
   column permits
   <code class="literal">
    NULL
   </code>
   values but its definition does not
        include
   <code class="literal">
    DEFAULT CURRENT_TIMESTAMP
   </code>
   , you must
        explicitly insert a value corresponding to the current date and
        time. Suppose that tables
   <code class="literal">
    t1
   </code>
   and
   <code class="literal">
    t2
   </code>
   have these definitions:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa88277116"><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>ts <span class="token datatype">TIMESTAMP</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token string">'0000-00-00 00:00:00'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t2 <span class="token punctuation">(</span>ts <span class="token datatype">TIMESTAMP</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token boolean">NULL</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   To set the
   <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
    <code class="literal">
     TIMESTAMP
    </code>
   </a>
   column in
        either table to the current timestamp at insert time, explicitly
        assign it that value. For example:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa50028337"><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">INSERT</span> <span class="token keyword">INTO</span> t2 <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token keyword">CURRENT_TIMESTAMP</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t1 <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token function">NOW</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   If 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 is enabled,
   <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
    <code class="literal">
     TIMESTAMP
    </code>
   </a>
   columns permit
   <code class="literal">
    NULL
   </code>
   values only if declared with the
   <code class="literal">
    NULL
   </code>
   attribute. Also,
   <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
    <code class="literal">
     TIMESTAMP
    </code>
   </a>
   columns do not permit
        assigning
   <code class="literal">
    NULL
   </code>
   to assign the current
        timestamp, whether declared with the
   <code class="literal">
    NULL
   </code>
   or
   <code class="literal">
    NOT NULL
   </code>
   attribute. To assign the current
        timestamp, set the column to
   <a class="link" href="date-and-time-functions.html#function_current-timestamp">
    <code class="literal">
     CURRENT_TIMESTAMP
    </code>
   </a>
   or a synonym
        such as
   <a class="link" href="date-and-time-functions.html#function_now">
    <code class="literal">
     NOW()
    </code>
   </a>
   .
  </p>
 </div>
 <br/>
</div>
