I cannot create a auto generated date time in mysql workbench

Question

I'm trying to create an auto-generated date-time using MySQL workbench, timestamp seems not working

This is the error

Operation failed: There was an error while applying the SQL script to the database.

ERROR 1067: Invalid default value for 'date_created'

SQL Statement:

ALTER TABLE `b2b_appointment`.`invoice` 
CHANGE COLUMN `` `date_created` TIMESTAMP(100) NOT NULL DEFAULT CURRENT_TIMESTAMP

Answer 1

The fsp value you set in the TIMESTAMP[(fsp)] is to blame. The following is taken from MySQL reference manual, An optional fsp value in the range from 0 to 6 may be given to specify fractional seconds precision. A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0. To make the fsp compatible with the column's default value using current_timestamp , set the value to 0 to ignore fractional part.

alter table b2b_appointment.invoice change column 
date_created date_created timestamp(0) not null default current_timestamp;