How do I alter a mysql table column defaults?

mysql database timestamp alter
Tihom picture Tihom · Nov 20, 2009 · Viewed 80.4k times · Source

I have a table with a column of type timestamp which defaults current_timestamp and updates to current_timestamp on every update.

I want to remove the "on update" feature on this column. How do I write the alter statement?

I tried the following: 

ALTER TABLE mytable alter column time  set DEFAULT now();

but this didn't work.

Answer

jonstjohn picture jonstjohn · Nov 20, 2009

Pete was almost correct but used the wrong syntax for 'change':

ALTER TABLE mytable CHANGE `time` `time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

Notice that you must repeat the column name. Also, make sure you are using backticks instead of single quotes to escape the column name time, which prevents it from being interpreted as the mysql column type of time.

By specifying the DEFAULT of CURRENT_TIMESTAMP, MySQL will no longer automatically update the column. From the MySQL Manual:

With a DEFAULT CURRENT_TIMESTAMP clause and no ON UPDATE clause, the column has the current timestamp for its default value but is not automatically updated.

Related questions

How to update MySql timestamp column to current timestamp on PHP?

I want to update the columns of data type timestamp manually through my PHP code. Can you please tell me how to do that?

Read More
MySQL: How can I get the timestamp of the last insertion to the database

How can I check when was the last time (timestamp) that I wrote in the database, irrespectively of the database table that I inserted into?

Read More
'IF' in 'SELECT' statement - choose output value based on column values

SELECT id, amount FROM report I need amount to be amount if report.type='P' and -amount if report.type='N'. How do I add this to the above query?

Read More