3

My question is two-fold.

1) I am coding a forum and I'm having trouble figuring out how to store time zones for the users of the forum. They will be able to set their time zone and have all dates on the forum modified accordingly. Do I have to create a DB table with timezone names and the number to adjust the server time by? Does .NET have time zone support built in somewhere?

2) Once I've figured out how to store the user's time zone and then modify a DateTime object to the right time, I then need an easy way to pass this modified date to the view in MVC. For instance, I have the following code:

List<Topic> topics = board.Topics.OrderByDescending(x => x.Replies.Any() 
                                                    ? x.Replies.OrderBy(y => y.PostedDate).Last().PostedDate 
                                                    : x.PostedDate).ToList();

This topics object is passed to the view as part of a view model object. The view loops through Model.Topics and displays the list of topics. The problem is that I don't want to do the time zone modifications in the view because I think that is too much responsibility for the view. Is there a way to modify the topic date within the LINQ query?

Thanks in advance!

CatDadCode
  • 58,507
  • 61
  • 212
  • 318

2 Answers2

7

You can get a list of timezones System.TimeZoneInfo.

var timeZones = System.TimeZoneInfo.GetSystemTimeZones();

foreach ( var timeZone in timeZones )
{
  Console.WriteLine( "{0} - {1}", timeZone.Id,  timeZone.DisplayName );
}

You can use that list to populate a dropdown list on the users profile page. The selected value should be stored with each user's profile data.

You can then use TimeZoneInfo.ConvertTime to convert any date time to the users time zone. Assuming you know which time zone it was created it.

var now = DateTime.Now;
Console.WriteLine( now );
Console.WriteLine( System.TimeZoneInfo.ConvertTime( now, TimeZoneInfo.Local, TimeZoneInfo.FindSystemTimeZoneById( "China Standard Time" ) ) );

As far as where to do this conversion goes you can do it in your controller rather then the view. Your best bet would be to create a view model on top of of Topic where you do the conversion.

Other wise create a helper function to do the conversion which is accessible from your views and use it appropriately. Personally I wouldn't be afraid of doing this in the view.

Be weary of attempting to do the conversion in the database, you'll severely limit the ability to perform object caching on the data returned from the database.

Also, Consider converting all dates to UTC time before inserting them into the database. This will make sorting correct (in regards to daylight savings) and also limit any issues that may arise if the hosting environment was moved time zones or hosted across time zones.

Aaron Carlson
  • 5,522
  • 4
  • 31
  • 35
0

I understand this is an answered question. I am adding my few cents also for future reference. This is also another approach.

My Complete Solution as follows. All the time is stored as UTC in database. You can choose another timezone as default. But then calculation get complicated. And if you decided to move your host to another country, it would be issue.

Database

First, Need to store user selected timezone in some table that holds user information. Basically we are going to store TimeZoneInfo.Id (String value). This is easy to create TimeZoneInfo class again.

Next, Create a User defined function and set return as datetime. This will be used in whole DB where ever you need server time. Code as shown,

Create FUNCTION [dbo].[fnGetDateTime] ()

RETURNS datetime

AS

BEGIN

RETURN GETUTCDATE()


END

This will return UTC time. We can use GETUTCDATE() method straightaway everywhere in the db. But using a UDF gives flexibility on maintenance.

Code

we can use method extendability in Date-time Class. Since this solution is based on asp.net, it is tricky to calculate time. Problem is, there are 3 parties involved. DB Server, Web Server and User. Each can be in different time zone. But we need to solely relay on User Selected time zone and UTC time.

I have exetened DateTime Strucute with 2 addtional methods.

Namespace Extensions
    Public Module ModDateTimeExtensions

        <System.Runtime.CompilerServices.Extension()> _
        Public Function GetUserTimeFromUTC(ByVal dtUtcTime As DateTime, ByVal id As String) As DateTime
            Return TimeZoneInfo.ConvertTimeFromUtc(dtUtcTime, TimeZoneInfo.FindSystemTimeZoneById(id))
        End Function

        <System.Runtime.CompilerServices.Extension()> _
        Public Function SetUserTimeToUTC(ByVal dtUserTime As DateTime, ByVal id As String) As DateTime
            Return TimeZoneInfo.ConvertTime(dtUserTime, TimeZoneInfo.FindSystemTimeZoneById(id), TimeZoneInfo.Utc)
        End Function

    End Module
End Namespace

Here important thing you shouldn't relay on web-server time zone for calculation.

Then basically you can do the conversion as below. Assuming user time zone set to "Pacific Standard Time". This needs to be pulled from DB during user login.

Dim dt as DateTime = FunctionToGetUTCTimeFromDB()

dt = dt.GetUserTimeFromUTC("Pacific Standard Time")

If you want to save user time to UTC then call,

Dim dt as DateTime = GetUserSelectedTimeFromUI()

dt = dt.SetUserTimeToUTC("Pacific Standard Time")

This gives Following flexibility,

Less Coding and Less Change if it needs to be implemented in existing system.

Easy maintenance.

In future if you want to implement User selectable date time format, can follow same way with small change.

Jeyara
  • 2,198
  • 1
  • 23
  • 26