MySQL | Automatic Initialization and updating for TIMESTAMP and DATETIME
Start your free 7-days trial now!
For TIMESTAMP and DATETIME columns in a table in MySQL you can:
Assign the current timestamp as the default value
Assign the auto-update value
Assign both of the above
Auto-update means when the value of any other column in the row is changed, the value of the auto-update column is automatically updated to the current timestamp.
Default value
To set the default value of TIMESTAMP and DATETIME columns to be the current timestamp:
CREATE TABLE table_name ( column1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP, column2 DATETIME DEFAULT CURRENT_TIMESTAMP);
Note that the default value does not have to be CURRENT_TIMESTAMP, you can use a fixed value also:
CREATE TABLE table_name ( column1 TIMESTAMP DEFAULT '2020-01-01 00:00:00', column2 DATETIME DEFAULT '2020-01-01 00:00:00');
Auto-update value
To set the TIMESTAMP and DATETIME columns to auto-update:
CREATE TABLE table_name ( column1 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- default 0 column2 DATETIME ON UPDATE CURRENT_TIMESTAMP -- default NULL);
If we do not provide a DEFAULT clause, TIMESTAMP defaults to 0, while DATETIME defaults to NULL
You can modify this behavior by specifying NULL or NOT NULL attributes:
CREATE TABLE table_name ( column1 TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP, -- default NULL column2 DATETIME NOT NULL ON UPDATE CURRENT_TIMESTAMP -- default 0);
Now column1 will have a default value of NULL while column2 will have a default value of 0.
Default value and Auto-update
To set the TIMESTAMP and DATETIME datatype columns to have a default value and auto-update:
CREATE TABLE table_name ( column1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, column2 DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
Now column1 and column2 both have a default value (current timestamp), while they will also be auto-updated when changes are made to any other column in the table (with the timestamp of when the change was made).
