Dates are about the worst thing ever, and I’m not talking about eating pasta with a stranger. I’m happily married, and my dates with my wife typically go pretty well every time.
I’m talking about dates in programming. It’s no secret that in software development, dates suck for a myriad of reasons. In fact, Tom Scott has a whole video about how Time and Timezones suck.
“I can’t really offer much advice here. I can offer a cautionary tale. I can tell you why you should really never, ever deal with timezones if you can help it.”
– Tom Scott
In said video, Tom outlines a ton of different problems with keeping track of timezones. Aside from the typical issues with daylight savings time being observed in some places and not others, England’s daylight savings changes a week earlier, Libya canceled daylight savings time a few days before it, and the West Bank decided they need to have different time zones. He then notes the issue with using Unix timestamps because of leap seconds are not accounted for, so they are not a true representation of time according to the Universal Coordinated Time, and everything sucks. Basically, time itself is human folly and we really should be focusing on space anyway.
In the end of the video, Tom does offer some generalized advice.
“What you learn after dealing with timezones, what you do is you put away your code. You don’t try to write anything to try to deal with this. You look at the people who have been there before you … the people who have built the spaghetti code, and you go to them and you thank them very much for making it open source and you give them credit and you take what they have made and put it in your program and you never look at it again because that way lies madness.”
– Tom Scott
This advice is sensible, but not all that actionable for the boots-on-the-ground developer. This is where I hope to shed some light. So without further ado, after being frustrated for years by dates, times, timezones, and madness, I’ll share what I’ve found to become a master of both dates and times.
Nothing Is Ever Perfect
When it comes to dates, you will get it wrong. And that’s ok. If someone in Papua New Guinea misses a train because you got the timezone wrong, that sucks, but if Papua New Guinea represents 0.001% of your users then you’re probably doing more good than harm. So some self-acceptance here is where I’d like to start. No matter what you do with dates, you’re going to get it wrong.
Store Dates As Unix Time
I’ve struggled with how to store dates for years. There’s no perfect, single, one way to do it. And that’s ok, see “Nothing Is Ever Perfect” above.
Sometimes databases have a date data type and you can use this to store, sort, and manage dates. This can really come in handy when you want to index on dates, or start writing SQL that sorts or filters on dates. One downside is that there’s some subtlety to how dates work. In a Microsoft SQL database, should you make a column contain a data type of time, date, smalldatetime, datetime, datetime2, or datetimeoffset? If you get this wrong, it can ramifications for years to come.
This also has ramifications when moving from one database technology to another. I often advise against worrying about portability. Whether you chose MySQL, Mongo, Microsoft SQL, Amazon DynamoDB, or whatever, moving from one to another is typically something that happens every few years rather than every few weeks. And planning ahead for that is fraught with peril, as the unknowns are so many and the knowns so few. So typically I don’t worry about it. But when it comes to dates, if there were an easy way to keep things portable, wouldn’t you use it?
I certainly would. That’s why I advocate storing dates as Unix time. That is, the time since January 1st, 1970 in seconds or, depending, milliseconds. So what does that look like? As of this writing, the Unix time in millisecond precision is 1587739941289. That’s it. There’s a Unix timestamp.
There are some issues with this. One is that it doesn’t account for leap seconds baked into UTC, but I’ll get to how to handle that in a second. Unless you have a very specific reason not to, storing dates in Unix time has a lot of benefits.
- It’s based on UTC time, which is a Universal Coordinated Time, aptly named as it doesn’t have anything to do with those pesky time zones. It’s universal.
- It is database agnostic and can be stored anywhere you have 13 places of integer available for millisecond precision.
- It is always an integer.
- It can be easily compared with other Unix timestamps using greater than, less than, or equal to logic.
- It doesn’t take up much space in a database.
- It can be easily sorted using any sorting technology under the sun.
Use Millisecond Precision
On one hand, you probably don’t need millisecond precision. Telling people that they’ve accessed a record at a precise millisecond rarely comes in handy. However, the cost associated with storing things at millisecond precision is typically very low, but the risk of not doing so could be very high.
If you do end up needing millisecond-precision for whatever reason after having used second-precision, you can’t go back retroactively to previous records to add that. You’re up a creek without a paddle at that point. And if you really feel like you need to finely tune your database to the point of saving a few bits off every transaction by reducing a 13-digit millisecond-precision Unix timestamp to a 10-digit second-precision Unix timestamp, well, you’re wrong. If you’d like to argue the point, reach out to me on Twitter @jporcenaluk, and remember that your 280-character message can contain 21 Unix millisecond-precision timestamps.
If you need better than millisecond precision, this article probably isn’t for you. You likely are dealing with quantum phasers or ballistic missile launches. I don’t think I want to be responsible for when your quantum phaser ballistic missile launch lands on Neptune rather than Io. Thankfully, this doesn’t apply to 99.9% of the people reading this article.
Don’t Format Until Someone Needs To See It
Within a computer system, formatting doesn’t matter. Keep the source of truth as the Unix time until some human needs to see it. A computer does not care if the date is formatted as Saturday, 23-Jun-83 16:34:49 UTC or 1587739941289, so let the date bounce around in the computer as the latter until a human needs to see it.
When would a human need to see it? Typically that’s through a user interface. That might be in a browser, or an app, or a billboard. It doesn’t much matter. No matter where a human will read it, there are libraries to help convert a Unix time to something human-legible. In JavaScript, for example, there’s Moment.js. In .NET, there’s the DateTime type to help out. Use any one of these tried-and-true methods to change a Unix time to a local time.
Use And Trust A Library For Handling Dates
How do you convert the date? It depends on the language you are using. Let’s say you are using the popular Javascript library Moment.js. This is how you can convert a formatted date to Unix time:
let date = moment.utc("2020-04-24T11:21:39-04:00").valueOf();
console.log(date);The output of this will be 1587741699000. Write a few unit tests around a few different timezones if you’d like, but ultimately trust the library. Don’t test every combination, because someone out of the millions of others that use Moment (or your language’s popular date library of choice) would have noticed if something that fundamental was wrong. Trust the library.
If you want to go back the other direction, to show a Unix time to the user as local time, you can do this:
let formattedDate = moment(1587741699000).format('LLL');
console.log(formattedDate);The output of this will be April 24, 2020 11:21 AM, which is shown in local time to the user based on their system clock. The LLL part denotes the localized format Month name, day of month, year, time. Moment, and indeed all Date libraries including ones built into languages and platforms like C# and Java, allow you to format Unix time back into a localized time that means something to users.
Unit Test The Bejeezus Out Of Date Stuff
One way that programmers typically interact with a program is debugging, and it’s admittedly difficult to understand what 1587739941289 means when debugging. This makes it hard to track down date-related problems if debugging is your primary tool for understanding how your program works.
Using your eyes to look between Saturday, 23-Jun-83 16:34:49 UTC and Saturday, 23-Jun-33 16:35:49 GMT is difficult. Attempting to look at Saturday June 23 vs. Saturday June 24 is more easily understood at a glance while debugging, but it certainly isn’t very precise.
Debugging is incredibly useful for understanding how a program is failing unexpectedly, but it shouldn’t be your primary tool in general for making sure what you are doing is working as expected. For that, you’ll want to use unit tests. The benefits of unit testing are many, but when it comes to dates it’s especially useful.
Write unit tests around how dates are used, converted, and compared within your program, and write unit tests around how they are formatted. Poke and prod using the fast, repeatable medium of unit tests until you are blue in the face. You’ll thank your future self you’ve done so.
Don’t Worry About Leap Seconds
Unix time does not include leap seconds because it follows the POSIX standard, which unambiguously includes the clause that “leap seconds are ignored.” There have been 27 leap seconds since Unix time began in 1970 as of this writing, so does that mean you should write (pseudo) code like this when converting to something a human can read?
const leapSecondsSince1970 = 27;
const formattedDate = new Date(unixTime + leapSecondsSince1970).toLocalTimeFormat();Thankfully, computers are pretty smart, and in practice one doesn’t have to include the leap seconds when converting Unix time to a local time format. You can instead write something like this (pseudo) code to do it:
const formattedDate = new Date(unixTime).toLocalTimeFormat();Convert 3rd Party Dates To Unix Time Immediately
If you are going to call out to a 3rd party API or library, and it returns a date in some god-awful format, please change it to Unix time as soon as possible. Because 3rd parties have no standards or dignity, this means they return dates that look like any number of these things:
2020-04-24T11:21:39-04:002020-04-2404/24/202004-24-202024/04/2020Friday, April 24, 2020 11:21 AMI hate you and you should never know what time it is, ha ha ha ha ha ha ha
I dunno, but I might stop using the API that returns that last one. The point is, dates are stupid. Trying to track how that date is represented in relation to UTC, or local time, and to what precision once it’s bouncing around your program is a recipe for becoming a strict nihilist.
Do yourself a favor. If you see an API returning a JSON object that looks like the following:
{
"id": "1",
"date": "2020-04-24T11:21:39-04:00"
}Convert it to this before it gets past the function that retrieved it:
{
"id": "1",
"date": 1587741699000
}Format Dates Using the Unicode Date Field Symbol Table
Most programming languages use the Unicode Date Field Symbol Table for those somewhat opaque formatting letter combinations. The LLL or YYYY-MM-DD used to format in Java will typically also work in C#, or PHP, or JavaScript. It’s helpful to know that despite the language, there’s some method to the formatting madness.
Once you’ve mastered how to handle dates in your programs, you become an powerful wizard. You can manipulate time as if it were a plaything. What is a second but a thousand milliseconds you can see and control with a whim? You hold the power in your hands to convert any moment on earth into a local time, and turn around and store that as 13 digits representing all the milliseconds that have ever existed since 1970. You are the master now. The master of time.
