MySQL Invalid default value for timestamp when no default value is given. -

look @ following sql.

create schema if not exists `scheduler`; use `scheduler` ; create table if not exists `scheduler`.`jobhistory` (   `id` int not null auto_increment,   `job` int not null,   `starttime` timestamp not null,   `finishtime` timestamp not null,   primary key (`id`),   index `fk_jobhistory_job_idx` (`job` asc)); 

it throwing errorcode: 1067. invalid default value 'finish time' i'm not giving default value finish time, there time stamp starttime same , i'm not getting exception one.

although @jsnplank right timestamps treated differently , should consider using datetime datatype these 2 particular columns, however, fails explain error message.

the error message result of combination of how mysql treats timestamp fields when no default value provided , sql mode settings.

1. you define both timestamp columns not null, without specific default value set. means 1st timestamp column's default value current_timestamp() , updated current_timestamp() whenever record changes. why 1st timestamp field not generate error message, no matter of 2 1st one.

   however, 2nd not null timestamp column's default value '0000-00-00 00:00:00' if not explicitly define default value.

   see this blog post more details.

2. probably no_zero_date sql mode enabled on server either explicitly or part of strict sql mode. sql mode generates error if want set '0000-00-00 00:00:00' default value or insert value date field.

so, can use timestamp data type in table, make 2nd 1 either nullable or provide 0 or valid date (such epoch) explicit default value.

since marking start , end dates these fields, uding datetime instead of timestamp datatype may idea.