I thought curtime(), now() and current_timestamp are valid default datetime values in MySql?

3
votes

I'm using the latest version of MySql and I tried altering a table to use curtime() or now() or current_timestamp as the default value for a datetime column (created_at, updated_at). For all three, the alter failed, saying these are invalid default values. Any ideas what's an appropriate default value to generate the current time in a datetime field?

ALTER TABLE `music_library_development`.`albums` 
MODIFY COLUMN `created_at` DATETIME NOT NULL DEFAULT current_timestamp,
MODIFY COLUMN `updated_at` DATETIME NOT NULL DEFAULT current_timestamp;

ALTER TABLE `music_library_development`.`albums` 
MODIFY COLUMN `created_at` DATETIME NOT NULL DEFAULT now(),
MODIFY COLUMN `updated_at` DATETIME NOT NULL DEFAULT now();

ALTER TABLE `music_library_development`.`albums` 
MODIFY COLUMN `created_at` DATETIME NOT NULL DEFAULT curtime(),
MODIFY COLUMN `updated_at` DATETIME NOT NULL DEFAULT curtime();
2
mysqldatetimedefault-value

2 Answers

1
votes

Try using the TIMESTAMP column type:

MODIFY COLUMN `updated_at` TIMESTAMP NOT NULL DEFAULT current_timestamp;
0
votes

alter table music_library_development change created_at created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

alter table music_library_development change updated_at updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP