Thinking about the end of time … Unix time
Everything is about time! Friday will be my 20th year in IT — I can start doing the “half way to retirement” memes now.
The Prime Minister of New Zealand, Bill English, announced yesterday that the retirement age in New Zealand will be slowly increased to 67, so I might not quite be there yet!
Then this morning our chief architect dropped another bomb — where will we be on 19th January 2038? Because that date will be the end of time … Unix time.
Unix time is a measure of time in milliseconds since 1st January 1970. This 32-bit number “rolls over” on 19th January 2038. Unix is used as a timestamp for transactions just about everything.
This caused a lunchtime session of thrashing out the problem. How’s this likely to be solved? From a code-side point of view, this could be relatively simple — just redefine your timestamp to use a 64-bit over a 32-bit timestamp. This isn’t majorly complex as a piece of work, so take a sigh, and prepare to walk away.
Except … you’ve redefined your timestamp in your code. If you’re something like a bank, you’ll have financial transactions which could go back as far as the 70s! That’ll be trillions of records! And even if you’ve updated your code, the records in the database will still be using 32-bit Unix time.
What you now have is a problem of epic data migration!
Once again, we threw around a few ideas. None of these there’s any urgency in doing, but as a mental exercise in problem solving, it was a lot of fun!
Solution 1 — Cut over to a new database — but keep the old one working
You basically get a cut off date, and anything from this point over will be handled as a 64-bit Unix time, anything in the old database table you know is in 32-bit Unix time, and hence pre-dates the new database table (which uses 64-bit).
In some ways, this would be like when you archive your old emails. It’s relatively simple, and no worries about moving over data.
The problem is it’s ugly, and means you’re looking after either two databases or tables which essentially contain the same information. Any time you’re doing queries, you’ll have to do it over two tables.
Solution 2 — update the existing database table
Some people were trying to play with different ways of doing this. Essentially you need to move the record from the old 32-bit datebase table to a 64-bit datebase table, including adding 32-zeros at the front!
One solution was to have a system very like solution 1, but with the old database replicating over time to the new database, and with records removed from the old database once confirmed they’re now in the new database.
Considering the huge amount of records that could be involved, this could take a month (just from a guesstimate). During that time, any power outage could mean the old and new database gets out of sync, and records could be lost.
However this would be really simple — you’d keep the migration going until the old database table was empty.
The alternative was to create a unique key for records — probably from datetime and a few other identifiers. If the record existed in the old database, but not in the new one, replicate across.
This would probably take longer, but less risk of losing records.
The problem would be that in the interim before the last record was copied, you’d need some kind of query much like solution 1, where you’d look over both the old and new tables.
Solution 3 — migrate to a new platform
Probably our third solution was our most elegant. You try and merge this change with an infrastructure refresh. This means you can build a separate environment secretly alongside your new one.
Your old database can replicate to the new one, all the time people are using the old platform. At a set point, much like in A-B testing, you switch over from the old site to the new one, and all the data is ready to go.
The disadvantage here is that you need everything ready way ahead of time, for the replication to have time to work. And how many times do you know IT projects go down to the wire?
There’s also likely to be a small trickle of transactions which will trickle over once the switch happens, but should be a temporary effect.
Once again, just a great example of thrashing out ideas as a group, which helps us develop an ability as a team to work on problems much more closely.
