Events_Download and Time Zones

Last post 07-18-2008 9:45 AM by typhoid. 3 replies.

Sort Posts:

  • Events_Download and Time Zones

    07-15-2008, 7:37 PM
    • Loading...
    • ddrossel
    • Joined on 06-24-2008, 4:35 AM
    • Posts 3

    I have an issue with the Club Web Site Events_Download routine.

    All my times are entered and stored in CST (Central Standard Time). My hosting server is in Arizona (MST, no daylight savings). My calendar (and that of all users of my site) is in the CST zone. When Events_Download is called to add an event to my calendar, the ".ToUniversalTime." routine assumes the entered CST times to be MST times and then coverts them to GMT for the VCalendar routine. Thess GMT values are then converted to the caledar time zone of CST for display on the calendar. Since there is a 2 hour differnce in MST (with no daylight savings) to CST during daylilght savings time, the calendar times are 2 hours to late.

    How do you work around this? Can you set the time zone for the application to CST so all times remain consistant (even though the server is in Arizona)?

    In my case knowing what the problem is doesn't neccassarily fix it for me.

  • Re: Events_Download and Time Zones

    07-16-2008, 8:41 AM
    • Loading...
    • typhoid
    • Joined on 08-10-2006, 9:14 AM
    • Posts 77

    A fully robust solution would be to store event times in UTC in the database, which would require that you know the user's time zone so you can convert correctly when saving or retrieving the times.

     However, since all of your users are in the same time zone you can use the same hack that I'm using, which is to adjust the times according to the timezone different when creating the calendar item for download.

    This is a snippet from my Events_Download.ashx, with the changes highlighted (I *think* that this is all I did - it's been awhile).  Note that you'll have to change the System.TimeSpan argument from -2 to -1 for your situation.

        public void writeCalEntry(int EventID, TextWriter output, string url)
        {
            SqlConnection connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ClubSiteDB"].ConnectionString);

            string qry = "SELECT Events.starttime, Events.endtime, Events.title, Events.description, Events.staticURL, Locations.title AS LocName, Locations.directions, ";
            qry += "Locations.description AS locDesc, Locations.address ";
            qry += "FROM Locations RIGHT OUTER JOIN ";
            qry += "Events ON Locations.id = Events.location ";
            qry += "WHERE (Events.id = @id)";

            SqlCommand command = new SqlCommand(qry, connection);
            SqlParameter param0 = new SqlParameter("@id", SqlDbType.Int);
            param0.Value = EventID;
            command.Parameters.Add(param0);

            connection.Open();
            DataTable dt = new DataTable();
            dt.Load(command.ExecuteReader());
            if (dt.Rows.Count > 0)
            {
                DataRow dr = dt.Rows[0];

                System.DateTime starttime, endtime;

                // Create a fudge factor to account for the hosting server being 2 hours West of all
                // of the users (host is in Pacific time zone, users are in Central time zone).
                // Fortunately, both host and users observe US Savings Time, so that aspect
                // can be ignored.
                System.TimeSpan tzconvert = new System.TimeSpan(0, -2, 0, 0);

                string title, description, location;

                StringBuilder sb = new StringBuilder();
                object o;

                // Extract the start and stop times, applying the time zone fudge factor           
                starttime = (DateTime)dr["starttime"] + tzconvert;
                if (dr["endtime"] != DBNull.Value)
                {
                    endtime = (DateTime)dr["endtime"] + tzconvert;
                }
                else
                {
                    endtime = starttime;
                }

  • Re: Events_Download and Time Zones

    07-17-2008, 7:45 PM
    Answer
    • Loading...
    • ddrossel
    • Joined on 06-24-2008, 4:35 AM
    • Posts 3

    Your solution does not work for me since Arizona dosn't follow daylight savings time which means any hard coded solution would only be right part of the time. It did however, lead me to a solution. My solution is:

    1. obtain a TimeZoneInfo object for the server's time zone and the event's time zone (Central Standard Time)

    2. use the .GetOffset method on both time zone objects to find the offset from UTC time

    3. the difference in the offsets is then added to the event's starttime and endtime to adjust the times to the server's time zone

    This allows the vCalendar portion of the procedure to properly set the date at the clients time zone. I did have to upgrade my web site to ASP.NET 3.5 to use the TimeZoneInfo object, this wasn't a problem since my server supports 3.5. The procedure should work regardless of the server's and client's location.

    The relevent portion of my Events_Download.ashx file is listed below with the code changes highlighted in green:

    ---------

    Public Sub writeCalEntry(ByVal EventID As Integer, ByVal output As IO.TextWriter, url as string)

    Dim connection As New SqlConnection(ConfigurationManager.ConnectionStrings("ClubSiteDB").ConnectionString)

    Dim qry As String = "SELECT Events.starttime, Events.endtime, Events.title, Events.description, Events.staticURL, Locations.title AS LocName, Locations.directions, "

    qry &= "Locations.description AS locDesc, Locations.address "

    qry &= "FROM Locations RIGHT OUTER JOIN "

    qry &= "Events ON Locations.id = Events.location "

    qry &= "WHERE (Events.id = @id)"

     

    Dim command As New SqlCommand(qry, connection)

    Dim param0 As New Data.SqlClient.SqlParameter("@id", Data.SqlDbType.Int)

    param0.Value = EventID

    command.Parameters.Add(param0)

    connection.Open()

     

    Dim dt As New Data.DataTable()

    dt.Load( command.ExecuteReader)

     

    If dt.Rows.Count > 0 Then

    Dim dr As Data.DataRow = dt.Rows(0)

     

    Dim starttime, endtime As Date

    Dim title, description, location As String

    Dim sb As New Text.StringBuilder()

    Dim o As Object

     

    Dim szone, ezone As TimeZoneInfo

    Dim soffset, eoffset, tzoffset As TimeSpan

    Dim id As String

     

    starttime = CDate(dr("starttime"))

     

    id = "Central Standard Time"

    szone = TimeZoneInfo.Local

    ezone = TimeZoneInfo.FindSystemTimeZoneById(id)

    soffset = szone.GetUtcOffset(starttime)

    eoffset = ezone.GetUtcOffset(starttime)

    tzoffset = soffset - eoffset

     

    starttime = starttime + tzoffset

    If Not IsDBNull(dr("endtime")) Then endtime = CDate(dr("endtime")) + tzoffset Else endtime = starttime

    If Not IsDBNull(dr("title")) Then title = CStr(dr("title")) Else title = "An untitled clubsite event"

     

  • Re: Events_Download and Time Zones

    07-18-2008, 9:45 AM
    • Loading...
    • typhoid
    • Joined on 08-10-2006, 9:14 AM
    • Posts 77

    That's definitely a more robust solution.  Too bad the hosting company that I'm using hasn't fully stepped up to ASP.NET 3.5.  Hopefully soon...

Page 1 of 1 (4 items)
Microsoft Communities
Page view counter