How to elegantly deal with timezones

Question

I have a website that is hosted in a different timezone than the users using the application. In addition to this, users can have a specific timezone. I was wondering how other SO users and applications approach this? The most obvious part is that inside the DB, date/times are stored in UTC. When on the server, all date/times should be dealt with in UTC. However, I see three problems that I'm trying to overcome:

1. Getting the current time in UTC (solved easily with DateTime.UtcNow).

2. Pulling date/times from the database and displaying these to the user. There are potentially lots of calls to print dates on different views. I was thinking of some layer in between the view and the controllers that could solve this issue. Or having a custom extension method on DateTime (see below). The major down side is that at every location of using a datetime in a view, the extension method must be called!

   This would also add difficulty to using something like the JsonResult. You could no longer easily call Json(myEnumerable), it would have to be Json(myEnumerable.Select(transformAllDates)). Maybe AutoMapper could help in this situation?

3. Getting input from the user (Local to UTC). For example, POSTing a form with a date would require converting the date to UTC before. The first thing that comes to mind is creating a custom ModelBinder.

Here's the extensions that I thought of using in the views:

public static class DateTimeExtensions
{
    public static DateTime UtcToLocal(this DateTime source, 
        TimeZoneInfo localTimeZone)
    {
        return TimeZoneInfo.ConvertTimeFromUtc(source, localTimeZone);
    }

    public static DateTime LocalToUtc(this DateTime source, 
        TimeZoneInfo localTimeZone)
    {
        source = DateTime.SpecifyKind(source, DateTimeKind.Unspecified);
        return TimeZoneInfo.ConvertTimeToUtc(source, localTimeZone);
    }
}

I would think that dealing with timezones would be such a common thing considering a lot of applications are now cloud-based where the server's local time could be much different than the expected time zone.

Has this been elegantly solved before? Is there anything that I'm missing? Ideas and thoughts are much appreciated.

EDIT: To clear some confusion I thought add some more details. The issue right now isn't how to store UTC times in the db, it's more about the process of going from UTC->Local and Local->UTC. As @Max Zerbini points out, it's obviously smart to put the UTC->Local code in the view, but is using the DateTimeExtensions really the answer? When getting input from the user, does it make sense to accept dates as the user's local time (since that's what JS would be using) and then use a ModelBinder to transform to UTC? The user's timezone is stored in the DB and is easily retrieved.

Answer 1

Not that this is a recommendation, its more sharing of a paradigm, but the most agressive way I've seen of handling timezone information in a web app (which is not exclusive to ASP.NET MVC) was the following:

• All date times on the server are UTC. That means using, like you said, DateTime.UtcNow.

• Try to trust the client passing dates to the server as little as possible. For example, if you need "now", don't create a date on the client and then pass it to the server. Either create a date in your GET and pass it to the ViewModel or on POST do DateTime.UtcNow.

So far, pretty standard fare, but this is where things get 'interesting'.

• If you have to accept a date from the client, then use javascript to make sure the data that you are posting to the server is in UTC. The client knows what timezone it is in, so it can with reasonable accuracy convert times into UTC.

• When rendering views, they were using the HTML5 <time> element, they would never render datetimes directly in the ViewModel. It was implemented as as HtmlHelper extension, something like Html.Time(Model.when). It would render <time datetime='[utctime]' data-date-format='[datetimeformat]'></time>.

  Then they would use javascript to translate UTC time into the clients local time. The script would find all the <time> elements and use the date-format data property to format the date and populate the contents of the element.

This way they never had to keep track of, store, or manage a clients timezone. The server didn't care what timezone the client was in, nor had to do any timezone translations. It simply spit out UTC and let the client convert that into something that was reasonable. Which is easy from the browser, because it knows what timezone it is in. If the client changed his/her timezone, the web application would automatically update itself. The only thing that they stored were the datetime format string for the locale of the user.

I'm not saying it was the best approach, but it was a different one that I had not seen before. Maybe you'll glean some interesting ideas from it.

Answer 2

After several feedbacks, here is my final solution which I think is clean and simple and covers daylight saving issues.

1 - We handle the conversion at model level. So, in the Model class, we write:

    public class Quote
    {
        ...
        public DateTime DateCreated
        {
            get { return CRM.Global.ToLocalTime(_DateCreated); }
            set { _DateCreated = value.ToUniversalTime(); }
        }
        private DateTime _DateCreated { get; set; }
        ...
    }

2 - In a global helper we make our custom function "ToLocalTime":

    public static DateTime ToLocalTime(DateTime utcDate)
    {
        var localTimeZoneId = "China Standard Time";
        var localTimeZone = TimeZoneInfo.FindSystemTimeZoneById(localTimeZoneId);
        var localTime = TimeZoneInfo.ConvertTimeFromUtc(utcDate, localTimeZone);
        return localTime;
    }

3 - We can improve this further, by saving the timezone id in each User profile so we can retrieve from the user class instead of using constant "China Standard Time":

public class Contact
{
    ...
    public string TimeZone { get; set; }
    ...
}

4 - Here we can get the list of timezone to show to user to select from a dropdownbox:

public class ListHelper
{
    public IEnumerable<SelectListItem> GetTimeZoneList()
    {
        var list = from tz in TimeZoneInfo.GetSystemTimeZones()
                   select new SelectListItem { Value = tz.Id, Text = tz.DisplayName };

        return list;
    }
}

So, now at 9:25 AM in China, Website hosted in USA, date saved in UTC at database, here is the final result:

5/9/2013 6:25:58 PM (Server - in USA) 
5/10/2013 1:25:58 AM (Database - Converted UTC)
5/10/2013 9:25:58 AM (Local - in China)

EDIT

Thanks to Matt Johnson for pointing out the weak parts of original solution, and sorry for deleting original post, but got issues getting right code display format... turned out the editor has problems mixing "bullets" with "pre code", so I removed the bulles and it was ok.

Answer 3

In the events section on sf4answers, users enter an address for an event, as well as a start date and an optional end date. These times are translated into a datetimeoffset in SQL server that accounts for the offset from UTC.

This is the same problem you are facing (although you are taking a different approach to it, in that you are using DateTime.UtcNow); you have a location and you need to translate a time from one timezone to another.

There are two main things I did which worked for me. First, use DateTimeOffset structure, always. It accounts for offset from UTC and if you can get that information from your client, it makes your life a little easier.

Second, when performing the translations, assuming you know the location/time zone that the client is in, you can use the public info time zone database to translate a time from UTC to another time zone (or triangulate, if you will, between two time zones). The great thing about the tz database (sometimes referred to as the Olson database) is that it accounts for the changes in time zones throughout history; getting an offset is a function of the date that you want to get the offset on (just look at the Energy Policy Act of 2005 which changed the dates when daylight savings time goes into effect in the US).

With the database in hand, you can use the ZoneInfo (tz database / Olson database) .NET API. Note that there isn't a binary distribution, you'll have to download the latest version and compile it yourself.

At the time of this writing, it currently parses all of the files in the latest data distribution (I actually ran it against the ftp://elsie.nci.nih.gov/pub/tzdata2011k.tar.gz file on September 25, 2011; in March 2017, you'd get it via https://iana.org/time-zones or from ftp://fpt.iana.org/tz/releases/tzdata2017a.tar.gz).

So on sf4answers, after getting the address, it is geocoded into a latitude/longitude combination and then sent to a third-party web service to get a timezone which corresponds to an entry in the tz database. From there, the start and end times are converted into DateTimeOffset instances with the proper UTC offset and then stored in the database.

As for dealing with it on SO and websites, it depends on the audience and what you are trying to display. If you notice, most social websites (and SO, and the events section on sf4answers) display events in relative time, or, if an absolute value is used, it's usually UTC.

However, if your audience expects local times, then using DateTimeOffset along with an extension method that takes the time zone to convert to would be just fine; the SQL data type datetimeoffset would translate to the .NET DateTimeOffset which you can then get the universal time for using the GetUniversalTime method. From there, you simply use the methods on the ZoneInfo class to convert from UTC to local time (you'll have to do a little work to get it into a DateTimeOffset, but it's simple enough to do).

Where to do the transformation? That's a cost you are going to have to pay somewhere, and there's no "best" way. I'd opt for the view though, with the timezone offset as part of the view model presented to the view. That way, if the requirements for the view change, you don't have to change your view model to accommodate the change. Your JsonResult would simply contain a model with the IEnumerable<T> and the offset.

On the input side, using a model binder? I'd say absolutely no way. You can't guarantee that all the dates (now or in the future) will have to be transformed in this way, it should be an explicit function of your controller to perform this action. Again, if the requirements change, you don't have to tweak one or many ModelBinder instances to adjust your business logic; and it is business logic, which means it should be in the controller.

Answer 4

This is just my opinion, I think that MVC application should separate well data presentation problem from data model management. A database can store data in local server time but it's a duty of the presentation layer to render datetime using local user timezone. This seems to me the same problem as I18N and number format for different countries. In your case, your application should detect the Culture and timezone of the user and change the View showing different text, number and datime presentation, but the stored data can have the same format.

Answer 5

For output, create an display/editor template like this

@inherits System.Web.Mvc.WebViewPage<System.DateTime>
@Html.Label(Model.ToLocalTime().ToLongTimeString()))

You can bind them based on attributes on your model if you want only certain models to use those templates.

See here and here for more details on creating custom editor templates.

Alternatively, since you want it to work for both input and output, I would suggest extending a control or even creating your own. That way you can intercept both the input and outputs and convert the text/value as needed.

This link will hopefully push you in the right direction if you want to go down that path.

Either way, if you want an elegant solution, its going to be a bit of work. On the bright side, once you have done it once you can keep it in your code library for future use!

Answer 6

I wanted to store dates as DateTimeOffset so that I could maintain the Time Zone Offset of the user that writes to the database. However, I wanted to only use DateTime inside of the application itself.

So, local time zone in, local time zone out. Regardless of who/where/when the user is looking at the data, it will be a local time to the observer - and changes are stored as UTC + local offset.

Here is how I achieved this.

1. Firstly, I needed to get the web client's local time zone offset and store this value on the web server:

// Sets a session variable for local time offset from UTC
function SetTimeZone() {
    var now = new Date();
    var offset = now.getTimezoneOffset() / 60;
    var sign = offset > 0 ? "-" : "+";
    var offset = "0" + offset;
    offset = sign + offset + ":00";
    $.ajax({
        type: "post",
        url: prefixWithSitePathRoot("/Home/SetTimeZone"),
        data: { OffSet: offset },
        datatype: "json",
        traditional: true,
        success: function (data) {
            var data = data;
        },
        error: function (XMLHttpRequest, textStatus, errorThrown) {
            alert("SetTimeZone failed");
        }
    });
}

The format is intended to match that of the SQL Server DateTimeOffset type.

SetTimeZone - just sets the value of Session variable. When the user logs on, I incorporate this value into the User profile cache.

2. As a user submits a change to the database, I filter the DateTime value through a utility class:

cmdADO.Parameters.AddWithValue("@AwardDate", (object)Utility.ConvertLocal2UTC(theContract.AwardDate, theContract.TimeOffset) ?? DBNull.Value);

The Method:

public static DateTimeOffset? ConvertLocal2UTC(DateTime? theDateTime, string TimeZoneOffset)
{
    DateTimeOffset? DtOffset = null;
    if (null != theDateTime)
    {
        TimeSpan AmountOfTime;
        TimeSpan.TryParse(TimeZoneOffset, out AmountOfTime);
        DateTime datetime = Convert.ToDateTime(theDateTime);
        DateTime datetimeUTC = datetime.ToUniversalTime();

        DtOffset = new DateTimeOffset(datetimeUTC.Ticks, AmountOfTime);
    }
    return DtOffset;
}

3. When I and reading in the date from the SQL Server, I am doing this:

theContract.AwardDate = theRow.IsNull("AwardDate") ? new Nullable<DateTime>() : DateTimeOffset.Parse(Convert.ToString(theRow["AwardDate"])).DateTime;

In the controller, I modify the datetime to match the local time of the observer. (I am sure someone can do better with an extension or something):

theContract.AwardDate = Utilities.ConvertUTC2Local(theContract.AwardDate, CachedCurrentUser.TimeZoneOffset);

The method:

public static DateTime? ConvertUTC2Local(DateTime? theDateTime, string TimeZoneOffset)
{
    if (null != theDateTime)
    {
        TimeSpan AmountOfTime;
        TimeSpan.TryParse(TimeZoneOffset, out AmountOfTime);
        DateTime datetime = Convert.ToDateTime(theDateTime);
        datetime = datetime.Add(AmountOfTime);
        theDateTime = new DateTime(datetime.Ticks, DateTimeKind.Utc);
    }
    return theDateTime;
}

In the View, I am just displaying/editing/validating a DateTime.

I hope this helps someone who has a similar need.

Answer 7

This is probably a sledgehammer to crack a nut but you could inject a layer between the UI and Business layers which transparently converts datetimes to the local time on returned object graphs, and to UTC on input datetime parameters.

I imagine this could be achieved using PostSharp or some inversion of control container.

Personally, I'd just go with explicitly converting your datetimes in the UI...