Year 2038 Bug¶

The Year 2038 problem (also called Y2038 or Y2k38 or Unix Y2K) relates to representing time in many digital systems as the number of seconds passed since 00:00:00 UTC on 1 January 1970 and storing it as a signed 32-bit integer. Such implementations cannot encode times after 03:14:07 UTC on 19 January 2038. Similar to the Y2K problem, the Year 2038 problem is caused by insufficient capacity used to represent time. -- https://en.wikipedia.org/wiki/Year_2038_problem

Solution¶

How do we solve it?¶

• Use long data types (64 bits is sufficient)
• For MySQL (or MariaDB), if you don't need the time information consider using the DATE column type. If you need higher accuracy, use DATETIME rather than TIMESTAMP. Beware that DATETIME columns do not store information about the timezone, so your application will have to know which timezone was used.
• Other Possible solutions described on Wikipedia
• Wait for MySQL devs to fix this bug reported over a decade ago.

Are there any possible alternatives to using it, which do not pose a similar problem?¶

Try wherever possible to use large types for storing dates in databases: 64-bits is sufficient - a long long type in GNU C and POSIX/SuS, or sprintf('%u'...) in PHP or the BCmath extension.

What are some potentially breaking use cases even though we're not yet in 2038?¶

So a MySQL DATETIME has a range of 1000-9999, but TIMESTAMP only has a range of 1970-2038. If your system stores birthdates, future forward dates (e.g. 30 year mortgages), or similar, you're already going to run into this bug. Again, don't use TIMESTAMP if this is going to be a problem.

What can we do to the existing applications that use TIMESTAMP, to avoid the so-called problem, when it really occurs?¶

Few PHP applications will still be around in 2038, though it's hard to foresee as the web hardly a legacy platform yet.

Here is a process for altering a database table column to convert TIMESTAMP to DATETIME. It starts with creating a temporary column:

# rename the old TIMESTAMP field
ALTER TABLE `myTable` CHANGE `myTimestamp` `temp_myTimestamp` int(11) NOT NULL;

# create a new DATETIME column of the same name as your old column
ALTER TABLE `myTable` ADD `myTimestamp` DATETIME NOT NULL;

# update all rows by populating your new DATETIME field
UPDATE `myTable` SET `myTimestamp` = FROM_UNIXTIME(temp_myTimestamp);

# remove the temporary column
ALTER TABLE `myTable` DROP `temp_myTimestamp`

Source

Resources¶

• The Internet Will End in 30 Years! - ReadWrite

#bug #database #Y2038