DateTime.Now and Culture/Timezone specific

Question

Our application was designed to handle user from different Geographic location.

We are unable to detect what is the current end user local time and time zone operate on it. They select different culture like sv-se, en-us, ta-In even they access from Europe/London timezone..

We hosted it in a hosting server in US, application users are from Norway/Denmark/Sweden/UK/USA/India

The problem is we used DateTime.Now to store the record created/updated date, etc.

Since the Server runs in USA all user data are saved as US time :(

After researching in SO, we decided to stored all history dates in DB as DateTime.UtcNow

PROBLEM:

There is a record created on 29 Dec 2013, 3:15 P.M Swedish time.

 public ActionResult Save(BookingViewModel model)
    {
        Booking booking = new Booking();
        booking.BookingDateTime = model.BookingDateTime; //10 Jan 2014 2:00 P.M
        booking.Name = model.Name;
        booking.CurrentUserId = (User)Session["currentUser"].UserId;
        //USA Server runs in Pacific Time Zone, UTC-08:00
        booking.CreatedDateTime = DateTime.UtcNow; //29 Dec 2013, 6:15 A.M
        BookingRepository.Save(booking);
        return View("Index");
    }

We want to show the same history time to the user who logged in in India/Sweden/USA.

As of now we are using current culture user logged in and choose the timezone from a config file and using for conversion with TimeZoneInfo class

<appSettings>
    <add key="sv-se" value="W. Europe Standard Time" />
    <add key="ta-IN" value="India Standard Time" />
</appSettings>

    private DateTime ConvertUTCBasedOnCulture(DateTime utcTime)
    {
        //utcTime is 29 Dec 2013, 6:15 A.M
        string TimezoneId =                  
                System.Configuration.ConfigurationManager.AppSettings
                [System.Threading.Thread.CurrentThread.CurrentCulture.Name];
        // if the user changes culture from sv-se to ta-IN, different date is shown
        TimeZoneInfo tZone = TimeZoneInfo.FindSystemTimeZoneById(TimezoneId);

        return TimeZoneInfo.ConvertTimeFromUtc(utcTime, tZone);
    }
    public ActionResult ViewHistory()
    {
        List<Booking> bookings = new List<Booking>();
        bookings=BookingRepository.GetBookingHistory();
        List<BookingViewModel> viewModel = new List<BookingViewModel>();
        foreach (Booking b in bookings)
        {
            BookingViewModel model = new BookingViewModel();
            model.CreatedTime = ConvertUTCBasedOnCulture(b.CreatedDateTime);
            viewModel.Add(model);
        }
        return View(viewModel);
    }

View Code

   @Model.CreatedTime.ToString("dd-MMM-yyyy - HH':'mm")

NOTE: The user can change the culture/language before they login. Its a localization based application, running in US server.

I have seen NODATIME, but I could not understand how it can help with multi culture web application hosted in different location.

Question

How can I show a same record creation date 29 Dec 2013, 3:15 P.M for the users logged in INDIA/USA/Anywhere`?

As of now my logic in ConvertUTCBasedOnCulture is based user logged in culture. This should be irrespective of culture, since user can login using any culture from India/USA

DATABASE COLUMN

CreatedTime: SMALLDATETIME

UPDATE: ATTEMPTED SOLUTION:

DATABASE COLUMN TYPE: DATETIMEOFFSET

UI

Finally I am sending the current user's local time using the below Momento.js code in each request

$.ajaxSetup({
    beforeSend: function (jqXHR, settings) {
        try {
      //moment.format gives current user date like 2014-01-04T18:27:59+01:00
            jqXHR.setRequestHeader('BrowserLocalTime', moment().format());
        }
        catch (e) {
        }
    }
});

APPLICATION

public static DateTimeOffset GetCurrentUserLocalTime()
{
    try
    {
      return 
      DateTimeOffset.Parse(HttpContext.Current.Request.Headers["BrowserLocalTime"]);
    }
    catch
    {
        return DateTimeOffset.Now;
    }
}

then called in

 model.AddedDateTime = WebAppHelper.GetCurrentUserLocalTime();

In View

@Model.AddedDateTime.Value.LocalDateTime.ToString("dd-MMM-yyyy - HH':'mm")

In view it shows the local time to user, however I want to see like dd-MMM-yyyy CET/PST (2 hours ago).

This 2 hours ago should calculate from end user's local time. Exactly same as stack overflow question created/edited time with Timezone display and local user calculation.

Example: answered Jan 25 '13 at 17:49 CST (6 hours/days/month ago) So the other viewing from USA/INDIA user can really understand this record was created exactly 6 hours from INDIA/USA current time

Almost I think I achieved everything, except the display format & calculation. How can i do this?

Solution 1

It sounds like you need to store a DateTimeOffset instead of a DateTime. You could just store the local DateTime to the user creating the value, but that means you can't perform any ordering operations etc. You can't just use DateTime.UtcNow, as that won't store anything to indicate the local date/time of the user when the record was created.

Alternatively, you could store an instant in time along with the user's time zone - that's harder to achieve, but would give you more information as then you'd be able to say things like "What is the user's local time one hour later?"

The hosting of the server should be irrelevant - you should never use the server's time zone. However, you will need to know the appropriate UTC offset (or time zone) for the user. This cannot be done based on just the culture - you'll want to use Javascript on the user's machine to determine the UTC offset at the time you're interested in (not necessarily "now").

Once you've worked out how to store the value, retrieving it is simple - if you've already stored the UTC instant and an offset, you just apply that offset and you'll get back to the original user's local time. You haven't said how you're converting values to text, but it should just drop out simply - just format the value, and you should get the original local time.

If you decide to use Noda Time, you'd just use OffsetDateTime instead of DateTimeOffset.

Solution 2

Standard approach is to always store any time data as UTC if particular moment in time is important. That time is not impacted by time zone changes and cultures.

Most common approach to showing time with time zone is to store time as UTC and convert to current user's culture/time zone combination when you display the value. This approach only requires single date time filed in the storage.

Note that for Web cases (like ASP.Net) you may need to figure out user's culture/time zone first and send it to server (as this information is not necessary available on GET requests) or do formatting of time in the browser.

Depending what "show the same history time" you may need to store additional information like current culture and/or current offset. If you need to show time exactly as original user seen it you may also save string representation (because formats/translations can change later and value will look different, also it is unusual).

Note: culture and time zone are not tied together, so you'll need to decide how you need to handle cases like IN-IN culture in US PST time zone.

Solution 3

I'm a little confused by the phrasing of your question, but it appears that you would like to determine the time zone of your user.

• Have you tried asking them? Many applications have the user pick their time zone in user settings.

• You could pick from a drop-down list, or a pair of lists (country, then time zone within the country), or from a map-based time zone picker control.

• You could take a guess and use that as the default unless your user changes it.

If you go down that route, you will need to be able to use IANA/Olson time zones, which is where Noda Time comes into play. You can access them from DateTimeZoneProviders.Tzdb.

The hosting location is irrelevant if you are using UTC. That's a good thing.

Also, if you're using Noda Time, then you probably should use SystemClock.Instance.Now instead of DateTime.UtcNow.

See also here and here.

Also - an alternative solution would be just to pass the UTC time to the browser and load it into a JavaScript Date object. The browser can convert that to the user's local time. You could also use a library like moment.js to make this easier.

---

Update

Regarding your approach of mapping culture codes to time zones:

<appSettings>
    <add key="sv-se" value="W. Europe Standard Time" />
    <add key="ta-IN" value="India Standard Time" />
</appSettings>

That will not work, for several reasons:

• Many people use a different culture setting on their computer than the area that they are physically in. For example, I might be an a US-English speaker living in Germany, my culture code is likely still en-US, not de-DE.

• A culture code containing a country is used to distinguish between dialects of a language. When you see es-MX, that means "Spanish, as spoken in Mexico". It does not mean that the user is actually in Mexico. It just means that user speaks that dialect of Spanish, as compared to es-ES which means "Spanish, as spoken in Spain".

• Even if the country portion of the culture code could be reliable, there are many countries that have multiple time zones! For example, what would you put in your mapping list for en-US? You can't just assume that we are all on Eastern Standard Time.

Now, I've explained why your current approach won't work, I strongly suggest you take my original advice. Very simply:

1. Determine the time zone of the user, preferably by asking them, perhaps with some assistance by one of the utilities I linked to above.

2. You're storing UTC, so just convert to that time zone for display.

   Using Microsoft Time Zones

   TimeZoneInfo tz = TimeZoneInfo.FindSystemTimeZoneById("W. Europe Standard Time");
   DateTime localDatetime = TimeZoneInfo.ConvertTimeFromUtc(yourUTCDateTime, tz);
   

   Using IANA Time Zones and Noda Time

   DateTimeZone tz = DateTimeZoneProviders.Tzdb["Europe/Stockholm"];
   Instant theInstant = Instant.FromDateTimeUtc(yourUTCDateTime);
   LocalDateTime localDateTime = theInstant.InZone(tz);
   

Solution 4

We faced a similar problem with an application I worked on recently. During development every one was in the same time zone and the issue wasn't noticed. And in any case there was a lot of legacy code that would have been a pain to change not to mention converting all the date time info that was already in the DB. So changing to DateTimeOffset was not an option. But we managed to get it all consistent by converting from server time to user time on the way out and converting from user time to server time on the way in. It was also important to do this with any date time comparisons that were a boundary. So if a user expected some thing to expire midnight their time then we would convert that time to server time and do all comparisons in server time. This sounds like a lot of work but was much less work then converting the entire application and DB to use DateTimeOffsets.

Hear is a thread that looks like has some good solutions to the time zone issue.

Determine a User's Timezone

View more solutions