# 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.

```sql
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.

```sql
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.

```sql
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.

```sql
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`.

```sql
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.

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