24

I am using 64 bit Ubuntu system.

I’m currently working on a project that incorporates MariaDB. I’m planning to introduce timestamp technique into the project so that people will receive the correct time for different timezone.

I’ve heard and read some articles about year 2038 problem for timestamp. Many articles suggest we use a 64 bit system to buy a “bit” more time.

How much time is this “bit” referring to? Is it long enough for us to be able to manage web applications until the end? If that’s not the case, is it like only two years extension so when year 2040 comes, are we going to have applications that do not work appropriately?

Improve this question
4
  • 27
    64-bit systems using 64-bit time_t integers will give you a 'bit' more time - till 15:30:08 on Sunday, 4 December 292,277,026,596. Hope it is long enough for your application ;)
    – Ron
    Sep 17, 2015 at 7:31
  • 4
    It doesn't give you a "bit" more. It gives you exactly 32 bits more.
    – user12205
    Sep 18, 2015 at 6:13
  • 5
    64-bit timestamps is another one of those stopgap measures. What about the people working on Project Utopia in 100,000,000,000,000 CE? They're going to need functional computer systems, too! Using 128-bit timestamps would let us uniquely identify any time from not only this iteration of the universe, but many, many others.
    – Blacklight Shining
    Sep 18, 2015 at 6:32
  • 1
    As you've never accepted any answers before on this site: If one of the answers below helped you, don't forget to click the grey ☑ at the left of its text, which means Yes, this answer is valid! ;-)
    – Fabby
    Sep 18, 2015 at 20:46

2 Answers 2

Reset to default
34

Well if there's an option to literally buy a "bit", ie transfer from a signed 32bit integer to an unsigned 32bit integer, things keep working into 2106.

Transferring to 64bit is "somewhat better". You get hundreds billions of years of resolution.

And Ubuntu does this:

$ uname -p
x86_64

$ date --date=9090-01-01 +%s
224685532800

However, that's the OS level. Just because Ubuntu uses a 64bit integer for its times doesn't mean that MySQL/MariaDB will use it to store its timestamps. If dates past 2038 are important to your now, start testing immediately.

Actually, I can save you some time. It's still broken. This bug was reported over a decade ago but its main test still fails with a 64bit int.

mysql> select from_unixtime(2548990800);
+---------------------------+
| from_unixtime(2548990800) |
+---------------------------+
| NULL                      |
+---------------------------+
1 row in set (0.00 sec)

This isn't even storage. It's slightly pathetic.

(And yes, that was run on MariaDB, version 10.1)

Improve this answer
2
  • 8
    10 years past, they still have 22 years to fix it. <Crosses fingers>...
    – Mindwin
    Sep 17, 2015 at 18:43
  • 4
    NB: Using unsigned 32 bit integers is a hack.
    – Kevin
    Sep 17, 2015 at 19:52
6

Don't store it as an integer at all. Store it as an ISO 8601 formatted date string. This is the standard format used across the Internet.

9999-12-31T23:59:59+00:00
Improve this answer
3
  • 17
    Let's all raise a glass to the Year10K Bug! ;) But in seriousness, while strings are really extendible, they're relatively huge (your example is 200bits!) and parsing and manipulating primitive numbers is a bazillion times faster. That matters.
    – Oli ♦
    Sep 17, 2015 at 21:43
  • 6
    This is great format for displaying - and for that only. For anything else (that is, handling the data until the last moment when you decide to format it to the user), like comparing, doing arithmetics etc. the Unix timestamp as an integer (or float) is much better.
    – egmont
    Sep 17, 2015 at 21:54
  • 1
    @Oli It matters, if it actually matters. This solution doesn't fail when there are times older than the UNIX epoch. The format is the standard used for dates all over the Internet, in protocols and APIs. If you are storing something in a MariaDB column, then really, it's how you should store it on disk. Sure, in memory, maybe you want to store it into a more amenable data structure. And you don't need the last 40 bits if you always use UTC.
    – dobey
    Sep 18, 2015 at 1:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.