Com(p)fy
Skip Navigation Links >> Community >> Technology Forums >> Compfy >> Web Development >> ADO.NET >> Converting DateTime stored in the database to users’ local time zone
Contents
Skip Navigation Links.
CollapseFolder
Web Development ( Posts: 5 Last: 5/22/2010 )
ExpandFolder
ASP.NET ( Posts: 3 Last: 5/22/2010 )
CollapseFolder
ADO.NET ( Posts: 1 Last: 4/21/2010 )
Text Post
ExpandFolder
Components and Libraries ( Posts: 1 Last: 5/21/2010 )
Folder
ExpandFolder
Compilers ( Posts: 1 Last: 6/20/2010 )
ExpandFolder
 
Converting DateTime stored in the database to users’ local time zone
4/21/2010 11:33:13 AM
Greg
Joined: 4/17/2010
Posts: 6

Users of a website can be located in different time zones. Each one of them should be able to see the time data of a social website in hers or his respective local time. Additionally, we should store the time data in a uniform and independent form so that if the host location changes, all time data remains intact. To achieve this, one can store the time data as UTC time in the database but convert it to the users' local time when displaying it to them. The code snippets below show an example of how it can be done conveniently.

After we restore data from the database into a DataTable, we can check whether this DataTable has any fields in the DateTime format. If found and for each one of them, we can create an additional field in the DateTime format, call it according to some naming scheme, and add to the original table. In the code below, I add "Local" to the original name of the filed. Then, one can go through all DataRow's in the table and convert the value of each original field in the DateTime format into the local time of the authenticated user and store it into the newly created field respectively.

Code
        public static void AddLocalTimeColumns(DataTable table, string newNameFormat)
        {
            Dictionary<string, string> newNames = new Dictionary<string,string>();
           
            foreach (DataColumn col in table.Columns)
            {
                if(col.DataType == typeof(DateTime))
                {
                    string newName = string.Format(newNameFormat, col.ColumnName);
                    newNames.Add(col.ColumnName, newName);
                }
            }

            if (newNames.Count == 0)
                return;

            foreach (KeyValuePair<string,string> pair in newNames)
            {
                table.Columns.Add(pair.Value, typeof(DateTime));
            }

            TimeZoneInfo userTZI = Time.UserTimeZoneInfo;

            foreach (DataRow row in table.Rows)
            {
                foreach (KeyValuePair<string, string> pair in newNames)
                {
                    if(row[pair.Key] != DBNull.Value)
                    {
                        DateTime utcDateTime = (DateTime)row[pair.Key];
                        DateTime localDateTime =
                            TimeZoneInfo.ConvertTimeFromUtc(utcDateTime, userTZI);
                        row[pair.Value] = localDateTime;
                    }
                    else
                    {
                        row[pair.Value] = DBNull.Value;
                    }
                }
            }
        }
 

The authenticated user's local TimeZoneInfo can be stored in the user's profile and retrieved as it is shown in the code below.

Code
        public static TimeZoneInfo UserTimeZoneInfo
        {
            get
            {
                MembershipUser u = Membership.GetUser();
                if (u != null)
                    return GetUserTimeZoneInfo((Guid)u.ProviderUserKey);
                else
                    return TimeZoneInfo.Local;
            }
        }
 

 

Code
        public static TimeZoneInfo GetUserTimeZoneInfo(Guid UserID)
        {
            TimeZoneInfo tmi = null;
            ProfileBase p = User.GetProfile(UserID);
            string sTzi = (string)p.GetPropertyValue("Compfy.TimeZoneInfo");
            if (!string.IsNullOrEmpty(sTzi))
                tmi = TimeZoneInfo.FromSerializedString(sTzi);
            else 
                tmi = TimeZoneInfo.Local;
            return tmi;
        }
 

 

Replies: Page: First Last  Posts/Page:
  Page: First Last  Posts/Page:


Registered users:
7
Users online:
1
Folders total:
18
Posts total:
6
Server timezone (for anonymous users):
(GMT-08:00) Pacific Time (US & Canada)
Powered by Com(p)fy