Column name is not taking timestamp as default value

1
votes

I have two columns created_at and updated_at in same table. Both the columns have data type as datetime. Now when I am trying to modify the column data types as follows -

alter table nodestatics modify column updated_at datetime default current_timestamp; 

alter table nodestatics modify column created_at datetime default current_timestamp;

It is showing the following error

Error Code : 1067 Invalid default value for 'updated_at' (0 ms taken)

Error Code : 1067 Invalid default value for 'created_at' (0 ms taken)

My mysql version is 5.5.41-0ubuntu0.14.04.1 (Ubuntu)

3
mysqlsqlmysql-error-1067

3 Answers

2
votes

It is hard to reference documentation in a comment:

As of MySQL 5.6.5, TIMESTAMP and DATETIME columns can be automatically initializated and updated to the current date and time (that is, the current timestamp). Before 5.6.5, this is true only for TIMESTAMP, and for at most one TIMESTAMP column per table.

Either upgrade to 5.6.5. Or use TIMESTAMP instead of DATETIME.

1
votes

Use TIMESTAMP instead of DATETIME

Should be something like this:

ALTER TABLE nodestatics MODIFY COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

ALTER TABLE nodestatics MODIFY COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

Or you can use TRIGGERS.

CREATE TRIGGER myTable_OnInsert BEFORE INSERT ON `tblMyTable`
    FOR EACH ROW SET NEW.dateAdded = NOW();

You can look at similar topic. Happy coding!

0
votes

Try this 

alter table nodestatics modify column created_at timestamp default current_timestamp;