locked
Converting UTC to local time RRS feed

  • Question

  • I have an application that is designed to schedule certain activities to run at certain times on various servers located around the country.  Actually, the app is similar in functinality to Windows Scheduld Tasks.  Anyway, one requirement is for the app to be able to recognize and convert datetimes to the local server's timezone. 

    I am storing the data in a SQL server table. 

    I thought about storing all the datetime data as UTC, and then when the particular server evaluates the process start time, it will convert it to the server's local time.  This part works fine.

    My issue is that I don't want the user to view the datetime values as the UTC time, but rather the local time.  (I am using a DataGridView).  I am using a typed dataset as the datasource for the DataGridView, but am unable to convert the UTC time to local time.

    I have set the DataColumn.DateTimeMode = System.Data.DataSetDateTime.Utc, but this does not effect the display.

    Any Ideas?

    Wednesday, March 14, 2007 3:50 PM

Answers

  • Well, if you want to have the DataTable in Utc, ie. the values will always be converted to Utc, then use the Utc settings like you do. But this means, as you already discovered, that any DateTime that you add to that column will be converted to Utc.
    The same is true for Local, if you use the Local setting anything will be converted to local time. As the DateTime carries the information about the time zone this conversion will always work, but then you have the data saved also as local time.

    But just as possible solution/workaround for you:

    You could just add additional columns to the DataTable and suppress the Utc column in the DataGridView. This could also introduce a new feature as you can add essentially two columns (or more):

    1) UTC column
    2) column local time of the event
    3) column with local time

    Example:

    DataTable dt = new DataTable();
    dt.Columns.Add(
    new DataColumn("Event", typeof(string)));
    dt.Columns.Add(
    new DataColumn("Utc", typeof(DateTime)));
    dt.Columns.Add(
    new DataColumn("Los Angeles", typeof(DateTime)));
    dt.Columns.Add(
    new DataColumn("New York", typeof(DateTime)));
    dt.Columns[1].DateTimeMode =
    DataSetDateTime.Utc;
    dt.Columns[2].ExtendedProperties.Add(
    "TZ", -7);
    dt.Columns[2].ExtendedProperties.Add(
    "TZ", -4);
    dt.RowChanged +=
    new DataRowChangeEventHandler(dt_RowChanged);

     

    static bool recalculatingTZ = false;
    void dt_RowChanged(object sender, DataRowChangeEventArgs e)
    {
     
    if (recalculatingTZ) return;
     
    DateTime utcTime = (DateTime)e.Row["Utc"];
     
    foreach (DataColumn column in e.Row.Table.Columns)
       
    if (column.ExtendedProperties.ContainsKey("TZ"))
        {
          recalculatingTZ =
    true;
          e.Row[column] = utcTime.AddHours((
    int)column.ExtendedProperties["TZ"]);
          recalculatingTZ =
    false;
        }
    }

    Wednesday, March 14, 2007 6:12 PM
  • Am I missing something since doesn't DateTime support such conversions?

    DateTime dt = DateTime.Now.ToUniversalTime();

    DateTime dt2 = dt.ToLocalTime();

    MessageBox.Show(dt2.ToLongDateString() + " " + dt2.ToLongTimeString());

    Thursday, March 15, 2007 7:09 PM

All replies

  • Well, if you want to have the DataTable in Utc, ie. the values will always be converted to Utc, then use the Utc settings like you do. But this means, as you already discovered, that any DateTime that you add to that column will be converted to Utc.
    The same is true for Local, if you use the Local setting anything will be converted to local time. As the DateTime carries the information about the time zone this conversion will always work, but then you have the data saved also as local time.

    But just as possible solution/workaround for you:

    You could just add additional columns to the DataTable and suppress the Utc column in the DataGridView. This could also introduce a new feature as you can add essentially two columns (or more):

    1) UTC column
    2) column local time of the event
    3) column with local time

    Example:

    DataTable dt = new DataTable();
    dt.Columns.Add(
    new DataColumn("Event", typeof(string)));
    dt.Columns.Add(
    new DataColumn("Utc", typeof(DateTime)));
    dt.Columns.Add(
    new DataColumn("Los Angeles", typeof(DateTime)));
    dt.Columns.Add(
    new DataColumn("New York", typeof(DateTime)));
    dt.Columns[1].DateTimeMode =
    DataSetDateTime.Utc;
    dt.Columns[2].ExtendedProperties.Add(
    "TZ", -7);
    dt.Columns[2].ExtendedProperties.Add(
    "TZ", -4);
    dt.RowChanged +=
    new DataRowChangeEventHandler(dt_RowChanged);

     

    static bool recalculatingTZ = false;
    void dt_RowChanged(object sender, DataRowChangeEventArgs e)
    {
     
    if (recalculatingTZ) return;
     
    DateTime utcTime = (DateTime)e.Row["Utc"];
     
    foreach (DataColumn column in e.Row.Table.Columns)
       
    if (column.ExtendedProperties.ContainsKey("TZ"))
        {
          recalculatingTZ =
    true;
          e.Row[column] = utcTime.AddHours((
    int)column.ExtendedProperties["TZ"]);
          recalculatingTZ =
    false;
        }
    }

    Wednesday, March 14, 2007 6:12 PM
  • If the data is used only by the local server, then there is no need to store it in UTC. You only need to store in UTC when the SAME data will be used in multiple timezones.

    Thursday, March 15, 2007 3:25 PM
  • Yes the same data will be used in different time zones.  For instance a user in the Central Time Zone may wish to schedule a job to run on a server located in the Pacific Time Zone.
    Thursday, March 15, 2007 4:07 PM
  • One issue I had with .net and c#. Did Microsoft take the week off when it came time to write a DateTime timezone conversion method? They must have because there is no local timezone conversion routines. Some guy in his basement should not have to code a somewhat complex routine to convert UTC time to the users local time. Luckily for us this guy was not on vacation that week while Microsoft was because he wrote a DateTime object conversion class.
    http://www.codeproject.com/dotnet/WorldClock.asp


    This just got me mad because the datetime object is incomplete in my opinion without having a way to convert to specific timezones.
    Thursday, March 15, 2007 4:27 PM
  • Am I missing something since doesn't DateTime support such conversions?

    DateTime dt = DateTime.Now.ToUniversalTime();

    DateTime dt2 = dt.ToLocalTime();

    MessageBox.Show(dt2.ToLongDateString() + " " + dt2.ToLongTimeString());

    Thursday, March 15, 2007 7:09 PM
  • Local time converts datetime to the time on your computer not to any timezone you want. Maybe there is a way i never found out about.
    Thursday, March 15, 2007 7:34 PM
  • Actually, this is similar to the solution I came up with.  I ended up adding two columns to my SQL database table, one to store the date as local time, and the other to store the same date converted to UTC.
    Friday, March 16, 2007 1:15 PM
  • This was not the issue I was having.  Yes, it is easy enough to convert local time to utc  My issue was wanting to store the date as UTC, but have the date displayed in the DataGridView converted to the user's local time.
    Friday, March 16, 2007 1:19 PM
  • Hi.  Just wondering.  The same requirement came up for me.  I was thinking of doing the same thing, do the UTC to Local conversion at the datatable level.  But how is this done?  Also for me, data that gets put into the datatable comes from a DataReader.  So, I am not able to modify the DataSetDateTime property of datetime datatype columns before the data fill.  Any idea how I might be able to pull this off?

    Thank,

    JB..
    Wednesday, March 21, 2007 5:59 PM
  •  JoseBonifacio wrote:
    Hi.  Just wondering.  The same requirement came up for me.  I was thinking of doing the same thing, do the UTC to Local conversion at the datatable level.  But how is this done?  Also for me, data that gets put into the datatable comes from a DataReader.  So, I am not able to modify the DataSetDateTime property of datetime datatype columns before the data fill.  Any idea how I might be able to pull this off?

    The code sample above does not really care where the data comes from. As long as you got the datatable prepared with the fake/new datetime column(s) for your timezone(s), the values of these rows will be calculated when the data of the rows change.

    Wednesday, March 21, 2007 7:47 PM
  • Hi.  I got one issue if I do that.  Since data is coming from a datareader.  What I do right now to get the data into the datatable is, datatable.Load(datareader).  Since I don't build the datatable structure by hand, how can I modify the columns DateTimeMode to Local so that when the data from the datareader gets filled into the datatable (by Load), dates will be in Local format?

    Thanks,

    JB..

    Wednesday, March 21, 2007 9:47 PM
  • First of all, have a look at this web site for info on Time Zones

    http://wwp.greenwichmeantime.com/info/timezone.htm

    My solution was to create two columns for each time value I needed to store.  For instance I have columns named TaskStartTime and TaskStartTimeUTC.  I also created another column named TimeZoneOffset.  This is the column where I store an integer to represent the TimeZone offset from GMT (Greenwich Mean Time).  So for Central Standard Time the value stored is -6, or 6 hours earlier than GMT.  The user interface allows the user to schedule a job to run relative to any time zone.  Here is how it works.

    Scenario: A manager located in the Eastern Standard Time Zone, needs to remotely schedule a task to run on a remote server locate in the Pacific Standard Time Zone.  This task needs to run at 10:00 AM CST. 

    From the user interface, he selects Central Standard Time from a combo box which stores -6 in the TimeZoneOffset column.  He then uses a datetimepicker to select the datetime of 10:00 AM.  This is also the value that is displayed to the user.  This value is converted to UTC by using dtTaskStartTime.AddHours(-iOffSet) and stored in the TaskStartTimeUTC column.  The reason I did not use the DateTime.ToUniversalTime() conversion is that the DateTime value in this case would have been converted using PST rather than CST, and therefore incorrect.  Note: if Daylight Savings is in effect, the iOffset value is incremented by 1 before converting to UTC.  So the value stored in TaskStartTimeUTC is 3:00 PM GMT (DST is in effect).

    This data is then replicated to the Database on the remote server.

    The windows service that is responsible for the running the task, compares the DateTime value in the TaskStartTimeUTC column to DateTime.UtcNow, and starts the task at 3:00 PM GMT, or 8:00 AM PST, or 10:00 AM CST.

    Wednesday, March 21, 2007 10:40 PM
  • I think the confusion here is that I have control over the back end data (SQL Server) table, so I am able to create the columns I need, where as you may not.  I sounds like you need to create extra columns and add them to the datatable after calling datatable.Load(datareader).
    Wednesday, March 21, 2007 10:46 PM
  • Actually, my requirement was simple.  All the datetime datatype that are stored on our database are in UTC.  All I wanted was to, on the GUI, display those datetime in Local format.  I thought that the DateTimeMode property of the Datatable.Columns's would help.  It will help, sort of, but it will not do the automatic convertion between formats.  You have to do it manually.  No free lunch. ;-)

    Anyway, got my issues figured out.

    Thanks for the hand.

    JB..

    Friday, March 23, 2007 10:31 PM
  • I am also having the same problem that the datetime autoconverts itself.

    this.DataTable.Load(dr) seems to change DateTimeMode back to DataSetDateTime.UnspecifiedLocal.

    How do I fix this?
    Monday, February 23, 2009 5:41 AM