How to choose MySQL column data types resilient to year 2038 -rollover?

Question

January 19, 2038 03:14:07 GMT is now less than 20 years away. That's the time that UNIX's 32-bit timestamp rolls over. I'm working on designing some MySQL tables that may still be in use at that time.

This is the so-called Year 2038 problem.

It seems, from stuff I've tried on MariaDB 10.3, using the TIMESTAMP datatype yields error 1292 (incorrect datetime value) for datestamps after the date rollover.

What is a good practice for designing these tables to be future-proof? I could use DATETIME data, but TIMESTAMP has some very useful features with respect to timezones.

Is there any chance some future version of MySQL (not to mention Linux and the other UNIX derivatives) will upgrade?

Answer 1

Use BigInts to store a unix timestamp. This is functionally equivalent to the TIMESTAMP type though lacking some of the sugar that is attached to that. However, if at the application level you're happy to just consume UNIX timestamps, it makes no difference at all and, for me so far at least, is trivial to handle at the database layer with the occasional UNIX_TIMESTAMP(…) / FROM_UNIXTIME(…) call. That will keep you going far beyond 2038.

Though I expect the MySQL / Maria mob will create some hack in version X.y that will automatically update TimeStamp fields as part of the upgrade path. It will probably be released on January the 18th, 2038, mind you. ;)

Anyway, if you want to future proof, BIGINT treated as a UNIX time stamp is your answer.

Answer 2

Pull out the code you wrote in 1998. Find a machine to run it on. You may need to find a floppy drive to load it.

Now, ask yourself "what happens to code and hardware in 20 years"?

I suggest that, aside from government contracts, all code written in 2018 will be in the trash long before 2038.

In 1998, MySQL was running version 3.xx; I would not want to touch that with a 10-year (or 20-year) pole. Since then, the internal format of DATETIME has changed, CHARACTER SETs were added, lots of optimizations were added, subqueries were added, bugs fixed, etc, etc.

My point in that last paragraph is that whatever you write today will undergo application changes as MySQL matures over the next 20 years. Fixing things for the 2038 problem will simply be one of many changes.

Answer 3

Use these two function and store timestamp as a decimal

CREATE FUNCTION from_unixtime_fixed (v DECIMAL(16,6))
    RETURNS DATETIME(6) DETERMINISTIC
    RETURN DATE_ADD(FROM_UNIXTIME(0), INTERVAL v second);
CREATE FUNCTION unix_timestamp_fixed (v DATETIME(6))
    RETURNS DECIMAL(16,6) DETERMINISTIC
    RETURN TIMESTAMPDIFF(SECOND, FROM_UNIXTIME(0), v);

Sample usage: select unix_timestamp_fixed('2039-01-01');

returns 2177449200.000000

select from_unixtime_fixed(2177449200.100000);

returns 2039-01-01 00:00:00.100000

reduce the DECIMAL(16,6) to DECIMAL(16,0) if milliseconds don't interest you