EWS returns "Custom Time Zone" - how to store it in SQL?

Question

When using the EWS GetUserAvailability() function, I get in the AttendeesAvailability[i] object a WorkingHours object containing a Start, an End, the WeekDays, and a TimeZoneInfo object.

For me, the TimeZoneInfo object always has the name Custom Timezone and a GUID as ID, so I cannot restore the very same time zone using FindSystemTimeZoneById.

The time zone that the user can select for his availability, however, is one of the system time zones, so I really expected to find a system time zone returned here. How can I find - from EWS - which system time zone the user has selected in his Outlook or OWA account settings?

If I can't, what is the appropriate way to store a TimeZoneInfo object in SQL server?

I'm very interested in this. Could you provide some examples? Please give an example value of what is set in OWA, and what you get back from `TimeZoneInfo.ToSerializedString()`. An [MCVE](http://stackoverflow.com/help/mcve) would also be extremely useful. — Matt Johnson-Pint, Jun 08 '16 at 16:24

score 0 · Accepted Answer · answered Jun 08 '16 at 16:54

I have since found out that the problem is the EWS XML format. As can be seen here, it does not contain the full time zone information (e.g. name), only a subset:

  <TimeZone>
    <Bias>480</Bias>
    <StandardTime>
      <Bias>0</Bias>
      <Time>02:00:00</Time>
      <DayOrder>5</DayOrder>
      <Month>10</Month>
      <DayOfWeek>Sunday</DayOfWeek>
    </StandardTime>
    <DaylightTime>
      <Bias>-60</Bias>
      <Time>02:00:00</Time>
      <DayOrder>1</DayOrder>
      <Month>4</Month>
      <DayOfWeek>Sunday</DayOfWeek>
    </DaylightTime>
  </TimeZone>

This subset is then parsed back into a time zone in this EWS Managed API code, with this really useful Guid "Id" (the Id is different every time I call GetUserAvailability, although the timezone is still the same):

return TimeZoneInfo.CreateCustomTimeZone(
    Guid.NewGuid().ToString(),
    -this.bias,
    "Custom time zone",
    "Standard time",
    "Daylight time",
    new TimeZoneInfo.AdjustmentRule[] { adjustmentRule });

So I guess I can expect that in 99% of the cases I will find one or more matching time zones in the list of available system time zones, and I am for now using the extension method:

public static TimeZoneInfo ToSystemTimeZone(this TimeZoneInfo customTimeZone)
{
    var tz = TimeZoneInfo.GetSystemTimeZones().FirstOrDefault(x => x.HasSameRules(customTimeZone));
    if (tz != null) return tz;
    else return customTimeZone;
}

This provides the first system time zone that has the same rules as the custom time zone. It's not a 100% match, since there may be multiple time zones with the same rules, but at least this should keep all the time zone information that is exposed via EWS.

Unfortunately, this will fail to match in any cases where there is more than one adjustment rule, as it doesn't appear that the entire rule definition is exposed. You may just want to compare the *current* rule, or the one associated with the calendar event timestamp (if there is one). The problem then is that you may find multiple different time zones that have the same rule and offset at that point in time, so you'd have no way to disambiguate. — Matt Johnson-Pint, Jun 09 '16 at 21:27
Really, it seems like this may be an impossible task, given the limited amount of detail returned by EWS. There's an implied assumption that a single rule is good enough, but really it's not. — Matt Johnson-Pint, Jun 09 '16 at 21:30
Well, then what did Microsoft think when they defined an API that gives us impossible tasks!? — Alexander, Jun 10 '16 at 06:26

EWS returns "Custom Time Zone" - how to store it in SQL?

1 Answers1