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

TaurusDB 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 system in this failure scenario uses the UTC+8 time zone. Therefore, the default value for timestamp must incorporate an 8-hour offset to remain within the valid range. Specifically, the supported range 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',