1

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 VERSIONcolumn 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!

NDeveloper
  • 3,115
  • 7
  • 23
  • 34
  • 1
    change the data type to TIMESTAMP , and it should work – Satya Mar 19 '14 at 07:05
  • 1
    possible duplicate of [Set NOW() as Default Value for datetime datatype?](http://stackoverflow.com/questions/5818423/set-now-as-default-value-for-datetime-datatype) – Peter Lang Mar 19 '14 at 07:08

4 Answers4

3

Use the CURRENT_TIMESTAMP instead of NOW():

ALTER TABLE masterdatabase.test add column CREATE_TIMESTAMP TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
Code Lღver
  • 15,573
  • 16
  • 56
  • 75
  • Hi..I tried this. it worked: `ALTER TABLE masterdatabase.test add column CREATE_TIMESTAMP TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;` but when I tried to add another column for `LAST_UPDATE_TIMESTAMP` it said there should be only one column type TIMESTAMP..:( – NDeveloper Mar 19 '14 at 07:10
  • @NDeveloper My pleasure in help you. Thanks for comment. – Code Lღver Mar 19 '14 at 07:12
  • @NDeveloper change the TIMESTAMP with datetime as you can have only one column with TIMESTAMP. – Code Lღver Mar 19 '14 at 07:13
  • Heart: It shows: `12:42:34 ALTER TABLE masterdatabase.test add column LAST_UPDATE_TIMESTAMP TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP Error Code: 1293. Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause 0.000 sec ` – NDeveloper Mar 19 '14 at 07:13
1

try this

alter table masterdatabase.test add column CREATE_TIMESTAMP datetime default NOW() ;

You need to store Date value in DATETIME as NOW() will retur you the date and time in format of 2008-11-11 12:45:34 and DATETIME can store this value for more detail see this

Vikas Hardia
  • 2,635
  • 5
  • 34
  • 53
1

You just change your data type DATE to DATETIME,so that it will work fine otherwise if you want to put the data type date then just change the alter query for last two column .

alter table masterdatabase.test add column CREATE_TIMESTAMP date default curdate(); 
alter table masterdatabase.test add column LAST_UPDATE_TIMESTAMP date default curdate();
Andolasoft Inc
  • 1,296
  • 1
  • 7
  • 16
0

The DEFAULT value clause in a data type specification indicates a default value for a column. With one exception, the default value must be a constant; it cannot be a function or an expression. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as the default for TIMESTAMP and DATETIME columns.

Try This One :

alter table test add column CREATE_TIMESTAMP TIMESTAMP DEFAULT CURRENT_TIMESTAMP

Hope this will help.

Mac
  • 191
  • 1
  • 12
  • It made sense..but Cant have two columns with TIMESTAMP. How can that be achive? I actually want to alter the table to add two columns with datatype date and default value date. :( – NDeveloper Mar 19 '14 at 07:25