Sunday, May 17, 2009

Setting a Time Zone in your .NET application

One important consideration when designing software systems is globalization.  This is nothing new - we have had software for many years that must transcend various cultures and languages.  But there is an aspect of globalization that has become more mainstream in the last two years - Time Zone globalization.  The evidence I offer you to substantiate this opinion is that in the last two years M$ has released major version of two product lines - SQL Server 2008 and the .NET Framework 3.5, which have features that specifically target the Time Zone globalization problem (The TimeZoneInfo and DateTimeOffset classes in .NET 3.5, and the DateTimeOffset Data Type in SQL Server 2008)

The Time Zone globalization problem is not new.  On Google, I found articles about solving this problem that date back to 2000 and beyond.  I think what makes this problem more relevant today is that software systems are more distributed; managed software (aka hosted software), software as a service, and software in the cloud are more mainstream today than ever before.

What is the Time Zone globalization problem?

I’m going to attempt to describe this problem in the form of an example.  You are  a software company.  10 years ago you built a web application that you sell and license to your customers.  Your product is very good and has exploded in popularity.  Last year you decided to expand your product line by offering this product as a managed service.  Now, instead of owning a license to your product, you host the product, and your customers pay for the privilege of using it on a subscription basis.

When your customers license and own your product - there is no Time Zone problem.  Your customers install your product on their servers where they have complete control of the Time Zones.  If your customer is a Massachusetts company, but their data center is in Colorado - it makes no difference.  The customer is in control of the data center; if they want to set all of their Colorado servers to Eastern Time because that is what makes sense to their business, that is their prerogative.  

When your customers subscribe to your managed service - there is a potential Time Zone problem.  You, as the managed service provider, are in control of the software and the servers, and thereby in control of the Time Zone on those servers.  Your hosting data center is in Colorado, so therefore you set the Time Zone to Mountain Time.  This presents the first side effect of the Time Zone globalization problem:

Your customer’s data is now in your local Time Zone, not theirs.

This may lead to various usability problems.  You’re customers employees, who are users of your product, will have to convert back and fourth between their local Time Zone and Mountain Time.  For example, lets say the hosted application is a Customer Service Call Center.  If a Customer Service Rep wants to set a call back reminder for 2pm Eastern Time, they’ll have to remember to set the reminder to 4pm (because the server Time Zone is Mountain Time which is offset by 2 hours from Eastern Time).

Lets take this example a bit further.  For various system integration reasons, you exchange data on a regular basis with your customers.   You’re customer sends you data in one Time Zone, you send data back in another.  This presents the next side effect of the Time Zone globalization problem: 

Your customer’s data is no longer in one Time Zone.

This may lead to various business rule problems.  For example, lets say the hosted application is a claims processing application, and the customer hosts their financial accounting application in-house in a different Time Zone.  To reconcile claims against payments you exchange data on a nightly basis.  Every so often, the job runs past 2:00am Mountain Time, which increments to the next day on the server, and pickups the next day of claims.  This is an unexpected condition and the job fails.

Possible Solutions

There are several ways to solve this problem.  One possible solution is to set the server, or operating system time, to the Time Zone of your customer.  This way we are back to one Time Zone and this problem ceases to exist.  This solution is not always possible.  If you are a managed service provider, you likely have customer across various Time Zones.  So setting server time to Eastern Time may help one customer, and ale another.

Another possibility is to fix this problem at the database level.  With SQL Server 2005, the datetime data type is stored as two 4-byte integers.  The first 4 bytes store the number of days before or after 1/1/1900.  The second four bytes store the time of day represented as the number of 1/300-second units after midnight.  This data type is not Time Zone aware.  So SQL Server 2005 is not Time Zone aware.  If you are storing the datetime “5/17/2009 20:00”, SQL Server has no way to distinguish if you mean Eastern Time, Mountain Time, or another Time Zone.  With SQL Server 2005, you can write some programming code that uses the CONVERT function to convert between Time Zones either when the data is stored or queried.

With SQL Server 2008, there is a new data type: DateTimeOffset which is Time Zone aware:

Using this data type, the Time Zone can be stored along with the date/time.  This does not solve the usability problem described above, as you will still need to convert to display the datetime in a different Time Zone.  But it does help you in solving the systems problem when exchanging data.

The third possibility is to solve this problem in the application itself.  When this issue came up for me at work this week, I set out looking for the magic web.config setting that would enable us to set the Time Zone to Eastern Time.  Unfortunately, this setting does not exist out of the box.  The setting I was thinking of was CultureInfo which lets you set how date/time information is formatted for display (i.e. 05/17/2009 or 05-May-2009, etc.,).  But there is no setting for Time Zone.  

However, writing your own setting is relatively straight forward.  The first thing you’ll need to decide is your reference Time Zone.  GMT/UTC is the real-life model of the reference Time Zone.   So my recommendation is to reference all of your date/time properties and fields using the built-in UTC directives in .NET and SQL Server.  This is optional.  But it will make the rest of what I’m about to suggest easier on you.

To reference data in UTC, stop using server time, and start using UTC time.  Put another way, stop using DateTime.Now in .NET and GETDATE() in SQL Server, and start using DateTime.UtcNow and GETUTCDATE().  Here is a good article by Scott Mitchell that shows some code examples

Next, at some point, either before storing or before displaying your data you will need to convert from your reference Time Zone to your local Time Zone.  This is where the TimeZoneInfo class (new with .NET 3.5) comes in to play:

Here is a quick code example which will convert your local time to Hawaiian Standard Time:

DateTime nowDateTime = DateTime.UtcNow;

DateTime newDateTime = TimeZoneInfo.ConvertTime(


    TimeZoneInfo.FindSystemTimeZoneById("Hawaiian Standard Time"));

Console.WriteLine("Now: {0}", nowDateTime);

Console.WriteLine("Now in Hawaii: {0}", newDateTime);

That’s it.  Not as easy as built-in web.config setting.  But this gives you the tools you need to build your own setting.   

If you have applied good separation of concerns, and application layering object oriented principals in your design, then applying this conversion right before you persist application data should be pretty straight forward.

Friday, May 15, 2009

Starting Apache 2.2 Web Server on Mac Leopard 10.5.6

I bought my iMac with Leopard OS pre-installed.  To start Apache, choose System Preferences from the dock.  Next choose Sharing

And select Web Sharing.  

That's it!  Apache Web Server is now running.  Click here to view your home page

Coming soon:

  • How to harden your iMac Apache Web Server

  • Enabling DDNS

Thursday, May 14, 2009

TriZetto QNXT Hosting

About 18 months ago, my company decided to embark on a core system replacement project.  We sell health insurance, so our core system is used for processing claims, plan and benefit administration, and procuring new members and providers.  There were several reasons why we felt replacing our core system was a good idea.  Our old core system, GE IDX, was not designed for health plan administration.  And GE was about to end-of-life their support for this part of their product line.  Also the landscape of health insurance has been changing in Massachusetts for some time.  And we needed a core system that would help is be more agile when introducing changes, and helping us to stay ahead of an increasingly demanding list of customer needs.

After evaluating a handful of leading vendors in this space, we settled with TriZetto QNXT as our new core system.  We also decided to be the very first Trizetto Hosting customer (If you would like to hear more about how I feel about being “first” see my profile).  It’s for this reason that I decided that writing about QNXT and TriZetto Hosting would be one of the first topics I would cover in this blog.  I intend to write about

  • The good, bad, and ugly of our implementation effort

  • The TriZetto Hosting environment

  • The QNXT SDK

  • How we built our own QNXT Web Portal

  • And last, but certainly not least: custom code delivery, promotion and deployment

If you are a TriZetto customer like me, I hope you can relate to all or some of these topics, and that the things I have to write, will move you to leave a comment