MySQL throws errors while adding datetime less than 1970

Question

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.

What is the datatype of the column, DATETIME or TIMESTAMP? I can use that date for a DATETIME, but not TIMESTAMP. — Barmar
WHen I try to use that date with a timestamp I get a warning "Out of range value for column 'd2' at row 1". You may have a setting that's turning the warning into a fatal error. — Barmar

Barmar Barmar · Accepted Answer · 2022-07-28T20:14:33

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.

MySQL throws errors while adding datetime less than 1970

1 Answers