Inside the mind of a geeky learning technologist

1601 Gregorian Epoch, unixtime, and MS SQL


A small part of my day job involves writing and maintaining integrations between third-party e-learning tools and our bespoke student record system. Often, this involves digging through documentation and discussion forums, working out which bits of the API and database we need, and writing something which mashes things together in a semi-successful manner.

Recently, I’ve been working with a software package which consists of a central server and a number of remote machines. The web UI for this software shows us when a remote machine was last active, and we can use this to determine whether there are any problems, but frustratingly the SOAP API doesn’t expose this information. After digging around in the database I discovered a potential candidate, a table called RemoteRecorder with a column entitled LastCheckin. At this point I would normally think ‘Mission Accomplished’ and be done with it. But annoyingly the LastCheckin column stored its information as a float value instead of a datetime (eg. 13034058916.8232, not 2013/12/24 09:00).

Seeing a date in this manner is usually indicative that it is stored in unixtime format, which is the number of seconds elapsed since 1st January 1970, and is fairly trivial to convert into a readable datetime. However, in this instance, the conversion SQL failed with a cast error and an overflow error. Plugging the date into an online unixtime converter yielded a date which was very close to what we expected, but approximately 370 years into the future (January 13th 2383, 03:55:16). More interestingly, it ignored the decimal part of the timestamp (0.8263).

So, what’s so important about the 369 year difference between 2083 (from the problematic unixtime, above), and 2014? Well, the timestamp above looks very similar to a unixtime value, only it’s 369 years into the future and has a decimal component. Subtracting 369 from the 1970 unixtime epoch yields 1601; the start of the Gregorian calendar, and according to Wikipedia, the epoch date for Microsoft Windows. Helpfully, Wikipedia also tells us that the decimal component is merely the number of milliseconds in the timestamp; which can safely be ignored (which is fortunate, since the MSSQL DATEADD method can only cope with integer values).

So, going back to the original problem, how do you convert a float value into a usable datetime? In MSSQL, you can use the following:

SELECT LastCheckin, DATEADD(second, (CAST(LastCheckin as BIGINT) - 11644473600),{d '1970-01-01'})
FROM RemoteRecorder

Breaking this down into its component parts:

  • CAST(LastCheckin as BIGINT) – strips off the decimal component, so 13034058916.8232 becomes 13034058916, and suitable for the DATEADD operation.
  • – 11644473600 – strips 369 years’ worth of seconds from our above datestamp, increasing it from 1701 to 1970, at this point our datestamp is effectively a unixtime stamp
  • DATEADD(second, …, {d, ‘1970-01-01’}) – converts our now unixtime stamp to a date and time that we humans understand

That’s it.

Leave a Reply

%d bloggers like this: