Understanding MySQL Timestamp Default Values and Zero‑Date Handling

Background : A client defined a column b timestamp NOT NULL in a MySQL table. After execution MySQL automatically added a default value, turning it into b timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' . The article investigates why this happens.

How MySQL sets default values for TIMESTAMP columns :

When the session variable explicit_defaults_for_timestamp is enabled (value = 1), non‑standard behavior is disabled. In this mode a TIMESTAMP column gets a default only if the column is declared with CURRENT_TIMESTAMP (or NOW() ) or with an explicit DEFAULT clause. A NOT NULL TIMESTAMP without a default is considered to have no default value.

When explicit_defaults_for_timestamp is disabled (value = 0), MySQL reverts to legacy behavior. The first TIMESTAMP column without an explicit NULL or DEFAULT gets DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP . Any subsequent TIMESTAMP column without an explicit declaration receives the “zero” default DEFAULT '0000-00-00 00:00:00' .

Zero‑date allowance : Whether the “zero” timestamp is accepted depends on the sql_mode setting. If NO_ZERO_DATE is present, the value '0000-00-00 00:00:00' is rejected; otherwise it is allowed.

Test cases on MySQL 5.7.24 :

Scenario 1

set session explicit_defaults_for_timestamp=1;
set session sql_mode='NO_ZERO_DATE';

Result: No default is added to the TIMESTAMP column because the parameter is enabled.

Scenario 2

set session explicit_defaults_for_timestamp=1;
set session sql_mode='';

Result: Same as Scenario 1 – the column receives no default.

Scenario 3

set session explicit_defaults_for_timestamp=0;
set session sql_mode='NO_ZERO_DATE';

Result: The second TIMESTAMP column b gets the zero default '0000-00-00 00:00:00' , but because NO_ZERO_DATE is active MySQL throws ERROR 1067 (42000): Invalid default value for 'b' .

Scenario 4

set session explicit_defaults_for_timestamp=0;
set session sql_mode='';

Result: The column b receives the zero default without error, since zero dates are permitted.

All screenshots referenced in the original article illustrate the outcomes of each scenario.

Recommendation : Keep explicit_defaults_for_timestamp=1 and include NO_ZERO_DATE in sql_mode to avoid unintended zero‑date defaults.