2

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?

O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • 3
    . . Your MySQL tables will still be in use, but by, say, MySQL version 20.0, they will probably have an upgrade path that fixes this problem. You could always use `datetime` and store the timezone offset in another column. – Gordon Linoff Sep 15 '18 at 13:18
  • @GordonLinoff you're assuming that OP doesn't store future times. Also: NEVER store the offset, store the Olson string such as Europe/Amsterdam. – Evert Sep 15 '18 at 14:42
  • @Evert . . . Ironically, the OP is not asking about storing future dates now. That is an issue -- and a current issue. Lots of financial instruments (such as American mortgages) already expire after 2038. – Gordon Linoff Sep 15 '18 at 18:50
  • I would use `DATE` for mortgages, not `DATETIME`, nor `TIMESTAMP`. I'll not be around to deal with `DATE's` Y10K-problem. – Rick James Oct 01 '18 at 17:26

3 Answers3

4

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.

JaneDoe
  • 430
  • 1
  • 4
  • 16
  • Sure. But `DATETIME` is also future proof and has the advantage of not requiring conversion before using datestamp arithmetic on it. – O. Jones Sep 15 '18 at 14:53
  • No. Datetime has the same upper limit as the 32-bit UNIX time stamp that you're concerned about. Plenty of applications might want to set a date ahead of this. Doesn't even have to be 20 years in the future. Just has to be more years in the future than however long before 2038 they finally fix this. I also prefer something that is an ACTUAL measure of time, not something that changes with the timezone. Datetime is currently no more future proof than Timestamp or 32bit int UNIX time. Datetime doesn't answer your question. – JaneDoe Sep 15 '18 at 15:31
  • 1
    with respect, `DATETIME`'s range goes to just before `10000-01-01`. See this in MySQL 5.5's documentation. https://dev.mysql.com/doc/refman/5.5/en/datetime.html – O. Jones Sep 15 '18 at 17:17
  • My error. But the rest of it stands. DateTime is not an actual value of time. You asked how to deal with the 2038 issue in MySQL / MariaDB and that you didn't like DateTime and did like Timestamp. So I gave you a way to use UNIX timestamps which will work for most scenarios and is future proof. Unless you're doing a lot of your business logic in the database (bad practice), it shouldn't be a big deal. At any rate, that's the answer I have for you. – JaneDoe Sep 15 '18 at 17:25
  • 1
    `TIMESTAMP` automatically adjusts for timezone of the client; `BIGINT` does not. – Rick James Oct 01 '18 at 17:27
  • @RickJames Well that's what I meant by "lacking some of the sugar that's attached to it". It stores the same value in the database and you can access it as the same value. – JaneDoe Oct 06 '18 at 07:44
  • @JaneDoe - For times, "same value" is ambiguous. Do you mean "what I see on my clock" (implemented via `DATETIME`) or do you mean "same time", as achieved by UTC and `TIMESTAMP`? – Rick James Oct 06 '18 at 19:34
  • It's not ambiguous. I mean same value. They are stored as the same value. Any manipulation to change the output happens above that layer. And frankly, you're becoming extremely tiresome. OP asked a question about future proofing. I've answered an answer that works which is use BigInt and apply any time zone modifications later. If you don't like the answer then downvote it but quit presenting doing timezone manipulation in the database as a necessity. Future proofing in this approach is achieved by doing it elsewhere as clear in my original answer. Very bored with this now. – JaneDoe Oct 24 '18 at 21:36
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.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • Good point. I started coding PHP and MySQL 20 years ago and have gone through many code changes to upgrade. A great example is going from mysql to now prepared statements with mysqli or PDO. – Dawson Irvine Jan 04 '21 at 02:18
  • 1
    @DawsonIrvine - I spent the last two weeks dealing with incompatibilities with PHP 8.0 in dozens of programs. – Rick James Apr 06 '22 at 22:17
0

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

theking2
  • 2,174
  • 1
  • 27
  • 36