What is the best way to store timezone information in my DB?

Question

I have a asp.net-mvc web site that i took over and there is a page page where people enter information and times (including local timezone). The database is persisting a start time, an end time and a timezone as it feels a bit flaky to me. here is the code to get the list to choose from:

  static private IEnumerable<SelectListItem> GetTimeZones(string selected)
    {
        var timeZoneNames = TimeZoneInfo.GetSystemTimeZones()
            .Where(tz => tz.DisplayName.Contains("London")
                         || tz.DisplayName.Contains("Hong Kong")
                         || tz.DisplayName.Contains("Mumbai")
                         || tz.DisplayName.Contains("Eastern Time"))
            .ConvertAll(tz => tz.DisplayName).ToList();

        var items = new List<SelectListItem>();
        foreach (var item in timeZoneNames)
        {
            var slItem = new SelectListItem();
            slItem.Text = item;
            slItem.Value = item;
            slItem.Selected = item == selected;
            items.Add(slItem);
        }

        return items;
    }

So its simply storing the full string that is returned from TimeZoneInfo.GetSystemTimeZones()

Are there any flaws with this approach? I am a bit concerned reading here that this comes locally from the machine as what if different machines have different settings. Also, trying to figure out if everything is listed as UTC or GMT, etc . . Any better suggestions? For example, should i do the conversion to UTC on the website and normalize all of the times in the database?

Answer 1

You should store the ID returned by TimeZoneInfo.Id. That's the identifier - the string which is meant to identify the time zone. Using the display name is a really bad idea, as that can vary by culture and is less likely to be stable.

You can then use TimeZoneInfo.FindSystemTimeZoneById to retrieve the zone from the ID.

Admittedly I prefer TZDB for time zone handling, but that's a different matter :)