How do I remove ON UPDATE CURRENT_TIMESTAMP from an existing column in MySQL?

The ON UPDATE CURRENT_TIMESTAMP defines that an update without an explicit timestamp would result in an update to the current timestamp value.

You can remove ON UPDATE CURRENT_TIMESTAMP from a column using ALTER command.

The syntax is as follows

ALTER TABLE yourTableName CHANGE yourTimeStampColumnName yourTimeStampColumnName timestamp NOT NULL default CURRENT_TIMESTAMP;

To understand the above syntax, let us create a table. The query to create a table is as follows

mysql> create table removeOnUpdateCurrentTimeStampDemo    - > (    - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    - > Name varchar(20),    - > UserUpdateTimestamp timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP    - > ); Query OK, 0 rows affected (0.54 sec)

Check the description of the table using DESC command.

The query is as follows

mysql> desc removeOnUpdateCurrentTimeStampDemo;

The following is the output

+---------------------+-------------+------+-----+-------------------+-----------------------------+ | Field               | Type        | Null | Key | Default           | Extra                       | +---------------------+-------------+------+-----+-------------------+-----------------------------+ | Id                  | int(11)     | NO   | PRI | NULL              | auto_increment              | | Name                | varchar(20) | YES  |     | NULL              |                             | | UserUpdateTimestamp | timestamp   | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | +---------------------+-------------+------+-----+-------------------+-----------------------------+ 3 rows in set (0.04 sec)

Now look at the Extra field there is on update CURRENT_TIMESTAMP. The query to remove ON UPDATE CURRENT_TIMESTAMP is as follows:

mysql> alter table removeOnUpdateCurrentTimeStampDemo    - > change UserUpdateTimestamp UserUpdateTimestamp timestamp NOT NULL default CURRENT_TIMESTAMP; Query OK, 0 rows affected (0.16 sec) Records: 0 Duplicates: 0 Warnings: 0

Check the description of table once again.

The query is as follows

mysql> desc removeOnUpdateCurrentTimeStampDemo;

The following is the output

+---------------------+-------------+------+-----+-------------------+----------------+ | Field               | Type        | Null | Key | Default           | Extra          | +---------------------+-------------+------+-----+-------------------+----------------+ | Id                  | int(11)     | NO   | PRI | NULL              | auto_increment | | Name                | varchar(20) | YES  |     | NULL              |                | | UserUpdateTimestamp | timestamp   | NO   |     | CURRENT_TIMESTAMP |                | +---------------------+-------------+------+-----+-------------------+----------------+ 3 rows in set (0.00 sec)

If you want to remove the default CURRENT_TIMESTAMP, then the query is as follows

mysql> alter table removeOnUpdateCurrentTimeStampDemo    - > change UserUpdateTimestamp UserUpdateTimestamp timestamp NOT NULL; Query OK, 0 rows affected (0.12 sec) Records: 0 Duplicates: 0 Warnings: 0

Check the description of table once again.

The query is as follows

mysql> desc removeOnUpdateCurrentTimeStampDemo;

The following is the output

+---------------------+-------------+------+-----+---------+----------------+ | Field               | Type        | Null | Key | Default | Extra          | +---------------------+-------------+------+-----+---------+----------------+ | Id                  | int(11)     | NO   | PRI | NULL    | auto_increment | | Name                | varchar(20) | YES  |     | NULL    |                | | UserUpdateTimestamp | timestamp   | NO   |   | NULL | | +---------------------+-------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec)

Now look at the above sample output, we have removed the ON UPDATE CURRENT TIMESTAMP.