NDeveloper NDeveloper - 1 month ago 9
MySQL Question

ALTER TABLE command to add columns in an existing Table with current date and time: Not Working

I have a MySQL table named test. and I need to add three more columns

VERSION
datatype bigint,
CREATE_TIMESTAMP
datatype
Date
and
LAST_UPDATE_TIMESTAMP
datatype
Date
.

The default value for
VERSION
should be
0
and for
CREATE_TIMESTAMP
and
LAST_UPDATE_TIMESTAMP
it should be the current datetime.

Now I am doing something like:

alter table masterdatabase.test add column VERSION bigint(50) default 0;
alter table masterdatabase.test add column CREATE_TIMESTAMP date default NOW();
alter table masterdatabase.test add column LAST_UPDATE_TIMESTAMP date default NOW();


it adds the
VERSION
column to the table with Default value
0
in each rows of the table. but could not create the other two columns of datatype
Date
.
It shows a Error in MySQL log:

alter table masterdatabase.test add column CREATE_TIMESTAMP date default NOW() | Error Code: 1067. Invalid default value for 'CREATE_TIMESTAMP' 0.000 sec


What exactly prevents me to create those columns I don't understand.

Please suggest is there any other way to do this.

Thanks in Advance!

Answer

Use the CURRENT_TIMESTAMP instead of NOW():

ALTER TABLE masterdatabase.test add column CREATE_TIMESTAMP TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP