Automatic initialization and update of TIMESTAMP and DATETIME

The TIMESTAMP and DATETIME columns can be automatically initialized and updated to the current date and time (i.e., the current timestamp).

For any TIMESTAMP or DATETIME column in the table, you can specify the current timestamp as the default value, automatically update the value, or both:

For insert rows that do not specify a value for the column, set the automatically initialized column to the current timestamp.
When the value of any other column in the row changes from the current value, the automatic update column will be automatically updated to the current timestamp. If all other columns are set to the current value, the automatically updated column remains the same. To prevent automatically updated columns from being updated when other columns change, explicitly set them to the current value. To update an automatically updated column, explicitly set it to the value it should have even if the other columns have not changed (for example, set it to CURRENT_TIMESTAMP).

To specify automatic properties, use the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses in the column definition. If two clauses define a column at the same time, their order is interchangeable and does not affect logical calculations. In addition, CURRENT_TIMESTAMP is consistent with CURRENT_TIMESTAMP() or NOW().

The use of DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP is specific to TIMESTAMP and DATETIME. The DEFAULT clause can also be used to specify constant (non-automatic) default values (for example, DEFAULT 0 or DEFAULT '2000-01-01 00:00:00').

The TIMESTAMP or DATETIME column definition can specify the current timestamp for the default and auto-update values, specify only one of them, or neither. Different columns can have different automatic attribute combinations. The following rules describe these possibilities:

When using the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses simultaneously, the default value of the column is the current timestamp and is automatically updated to the current timestamp.

CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Use only the DEFAULT clause, not the ON UPDATE CURRENT_TIMESTAMP clause, the column has the given default value, but is not automatically updated to the current timestamp.

The default value depends on whether the DEFAULT clause specifies CURRENT_TIMESTAMP or a constant value. Use CURRENT_TIMESTAMP, default is the current timestamp.

CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  dt DATETIME DEFAULT CURRENT_TIMESTAMP
);

For constants, the default value is the given value. In this case, the column has no automatic properties.

CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT 0,
  dt DATETIME DEFAULT 0
);

Using the ON UPDATE CURRENT_TIMESTAMP clause and the constant DEFAULT clause, the column is automatically updated to the current timestamp and uses the given constant default value.

CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP,
  dt DATETIME DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP
);

When using only the ON UPDATE CURRENT_TIMESTAMP clause, but not the DEFAULT clause, the column is automatically updated to the current timestamp, but its default value does not have the current timestamp.

The default value of TIMESTAMP is 0; if defined using the NULL property, the default value is NULL.

CREATE TABLE t1 (
  ts1 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- default 0
  ts2 TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP -- default NULL
);

The default value of DATETIME is NULL; if defined using the NOT NULL property, the default value is 0.

CREATE TABLE t1 (
  dt1 DATETIME ON UPDATE CURRENT_TIMESTAMP, -- default NULL
  dt2 DATETIME NOT NULL ON UPDATE CURRENT_TIMESTAMP -- default 0
);