Contrary to popular belief, offices can be fun – especially if you are faced with new challenges each day. Hence, this week, we will be taking a small break from discussing the “Underappreciated features of SQL Server”, and instead discuss something that happened at the office a few days ago. One of the project engineers asked me a seemingly very simple question.

“Can I represent a UNIX time value to a valid date using SQL Server? If yes, can you help me on how to achieve this?”

As always, I gladly agreed to assist him resolve his query. But before I could help him, I needed some help myself. The first question that came from me to him was “What is a UNIX time?”. The poor guy was petrified at the prospect of being helped by someone who doesn’t even know what the context of the discussion means. Anyway, this is what he explained to me:

UNIX time

UNIX time, or POSIX time is defined as the number of seconds elapsed since midnight of January 1, 1970 (in Coordinated Universal Time (UTC)), not counting leap seconds. The only reason why this does not consider leap seconds is because the Coordinated Universal Time (UTC) did not come into existence (in it’s current form) until 1972. UTC introduced the concept of adding and deleting leap seconds to keep the clock in synchronization with the Earth’s rotation.

Anyway, UNIX time is quite simple actually (simple is always good!). The UNIX epoch is the time 00:00:00 UTC on January 01, 1970. The Unix time number is zero at the Unix epoch, and increases by exactly 86400 seconds (24 hours * 60 minutes/hour * 60 seconds/minute) per day since the epoch.

Thus, a UNIX time of 1293840000 corresponds to the midnight of January 01, 2011 (the New Year!).

Once I was clear on this, I was able to help him out and here’s how. Immediately after starting, I landed up with a problem.

The Problem

Theoretically, simply adding the number of seconds to January 01, 1970 using the SQL DATEADD function should be sufficient to convert a UNIX timestamp to SQL Server DATETIME. And hence, I directly attempted the same, but the solution failed in basic unit testing. Here’s how:

For midnight, January 01, 2011 (UNIX time = 1293840000), the approach of directly adding the seconds to the UNIX epoch works fine. However, the moment I attempt to convert a timestamp value that is higher than the INTEGER data type limit (e.g. midnight January 01, 2050 = UNIX time 2524608000), I end up in an arithmetic overflow error. The source of the problem is the DATEADD function, which cannot accept a BIGINT interval value.

DECLARE @DateInt     INT
DECLARE @DateBigInt  BIGINT

SET @DateInt    = 1293840000 /* (UNIX time = 1293840000 => midnight, January 01, 2011) */
SET @DateBigInt = 2524608000 /* (UNIX time = 1293840000 => midnight, January 01, 2050) */

/**************** Building the Scenario *****************/
PRINT DATEADD(ss, @DateInt, '1970-01-01')
PRINT DATEADD(ss, @DateBigInt, '1970-01-01')

In today’s business world, not being able to handle dates as “close” as January 01, 2050 is not at all acceptable. This “limitation” has already been reported to Microsoft (refer the Connect link here). While I do not believe this to be a SQL Server defect (Why? read on…), we undoubtedly need something for applications being written today. Below are a few of my attempts to overcome this challenge.

The Solutions

I will be presenting two possible solutions before you – one today, and the other one later on in the week. Depending upon your preference, you may use either one in your application.

For today, let’s work our way backwards. We do not necessarily need to add the entire difference (in seconds) to the UNIX epoch time. DATEADD provides us the freedom to add years, months, and even days. Because we know that each day in the UNIX time is exactly 86400 seconds long, we can easily calculate the number of days elapsed based on the seconds information provided by performing a simple division.

In an ideal world, we would not be dealing only with a time value of midnight. We can have any hour, minute or second in the day coming through as part of the BIGINT number that we receive in UNIX time from interfacing systems. Because (in normal approximated terms) a day can have a maximum of 86400 seconds (which can be handled by an INT data-type), we simply need to use the modulo operator to get the number of seconds from the last midnight value.

/* Method 01 - Calculate based on the number of seconds per day (60sec*60min*24hrs)=86400sec */
SET @DateInt    = 1293840000 /* (UNIX time = 1293840000 => midnight, January 01, 2011) */
SET @DateBigInt = 2524644630 /* (UNIX time = 2524644630 => 10:10AM, 30 seconds, January 01, 2050) */
SELECT DATEADD(ss, @DateInt,            '1970-01-01')                                    AS 'IntegerDateRange',
       (@DateBigInt/86400) AS NumberOfDaysSinceEpoch,
       (@DateBigInt%86400) AS NumberOfSecondsFromMidnight,
       DATEADD(ss, (@DateBigInt%86400), DATEADD(DAY, @DateBigInt/86400, '1970-01-01' ) ) AS 'BigIntDateRange'

Here’s what the output looks like:

Conclusion

We have thus successfully represented a UNIX time as a SQL Server DATETIME value (represented as UTC time). However, please note that because of the fundamental differences between UTC and UNIX time, the conversions can be a bit off depending upon the number of leap seconds introduced till the date under consideration.

As I was researching on how to use BIGINT in the DATEADD function, an interesting observation came to light about using the 32-bit INT data-type – which forces me to start thinking about the time I have left to upgrade my 32-bit home operating system and hardware to 64-bit. This is referred to popularly as the Year 2038 problem, which I will write about later in the week along with the second method to convert a BIGINT to a DATETIME value. The upcoming article will also justify why I do not believe this to be a SQL Server defect.

Be courteous. Drive responsibly.