Mapping C# DateTime to SQL Server datetimeoffset using Nhibernate

Question

Suppose you have a pretty complex system with a heavy usage of DateTime(more than 1k places with some of helpers and extension relying on the exact type). Suppose you decide that you need to store UTC dates from now on.

My initial idea was substituting all DateTime types with DateTimeOffset but this is not a trivial task since of its heavy usage in different contexts.

The question is could I change just NHiberante mapping without having to change the type used in the rest of the system. Or is that large refactoring the only way?

How would NHibernate know what offset to apply? How would it deal with loading of historic dates? If you're going to embrace UTC, it pretty well has to be throughout the codebase. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Mar 20, 2012 at 19:03
From this description: • A 62-bit number, indicating the number of ticks since 1/1/0001 • A 2-bit enum, indicating the DateTimeKind (Unspecified, Local, or Utc) , it is like you can always make the conversion — Yurii Hohan
– Yurii Hohan, Commented Mar 20, 2012 at 19:06

Anton · Accepted Answer · 2012-03-21 10:32:21Z

1

If you use NHibernate Fluent Automapping, this's my convention for:

    public class UtcDateTimeConvention : IPropertyConvention, IPropertyConventionAcceptance
    {
        public void Apply(IPropertyInstance instance)
        {
            instance.CustomType<UtcDateTimeType>();
        }

        public void Accept(IAcceptanceCriteria<IPropertyInspector> criteria)
        {
            criteria.Expect(x =>
                x.Property.PropertyType.Equals(typeof(DateTime)) ||
                x.Property.PropertyType.Equals(typeof(DateTime?)));
        }
    }

I prefer to persist UtcDateTimeType, and than convert it to local:

        public static DateTime ToUserTimeZone(this DateTime utcDateTime, TimeZoneInfo currentTimeZone)
        {
            if (utcDateTime.Kind != DateTimeKind.Utc)
            {
                throw new ArgumentException("utcDateTime.Kind != DateTimeKind.Utc", "utcDateTime");
            }

            return TimeZoneInfo.ConvertTimeFromUtc(utcDateTime, currentTimeZone);
        }

        public static DateTime? ToUserTimeZone(this DateTime? utcDateTime, TimeZoneInfo currentTimeZone)
        {
            if (utcDateTime.HasValue)
            {
                return ToUserTimeZone(utcDateTime.Value, currentTimeZone);
            }

            return null;
        }

edited Mar 21, 2012 at 10:32

answered Mar 20, 2012 at 20:27

Anton

1,58312 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Yurii Hohan Over a year ago

UtcDateTimeType is not exactly the DateTimeOffset type it seems, may be some cast is possible here

Yurii Hohan Over a year ago

Indeed, if you get persisted value and execute ToLocalTime() on it you would get an exception

Gerke Geurts · Accepted Answer · 2012-03-21 08:09:59Z

0

You can map the DateTime fields with NHibernate UtcDateTime type instead of regular DateTime type if the database always stores UTC values. See http://codebetter.com/jameskovacs/2011/01/26/datetime-support-in-nhibernate/ for an example.

answered Mar 21, 2012 at 8:09

Gerke Geurts

6425 silver badges9 bronze badges

Collectives™ on Stack Overflow

Mapping C# DateTime to SQL Server datetimeoffset using Nhibernate

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related