Understanding default values for timestamps in MySQL

Other Questions MySQL
1

I am using MySQL version 5.7.20 and I have some questions about how default values for timestamp columns work, especially when I don’t set them explicitly.

create table activity_log (
  event_time timestamp,
  created_at timestamp NOT NULL ON UPDATE CURRENT_TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at timestamp NOT NULL ON UPDATE CURRENT_TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

This SQL statement runs smoothly and successfully creates the table.

On the other hand, when I use a simpler version like this:

create table activity_log (
  event_time timestamp,
  created_at timestamp,
  updated_at timestamp
);

I encounter error 1067 which states “Invalid default value for ‘created_at’”. In the first example, I’ve observed that the event_time column takes CURRENT_TIMESTAMP as its default and keeps updating by itself. Can someone clarify what the default behavior is in the second SQL, and what causes it to not work like the first? I’m looking to understand the differences between the two setups.

2

This happens because of MySQL’s explicit_defaults_for_timestamp setting. In MySQL 5.7, this is disabled by default, so the first TIMESTAMP column automatically gets DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP unless you specify otherwise. Any additional TIMESTAMP columns without explicit defaults will throw that error - MySQL can’t assign them a valid default value. Your second query fails because created_at is the second TIMESTAMP column and doesn’t have an explicit default. Meanwhile, event_time works fine since it’s first and gets the automatic behavior. Check your current setting with SHOW VARIABLES LIKE 'explicit_defaults_for_timestamp'. If you enable it, all TIMESTAMP columns behave the same way - they’ll need explicit defaults and you won’t get this weird automatic behavior anymore.

3

This occurs due to MySQL’s peculiar handling of timestamp columns when explicit_defaults_for_timestamp is not enabled. In this scenario, only the first timestamp column automatically receives DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP. Other timestamp columns, like created_at, cannot be NULL but lack an automatic default, resulting in error 1067. To resolve this, you can either enable explicit_defaults_for_timestamp or explicitly set defaults for all subsequent timestamp columns. Alternatively, using DEFAULT '0000-00-00 00:00:00' for non-auto-updating columns is an option, although this practice is deprecated in newer MySQL releases.