I noticed than MySQL throws an error if I try to add datetime with value less than 1970 year (for instance, 1969-01-01 00:00:01). The reason for this is that timestamp in MySQL starts only from 1970. But, in my testing server, I am able to add datetimes less than 1970. On production server - not. Why so? I suppose that MySQL was configured differently on production and testing servers using different sql modes. But I couldn't find which mode is responsible for such a behaviour.

CodePudding user response：

You apparently have strict SQL mode enabled on the production server, but not the testing server. The documentation on Out-of-Range and Overflow Handling says:

When MySQL stores a value in a numeric column that is outside the permissible range of the column data type, the result depends on the SQL mode in effect at the time:

• If strict SQL mode is enabled, MySQL rejects the out-of-range value with an error, and the insert fails, in accordance with the SQL standard.

• If no restrictive modes are enabled, MySQL clips the value to the appropriate endpoint of the column data type range and stores the resulting value instead.

When I try to store that date into a TIMESTAMP column on a server without strict SQL mode, I get a warning:

Out of range value for column 'd2' at row 1

and it stores 0000-00-00 00:00:00 instead.