Invalid TIMESTAMP Default Value during Table Creation

Scenario

The CREATE TABLE statement failed to be executed, and the error message "ERROR 1067: Invalid default value for 'session_start'" was displayed.

CREATE TABLE cluster_membership
(
...
session_start TIMESTAMP DEFAULT '1970-01-01 00:00:01',
...
);

Possible Causes

RDS for MySQL converts the value inserted to the TIMESTAMP column from the current time zone to the UTC time for storage. During query, it returns the value by converting the UTC time to the current time zone.

The time range for the TIMESTAMP column is from '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC. For details, see The DATE, DATETIME, and TIMESTAMP Types.

Run the following command to check the time zone:

show variables like "%zone%";

UTC+8 is the time zone, so the valid range for the default value starts with 1970-01-01 08:00:01.

Solution

Change the default value of the TIMESTAMP column.

session_start TIMESTAMP DEFAULT '1970-01-01 08:00:01',