Timezone Offsets With Sql Server - Revisited

This week-end I played a little bit with timezones and two new SQL Server 2008 functions:

• TODATETIMEOFFSET()
• SWITCHOFFSET()

It reminded me how I was handling timezones on my websites and I decided to write about it.

Table of Contents

• Introduction
• A Solution - Application Layer
• Another (new) Solution - Database Layer
• Analysis
• Adjusting the timezone in the application layer
• Adjusting the timezone in the database layer
• Conclusion

Introduction

Hi,

Timezone is always a challenge for programmers and webmasters. It is somewhat difficult for everyone on a website to see the content according to their own timezone. If we didn't care about your timezone, you would be able to read articles in the future, or think that an item you want on eBay is already sold.

So everyone must be synchronized. And this synchronization is possible with UTC. Every region in the world have a time relative to UTC. The difference of time between UTC and your region is called an offset. So when we bring everyone on the site back to a zero offset, all the users are synchronizedin the present moment, not according to their time of sunshine!

So how we do it? As far as I know, programmers always implemented a solution in the application layer, not the database layer. With SQL Server 2008 however, we have more options...

A Solution - Application Layer

The trick is, when you have a website with users from all around the world, you put all dates and times converted to UTC in the database.

Here's how I do it:

• When collecting a date from the user, you convert it to UTC and insert it in the database
• When getting the current date from SQL Server, you use GETUTCDATE() whenever you would use GETDATE()
• When displaying a date to the user, you select it from the database, then in your application you convert it relatively to the user's timezone

So if you have a couple of DateTime fields here and there, they should all contains UTC dates in the database. There's one catch with this solution, you have to know the user's timezone whenever you need to display a date, and it's not an easy task.

Well isn't it the root of the problem, knowing your users timezone? Yes but there's no reliable way to determine it without asking them. JavaScript is of little help if I remember correctly and is not reliable. The way I do it, I ask the user's timezone on registration. Another solution would be to put a drop down on pages to change the current timezone and remember the selection in a cookie..

Another (new) Solution - Database Layer

Well, with SQL Server 2008 we have an alternative solution, but it's basically the same thing as above:

• Every dates you insert in the database should be in UTC
• When getting the current date from SQL Server you use GETUTCDATE()
• But when the dates are arriving to the application layer from the database layer, they already should be in the user's timezone (This is the difference)

This means the work is offloaded from the application layer to the database layer. Let's look at some Transact-SQL to see how we do it:

The new function in Transact-SQL to offset a DateTime value is

SWITCHOFFSET(Date, OffsetAmount)

Where...

• Date is the date we want to offset (or convert) (expressed in DATETIMEOFFSET)
• OffsetAmount is how much we want to offset Date to reach a specific timezone

For example, if Date is UTC, and we want to convert it to GMT-05:00 (Eastern), it would be great if we could write:

SELECT SWITCHOFFSET(DateColumn, '-05:00')

And the result of the SELECT would be a date. But that's not quite it. SWITCHOFFSET has been designed to offset any date, not just UTC dates. So the first parameter does not accept a DateTime type but rather a DATETIMEOFFSET structure.

Fortunately, converting a DateTime to a DATETIMEOFFSET is pretty easy. Suppose your DateTime values in a column is in UTC, you would convert the column with:

TODATETIMEOFFSET(DateColumn, '+00:00')

We give a zero offset because we want to stay in UTC. The function returns what we need. Now let's try to put it all together:

SELECT SWITCHOFFSET(TODATETIMEOFFSET(DateColumn, '+00:00'), '-05:00')

We have now converted a UTC date in the database to a GMT-05:00 (Eastern) date! Notice that since the beginning of this article we're working with UTC dates. But keep in mind that you can convert any timezone to any timezone.

Analysis

Moving the date offset calculation from the application layer to the database layer has no notable advantages in my opinion. Depending on the size of the application, it may be simpler. To aid in your decision, I dressed up a list of advantages and disadvantages of doing so. Feel free to post your comments and suggestions!

Adjusting the timezone in the application layer

The good

• Your SQL queries are simpler
• You are able to convert any dates from any source, not just those coming from the database
• If you change your data provider, you're still in business
• Layers are less coupled

The bad

• The user's timezone have to be known by the application layer. But retrieving it from the database and storing it in the session is pretty flexible and easy.
• You have to convert the dates before displaying them to the user. There could be many places where you can forget to do so... Where you do it? Presentation layer? Controllers? You have to pass the target timezone around, etc

Adjusting the timezone in the database layer

The good

• Everything that comes out of your database (stored procedures, functions, queries, etc.) is already in the proper timezone
• The application layer is a little less complex

The bad

• There is not translation in LINQ to SQL for SWITCHOFFSET
• The user's timezone have to be known by the database layer (Not a real problem if we store the user's timezone in the database)
• If the timezone is not stored in the database, the application layer has to constantly pass down this information, creating complexity and maintainability problems
• If the application layer displays information to the user that's not in the database, the application still have to convert the dates to the user's timezone and we now have an hybrid model we have to maintain (bad)
• Your SQL Queries could be a little more complex. For example, you'll consistently have to join the table where the user's timezone is stored.
  

Conclusion

Just because now you can doesn't mean you should calculate your timezones in the database layer, you have to take what fits your needs. You shouldn't take performance as a decision factor but rather maintainability and flexibility. And after weighting the two, I will still do it in the application layer...

You now have an overview of what it takes to create a website "time friendly", and how to do it!

I'm putting time and effort on this blog and having you here is my reward. Make me feel better and better everyday by spreading the love with the buttons below! Also, don't hesitate to leave a comment. Thanks for reading!

See ya