The past few months have found me seriously investigating open source technologies for development… more specifically, the MEAN Stack (MongoDB, ExpressJS, AngularJS, and NodeJS). I started on this path to find better front end technologies however, the more that I did, I am finding countless techniques to improve the dev experience in the Windows and .NET world as well. One prime example of this is the use of UNIX Dates.
All of us have been challenged with dates at one point or another. Whether we’re having to escape characters for a RESTful query or query string, or maybe trying to save space by switching from DateTime to Date in our SQL database, or even just parsing dates from a file, they can be a pain… or, at least, increase the hoops we have to jump through. In my opinion, UNIX Dates solve many of these issues… even in .NET and Windows.
Basically, a UNIX Date is an integer representation of the number of seconds between now and January 1, 1970 (notice I said “seconds” and not “milliseconds”). And, yes, this means using an integer will make them not as precise as a DateTime (using a decimal will take care of the milliseconds) and they won’t span the entire range of available values as a DateTime will within SQL Server. However, for most of today’s LOB apps, these integers cover a good 99% of what we commonly need.
From a space perspective, they have an immediate benefit within SQL Server: they provide precision and yet, when using an integer, take up half of the space compared to a DateTime value (4 bytes vs 8 bytes). For audit columns alone, this chops a load off of each table… especially those annoying association tables where the audit columns might constitute most of the space consumed.
The biggest benefit of UNIX Date stamps, in my opinion, is when it comes down to handling them. Math becomes a breeze since you’re calculating one numeric versus another. Formatting and parsing is simple compared to a date string… the value is either a valid number or it’s not. And, of course, any time you’re working on a cross-platform app, like something in AngularJS or NodeJS, libraries like MomentJS handle them as a first-class date object.
So, for me, even in SQL Server, unless I need ultra-precision, I’m gonna start using the UNIX Date format when storing dates. If I need something cosmetic, for a report or UI, I can easily parse it on a view or computed field.
