Timestamp, year 2038 problem for 64 bit Ubuntu system

Question

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?

Solution 1

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)

Solution 2

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

Comments

[Ron]

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 ;)

[user12205]

It doesn't give you a "bit" more. It gives you exactly 32 bits more.

[Blacklight Shining]

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.

[Fabby]

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! ;-)

[Mindwin]

10 years past, they still have 22 years to fix it. <Crosses fingers>...

[Kevin]

NB: Using unsigned 32 bit integers is a hack.

[Oli]

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.

[dobey]

@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.