Converting UNIX timestamp (BIGINT) to DATETIME value using DATEADD function

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.

Read more: Beyond Relational

Posted via email from Jasper-net