Invalid TIMESTAMP Default Value during Table Creation

Scenario

The CREATE TABLE statement failed to be executed.

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

Failure cause: ERROR 1067: Invalid default value for 'session_start'

Possible Causes

The table column type is TIMESTAMP.

GaussDB(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.

1. 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.
2. Run the following command to check the time zone:

   show variables like "%zone%";

3. The UTC+8 time zone is used. The valid range for the default value starts from 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',