LINQ(-to-SQL), DateTimeOffset, and Time Zones

Filed in Software Developement Leave a comment

Today I was requested to produce a report on some timestamped data, but with the timestamps in the time zone where the data was collected.  Now long ago I learned the valuable lesson of using DateTimeOffset to store data and there is no change with this scenario, but something I had never actually tried before was getting LINQ-to-SQL (LINQPad) to emit timestamp data in a time zone other than my current system time zone.  This is really more of a general .NET solution to the same problem as i don’t believe LINQ-to-SQL actually converts any timestamp data on the data side (nor should it, we only want the DateTimeOffset values).

The crucial type in this case is TimeZoneInfo. First you need to figure out which time zone you want as your target (destination) time zone.  Use TimeZoneInfo.GetSystemTimeZones() to figure that one out, and make note of the Id property.  Now you need to create the proper TimeZoneInfo instance, you could use the results of that list, but more simply you can create the instance from the Id property using TimeZoneInfo.FindSystemTimeZoneById("Id") or for a real world example: TimeZoneInfo.FindSystemTimeZoneById("Newfoundland Standard Time"). The last part is to use TimeZoneInfo.ConvertTime(DateTimeOffset, TimeZoneInfo) to offset your DateTimeOffset to the proper amount.

Here is a full example:

TimeStampedData.Select(x => new { x.Name, Timestamp = TimeZoneInfo.ConvertTime(x.Timestamp, TimeZoneInfo.FindSystemTimeZoneById("Newfoundland Standard Time")) })

Nice and simple, but a little bit of a pain to actually get the right time zone.  Ideally, .NET would have static accessors to all the common standard time zones (as TimeZoneInfo static instances), but otherwise this is a reasonably easy solution to achieve.

, , , ,