One of the trickiest issues with databases and information is the storage of dates and dealing with time zones. I have to use data historians in most of my projects which means that most of the data is time-series based and not necessarily relational. Data historians (OSISoft PI, Wonderware Historian (InSQL), etc.) usually store time zone information with each data acquisition which explicitly stores the exact time zone information at the moment the data is obtained. In most relational databases, the DBA or designer does not always store time zone information along with a time stamp. The time stamp should be stored in UTC along with useful information such as the time zone of the user or server, and possibly if any daylight savings (DST) is in effect.

One of the challenges I encountered is how to retrieve data from relational databases when the time stamp is in UTC and display it to the user in his or her preferred time zone. In order to accomplish the time zone conversion, I used a code utility to process each DateTime field and convert it to the selected time zone. I used a client cookie to store the time zone in the web browser.

function checkClientTimeZone() {
    // Set the client time zone
    var dt = new Date();
    SetCookieCrumb("ClientDateTime", dt.toString());

    var tz = -dt.getTimezoneOffset();
    SetCookieCrumb("ClientTimeZone", tz.toString());

    // Expire in one year
    dt.setYear(dt.getYear() + 1);
    SetCookieCrumb("expires", dt.toUTCString());
}

The code to process the Dataset was based on a Microsoft KB article and modified to use the cookie as shown above.

/// 

/// Summary description for TimeZoneUtility. Adapted from
/// http://support.microsoft.com/default.aspx?scid=kb;en-us;842545
/// 
public static class TimeZoneUtility
{
    public static string AdjustDSTimeZone(DataSet ds)
    {
        // Obtains the time difference on the sender computer that
        // remoted this dataset to the Web service.
        string sourceTicksString = ds.ExtendedProperties["UTCDifference"].ToString();
        long sourceTicks = long.Parse( sourceTicksString );

        // Obtain the UTC offset for the remote computer.
        //DateTime baseUTC = DateTime.Now;
        //long UtcTicksLocal = TimeZone.CurrentTimeZone.GetUtcOffset( baseUTC ).Ticks;

        // Obtain the time difference between the sender computer and the remote computer.
        //long ticksDifference = sourceTicks - UtcTicksLocal;
        //TimeSpan timespan = new TimeSpan( ticksDifference );

        TimeSpan timespan = new TimeSpan( sourceTicks );

        // The following code iterates through each table, and find all the columns that are
        // DateTime columns. After identifying the columns that have to be adjusted,
        // it traverses the data in the table and adjusts the DateTime columns back to their
        // original values. You must leave the RowState of the DataRow in the same state
        // after making the adjustments.
        foreach ( DataTable table in ds.Tables )
        {
            DataColumnCollection columns = table.Columns;
            int[] ColumnNumbers = new int[columns.Count];
            int   ColumnNumbersIndex = 0;
            for ( int i = 0; i < columns.Count; i++ )
            {
                DataColumn col = columns[i];
                if ( col.DataType == typeof( DateTime ) )
                {
                    ColumnNumbers[ColumnNumbersIndex] = i;
                    ColumnNumbersIndex++;
                }
            }
            foreach ( DataRow row in table.Rows )
            {
                switch ( row.RowState )
                {
                    case DataRowState.Unchanged:
                        AdjustDateTimeValues( row, ColumnNumbers,
                            ColumnNumbersIndex, timespan );
                        row.AcceptChanges();	// This is to make sure that the
                        // row appears to be unchanged again.
                        Debug.Assert( row.RowState == DataRowState.Unchanged );
                        break;

                    case DataRowState.Added:
                        AdjustDateTimeValues( row, ColumnNumbers, ColumnNumbersIndex, timespan );
                        // The row is still in a DataRowState.Added state.
                        Debug.Assert( row.RowState == DataRowState.Added );
                        break;

                    case DataRowState.Modified:
                        AdjustDateTimeValues( row, ColumnNumbers, ColumnNumbersIndex, timespan );
                        // The row is a still DataRowState.Modified.
                        Debug.Assert( row.RowState == DataRowState.Modified );
                        break;

                    case DataRowState.Deleted:
                        //   This is to make sure that you obtain the right results if
                        //the .RejectChanges()method is called.
                        row.RejectChanges();	// This is to "undo" the delete.
                        AdjustDateTimeValues( row, ColumnNumbers, ColumnNumbersIndex, timespan );
                        // To adjust the datatime values.
                        // The row is now in DataRowState.Modified state.
                        Debug.Assert( row.RowState == DataRowState.Modified );
                        row.AcceptChanges();	// This is to mark the changes as permanent.
                        Debug.Assert( row.RowState == DataRowState.Unchanged );
                        row.Delete();
                        // Delete the row. Now, it has the same state as it started.
                        Debug.Assert( row.RowState == DataRowState.Deleted );
                        break;

                    default:
                        throw new ApplicationException
                            ( "You must add a case statement that handles the new version of the dataset." );
                }
            }
        }

        string str = sourceTicksString; // ds.Tables["MyTable"].Rows[0][1].ToString() ;
        return str;
    }

    public static void AdjustDateTimeValues(DataRow row, int[] ColumnNumbers, int columnCount, TimeSpan timespan)
    {
        for ( int i = 0; i < columnCount; i++ )
        {
            int columnIndex = ColumnNumbers[i];
            if (row[columnIndex] != DBNull.Value)
            {
                DateTime original = (DateTime)row[columnIndex];
                DateTime modifiedDateTime = original.Add(timespan);
                row[columnIndex] = modifiedDateTime;
            }
        }
    }

    /// 

    /// Returns the client (if available in cookie) or server timezone.
    /// 
    /// 
    /// 
    public static int GetTimeZoneOffset(HttpRequest Request)
    {
        TimeZone tz = TimeZone.CurrentTimeZone;
        TimeSpan ts = tz.GetUtcOffset(DateTime.Now);
        int result = (int) ts.TotalMinutes;
        HttpCookie cookie = Request.Cookies["ClientTimeZone"];
        if ((cookie != null) && AppUtility.IsNumeric(cookie))
            result = Convert.ToInt32(cookie.Value);
        return result;
    }

    /// 

    /// Returns the client (if available in cookie) or server timezone.
    /// 
    /// 
    /// 
    public static int ClientTimeZoneOffset
    {
        get
        {
            return GetTimeZoneOffset(HttpContext.Current.Request);
        }
    }

    public static TimeSpan ClientTimeZoneSpan
    {
        get
        {
            TimeSpan ts = TimeSpan.MinValue;
            HttpContext Context = HttpContext.Current;

            // Check session for client time zone
            object tz = Context.Session["ClientTimeZone"];
            if (tz != null)
            {
                ts = (TimeSpan) tz;
            }
            else
            {
                // Check for client cookie
                HttpCookie cookie = Context.Request.Cookies["ClientTimeZone"];
                if ((cookie != null) && AppUtility.IsNumeric(cookie.Value))
                {
                    int minutes = Convert.ToInt32(cookie.Value);
                    ts = new TimeSpan(0, minutes, 0);
                    Context.Session.Add("ClientTimeZone", ts);
                }
            }

            return ts;
        }
    }

    /// 

    /// Returns the server timezone.
    /// 
    /// 
    /// 
    public static int ServerTimeZoneOffset()
    {
        TimeZone tz = TimeZone.CurrentTimeZone;
        TimeSpan ts = tz.GetUtcOffset(DateTime.Now);
        int result = (int) ts.TotalMinutes;
        return result;
    }

    public static TimeSpan ServerTimeZoneSpan
    {
        get
        {
            DateTime dt = DateTime.Now;
            TimeSpan ts = TimeZone.CurrentTimeZone.GetUtcOffset(dt);
            return ts;
        }
    }

    public static object UTCtoClientTZ(object value)
    {
        object result = value;
        if (value is DateTime)
        {
            DateTime dt = (DateTime) value;
            TimeSpan ts = ClientTimeZoneSpan;

            // Adjust to client time zone if set, or use the server timezone.
            if (ts != TimeSpan.MinValue)
                result = dt.Add(ts);
            //else
            //	result = UTCtoServerTZ(dt);
        }
        return result;
    }

    public static object UTCtoServerTZ(object value)
    {
        object result = value;
        if (value is DateTime)
        {
            DateTime dt = (DateTime) value;
            TimeZone tz = TimeZone.CurrentTimeZone;
            result = tz.ToLocalTime(dt);
        }
        return result;
    }

    public static object ClientToServerTZ(object value)
    {
        object result = value;
        if (value is DateTime)
        {
            DateTime dt = (DateTime) value;
            result = UTCtoServerTZ(dt.ToUniversalTime());
        }
        return result;
    }

    public static object ServerToClientTZ(object value)
    {
        object result = value;
        if (value is DateTime)
        {
            DateTime dt = (DateTime) value;
            result = UTCtoClientTZ(dt.ToUniversalTime());
        }
        return result;
    }
}

One of the remaining challenges is recalling the data using the correct DST adjustment. When I get more time I will add DST adjustment to the code