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?

What version of SQL Server? SQL Server 2008 introduced [`DATETIMEOFFSET`](http://msdn.microsoft.com/en-us/library/bb630289(v=sql.100).aspx). I have yet to find a use for it (I would rather store the user's time zone once, store the date as UTC, and adjust per user), but others claim it can be used for this purpose. — Aaron Bertrand, Jul 20 '12 at 13:36
There is one down side to storing the date time as UTC in the db. How do you account for DST on the DB side? There is no way to convert a DateTime or DateTime2 to the users local time with respect to time zones and DST. If you pass an offset, then you could still be off due to one day being in DST and one not. Example 3/10/2012 2:00:00 EST is 3/09/2012 20:00:00 UTC but 3/13/2012 2:00:00 EST is 3/12/2012 19:00:00 UTC. Basically an offset of -4 and -5 respectively. So which offset do you pass into your SP -4 or -5 to convert the UTC to local? Either way one is wrong. — aBetterGamer, Oct 18 '12 at 14:38

score 39 · Accepted Answer · answered Jul 20 '12 at 13:34

39

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 :)

answered Jul 20 '12 at 13:34

Jon Skeet

1,421,763
867
9,128
9,194

Can you shed some more light on TZDB and why you prefer it? – M Afifi Jul 20 '12 at 13:41
@Jon Skeet - Just to clarify, it looks like the Id property is also a string (like "Eastern Standard Time") . . that is the field you are suggesting i store ? – leora Jul 20 '12 at 13:48
@leora - He linked to the documentation, so yes, that is the field you should use. – Security Hound Jul 20 '12 at 15:28
3

@Ramhound - I saw that, I was just surprised that the Id was a descriptive string (as opposed to some number) – leora Jul 20 '12 at 16:28
Is there a difference between Mono and .Net for this? – Richard B Jul 31 '12 at 02:39
@RichardB: In my experience Mono uses different time zone IDs on different platforms and in different versions. But even so, you should still be able to find a time zone by its ID. – Jon Skeet Jul 31 '12 at 05:54
@JonSkeet Thx. Good enough. Just concerned because we're trying to do cross-platform dev on a project I'm on, and just know I need to run unit testing on both now to make sure they work as expected. – Richard B Jul 31 '12 at 16:04
@RichardB: I'd expect each to work individually - but don't expect the same IDs to necessarily work on both platforms, if you see what I mean. – Jon Skeet Jul 31 '12 at 16:07
@JonSkeet Mmm... ok Will pick up tonight with this. That may or may not cause an issue. Will have to experiment. – Richard B Jul 31 '12 at 16:09
@ChristianRios: `Id` is an instance property of `TimeZoneInfo`. In other words, I'm recommending that you use the `Id` property obtained from whatever `TimeZoneInfo` you're interested in – Jon Skeet Oct 16 '21 at 20:12

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

1 Answers1

Linked