CODEWITHSUNDEEP
mysqldatabasedate
LostReality
LostReality

Reputation: 687

Incorrect datetime value if TIMESTAMP is too far in the future in MySQL

I have setup a mysql database and I am encountering a strange error whenever I try to insert a timestamp in a table : Incorrect datetime value: '2117-11-20 06:04:35' for column 'NOT_VALID_AFTER_DATE' at row 1

I tried different values and surprisingly some values with exact same format are OK :

I do not get why the date would become invalid past a certain year. I have read a bit of documentation and it does not seem to talk about that kind of limitation.

Has anyone a clue ?

Thanks for the help,

Upvotes: 1

Views: 2436

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562260

Did you read this documentation: https://dev.mysql.com/doc/refman/8.0/en/datetime.html

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

This is because the value is stored as a 32-bit unsigned integer. The integer is the number of seconds since the UNIX epoch, which is 1970-01-01 00:00:01. The maximum value is 232-1 seconds, which results in the larger timestamp value mentioned in the documentation.

If you need dates outside of that range, use DATETIME. See the manual page I linked to for more details.

Upvotes: 1

Related Questions