We are in the midst of doing a total rewrite of our Software, and one of the things that has come up is date and time.  How do we do it, how do we store it, and how do we ensure that we can compare DateTime from one timezone to DateTime in another timezone.  After a lot of research, we settled on using UTC (or UCT depending on your preference).  FxCop will take care of ensuring we use UTC (for the most part).

That solves the problem, or so we thought.  Turns out, when you create a DateTime object either through the constructor, or through DateTime.Parse, its Kind defaults to DateTimeKind.Unspecified.  We need a way to ensure that all DateTime objects are always set to UTC.

What are our options?

Because we are using NHibernate, we have a few options.  The three NHibernate specific ones that immediately come to mind are using an Interceptor, a custom UserType with a SQL datetime column, and a custom UserType with a SQL varchar column; and the non-NHibernate specific one is creating our own DateTime container.  What are the pros and cons of each of these?

NHibernate Interceptor

Pro: very cross-cutting, can touch every object as it comes in and goes out to the database; if there are other data types we need to monkey with, we already have a framework in place.

Con: Very cross-cutting, can be expensive because it's touching every property on every entity as the entities are loaded and persisted

UserType with SQL datetime column

Pro: Only touching the DateTime objects that we want it to

Con: The type has to be specified for every DateTime object in every mapping file; no meta-data along with the date to stamp in the timezone it was created in

UserType with SQL varchar column

Pro: Only touching the DateTime objects that we want it to; can store the timezone and offset along with the date in the db

Con: Same as above UserType; abusing SQL data types; datetimes created at the same (relative) time in two different timezones won't be sorted correctly

Custom DateTime container

Pro: We can do anything we want

Con: Yuck! - I could write a whole paragraph on why this is yucky, but I'll leave that to your imagination

After some thought, I decided on the Interceptor!  Here is the class I came up with (you can also download the complete UtcDateTimeInterceptor class):

 1 class UtcDateTimeInterceptor : IInterceptor
 2 {
 3   public bool OnLoad(object entity, object id, object[] state, string[] propertyNames, IType[] types)
 4   {
 5     ConvertDatabaseDateTimeToUtc(state, types);
 6     return true;
 7   }
 8 
 9   public bool OnSave(object entity, object id, object[] state, string[] propertyNames, IType[] types)
10   {
11     ConvertLocalDateToUtc(state, types);
12     return true;
13   }
14 
15   public bool OnFlushDirty(object entity, object id, object[] currentState, object[] previousState, string[] propertyNames,
16     IType[] types)
17   {
18     ConvertLocalDateToUtc(currentState, types);
19     return true;
20   }
21 
22   private void ConvertLocalDateToUtc(object[] state, IType[] types)
23   {
24     int index = 0;
25     foreach (IType type in types)
26     {
27       if ((type.ReturnedClass == typeof(DateTime)) && state[index] != null && (((DateTime)state[index]).Kind == DateTimeKind.Utc))
28       {
29         state[index] = ((DateTime)state[index]).ToUniversalTime();
30       }
31 
32       ++index;
33     }
34   }
35 
36   private void ConvertDatabaseDateTimeToUtc(object[] state, IType[] types)
37   {
38     int index = 0;
39     foreach (IType type in types)
40     {
41       if ((type.ReturnedClass == typeof(DateTime)) && state[index] != null && (((DateTime)state[index]).Kind == DateTimeKind.Unspecified))
42       {
43         //Create a new date and assume the value stored in the database is Utc
44         DateTime cur = (DateTime)state[index];
45         DateTime result = DateTime.SpecifyKind(cur, DateTimeKind.Local);
46         state[index] = result;
47       }
48 
49       ++index;
50     }
51   }
52 }

Loading The Entities

For the sake of brevity, I'm going to exclude the bits of the Interceptor interface that aren't relevant to my posting.  With that said, the OnLoad event (line 3) gets fired every time an entity is loaded from the database.  We can count on the fact that all dates are stored as UTC in the database because of stuff we'll do later, so we need to convert the DateTime that NHibernate generates (which has a DateTimeKind of Unspecified) to a UTC date (line 36 - 48).  The types array holds the CLR data type of each property in the entity, and each type in the array contains both the internal NHibernate type, and the CLR type. But we don't really care about how NHibernate maps the data types internally, so we are only interested in type.ReturnedClass.

The first thing we need to do is see if the ReturnedClass is of type DateTime (line 41), if its a DateTime, then we need to see if its null, and finally double check that the Kind on the DateTime object coming back from the database is Unspecified.  This last check is a sanity check, in case this behavior changes in the future.

After all these checks are passed, we need to create a new DateTime object from the old one, and set its Kind to Utc (lines 44 and 45).  Thankfully, DateTime has the built-in method SpecifyKind which will take care of building a DateTime of the specified kind for us. And finally replace the existing DateTime object with our new one (line 46).  Shampoo, rinse, repeat for all the DateTime values in the entity.

Persisting The Entities

Now we can move on to the save and update NHibernate events (line 9, and 15 respectively).  In these, we want to make sure the values being persisted to the datastore are UTC, and that no Local times have slipped through the cracks.  If one were so inclined, they could throw an error instead of converting the DateTime to UTC...

The basic code for converting Local DateTime's to Utc (line 22- 34) is much the same as above, but with a few exceptions.  When we do all our checks (line 27), this time we make sure the DateTimeKind is Local before we perform a conversion operation on it.  It is pointless to check if the Kind is Unspecified, because there is no conversion operation we can really perform on it.  On line 29, we can use the built in DateTime method ToUniversalTime() to convert a LocalTime to UTC.

Finishing Up

How do we wire this all up?  When you open a session on your session factory, you can pass in an Interceptor, this is where you would pass in the UtcDateTimeInterceptor.  eg:

ISession openSession = ourSessionFactory.OpenSession(new UtcDateTimeInterceptor());

I want to give credit where credit is due, I got the actual idea of using an Interceptor from a blog post by Ayende where he grappled with DateTime, null, messages and web services.  If anyone using NHibernate doesn't read Ayende Rahiens' blog, I would highly encourage you to.  He is a very, very sharp fellow; and prolific blogger.