Invalid default value for 'Date and time' CURRENT_TIMESTAMP

Question

I am using:

'time'  type(datetime) defult value(CURRENT_TIMESTAMP)

but it doesn't work, it's showing the following error:

Invalid default value for 'Date'

Please can anybody help me?

Answer 1

If you are running MySQL version 5.6.5 or later

In your CREATE TABLE you can declare a column like this:

  `mydtcol` DATETIME DEFAULT CURRENT_TIMESTAMP 

Before 5.6, it's not possible to use CURRENT_TIMESTAMP for a DATETIME column. It is possible with the first TIMESTAMP column in a table.

  `mytscol` TIMESTAMP DEFAULT CURRENT_TIMESTAMP 

If you require the column to be DATETIME datatype, and you need the value of the column initialized when you insert a row, you can either provide a value for the column in the INSERT statement, e.g.

 INSERT INTO mytable (...,mycol,...) VALUES (...,'2016-04-21 23:55:55',...)

Or, you could use a BEFORE INSERT trigger to assign a value to the column.

 DELIMITER $$

 CREATE TRIGGER mytable_bi 
 BEFORE INSERT ON mytable
 FOR EACH ROW
 BEGIN
   IF new.mydtcol IS NULL THEN
      SET new.mydtcol = NOW();
   END IF;
 END$$

 DELIMITER ;