How to probably handle UTC times stored in DB with LINQ to SQL?

Question

Hi

I am wondering how to probably handling UTC DateTime's in LINQ to SQL. In my case, the dates stored in the database is in UTC, but when it is read out via LINQ to SQL, DateTime.Kind is DateTimeKind.Unspecified.

The problem asserts itself when I need to display my DateTimes on the front end, where the user is not in UTC time.

Using partial classes I have tried extending my entity classes, but I am unsure if this is the right approach, and if there are any pitfalls with this solution.

Code Block

    public partial class WorkItem
    {
        public DateTime CreatedOnLocal
        {
            get
            {
                return CreatedOn.ToLocalTime();
            }
            set
            {
                CreatedOn = value.ToUniversalTime();
            }
        }
    }

This particular solution makes databinding and updating easy, since I just bind to CreatedOnLocal instead of CreatedOn and also updates through CreatedOnLocal, thus preventing accidental insertion of none UTC times. This way the user can work in his or her local time, and I will have a consistant times in the database.

I also looked in to extension methods (link to blog post where I talk about that solution), but I was never really satisfied with that  approach either.

Anyways, this is starting to become a long post, hope somebody out there has some feedback on this, best regards, Egil.

Answer 1

I posted a related question a while back and havent't gotten any reponse yet, see "DateTime columns and DateTime.Kind (LinqToSql)"

 

What I've done for now is to alter the Storeage to NOT set the private field, but to use the property setter, and altered that as follows:

 

Code Block

public System.DateTime UpdatedUTC

{

get { return this._UpdatedUTC; }

set

{

if(value.Kind == DateTimeKind.Unspecified)

value = value.ToLocalTime().ToUniversalTime();

if((this._UpdatedUTC != value))

{

this._UpdatedUTC = value;

}

}

}

 

 

 

Answer 2

Hi Patrick

I dont think it is a very comon problem, or rather, it probably is, but not that many is aware of the issues around the DateTime type.

I read something on a blog a while ago about the new DateTimeOffset type, which is suppose to handle the hole timezone issue better. When SQL Server 2008 gets released, it will also support this type, making our problems go away, at least when storing dates and times in a database.

On question: what is the reasoning behind this?

if(value.Kind == DateTimeKind.Unspecified)
    value = value.ToLocalTime().ToUniversalTime();

Are you assuming your value is a local time, and then first converting it to local to set its Kind property, and then to UTC time?

Regards, Egil.

Answer 3

The ToLocalTime().ToUniversalTime() has to do with how those methods work on unspecified kinds. I assume that the time is UTC, but to get the time specified correctly, first it needs to be converted to local time, then once it is a local time it can be converted back to universal time, but now the kind is correct. The reverse is also true, if I assume unknown to be local and I want a kind of local, then I need to first convert it to universal, then convert it back to local.