MySQL timestamp and the Y2K38 Problem

Following Y2K and Y2K20, this piece examines the Y2K38 Problem as the last major issue in date and time systems. Unix time stores seconds since January 1, 1970 UTC as a 32-bit integer, so it reaches 2,147,483,647 at 03:14:07 on January 19, 2038, and overflow occurs starting the very next second.

This limitation affects every area that uses Unix timestamps, including OS, applications, databases, and binary files. The article shows an actual example where, on 32-bit Linux and a C program, crossing 2038 causes the time to roll back to 1901, and compares this with the same test running correctly on a 64-bit environment.

In MySQL as well, timestamp works the same way as Unix time, so it has the same problem. In contrast, datetime is less affected by UTC conversion and the 2038 limitation, and storage space is described as 5 bytes + fractional seconds for datetime versus 4 bytes + fractional seconds for timestamp.

In actual testing, on MySQL 8.0.27, unix_timestamp('2038-01-19 03:14:08') rolls back to 0, but on 8.0.28, the same value is calculated correctly as 2147483648, 2147483649, and so on. The key to this improvement is that my_time.h now references a new header, my_time_t.h, and the time type has been extended to be int64_t-based.

In summary, whether MySQL timestamp handles Y2K38 properly differs by version and component, and in actual production environments you should check the following:

• Whether it can handle times after 2038-01-19 03:14:07
• Whether the MySQL version in use supports 64-bit time handling
• Whether datetime would be more suitable than timestamp
• Whether the OS, libraries, applications, and schema have all been upgraded to the same standard