Taking Care of Time Zones

When creating an application accessible anywhere around the globe, special attention must be given to data involving date and time. A mere DateTime data type is not enough and sometimes, could be a dangerous proposition. The system should also be aware of the time zone. Fortunately, this is very easy today that SQL Server 2008 has already a data type specifically for this requirement – the DateTimeOffset.

Let’s consider a web application that allows viewing of historical data with regard to the status of a task. A designer who tends to skimp on date and time data analysis would come up with the “wrong way” model similar to the left part of this illustration:

Query for historical data usually involves passing the date time when the query is requested and then qualifying this date time BETWEEN the start date and end date columns. To signify that a record is the latest version, the end date is set to null or unrealistic high value like ‘12/30/9999’. This guarantees a match if the current date is passed. Applying this technique in our query for the wrong model, the following scenario would fail:

A user from Manila, Philippines updates the status of Task 1 on 06/26/2009 0900 local time. Two hours later, a user from Tampa, FL tries to view the latest status updates.

Here is the content of our database after the update in Manila

The scenario fails because in Florida, it’s still 06/25/2009 11:00PM and as far as the system is concern, that is earlier than the update date:

The DateTimeOffset data type of SQL Server 2008 makes it easy to avoids this pitfall. Prior to SQL Server 2008, the solutions would usually involve saving the hour offset in the database and use this to manipulate the timezone-agnostic date time in the UI. We applied this new data type in the right side of the model - the “Right Way”. For this model, I opted for LINQ using LinqPad. I find LinqPad to be a very effective prototyping tool and maybe you should give it a try. So back to our discussion, the codes below first simulate the transaction from different time zones. After that, we use the generated timezone-aware dates for the query. As you can see, our parameter qualifies for the condition, as expected, even though the date parts are very far apart. Because of the offset value in the dates, SQL Server correctly computes and determines that Tampa time is actually just 2-hour later.

The DateTimeOffset is really a very valuable addition to SQL Server. It addresses a small but significant shortcoming SQL Server in an era of outsourcing, e-commerce and cloud-computing. I myself is already wary on using DateTime because I just don’t know when a pathetic application of mine would go global. Oh yeah! ;)