Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1153
  • Last Modified:

Time not correct when creating iCalendar and vCalendar files

One of my apps creates ical and vcal files on the fly and is based on the information in our database.

We have noticed that the time stored in the file (when viewed from Notepad) is an hour earlier then it should be.  I am guessing that it has something to do with daylight saving time.  

Could someone tell me how to trap/fix this and also if other timezones will have an impact (we are GMT)
BEGIN:VCALENDAR
PRODID:-//Microsoft Corporation//Outlook 12.0 MIMEDIR//EN
VERSION:2.0
METHOD:PUBLISH
X-MS-OLK-FORCEINSPECTOROPEN:TRUE
BEGIN:VEVENT
CLASS:PUBLIC
CREATED:20090421T145441Z
DTEND:20090421T083000Z
DTSTART:20090421T080000Z
PRIORITY:5
SEQUENCE:0
SUMMARY;LANGUAGE=en-gb:Test
END:VEVENT
END:VCALENDAR

Open in new window

0
Andy Brown
Asked:
Andy Brown
  • 18
  • 12
  • 5
1 Solution
 
David LeeCommented:
Hi, AndrewDBrown.

The time is written in "zulu" (GMT) time.  The receiving system will adjust accordingly.  I don't know anything about the app that's creating the iCal/vCal file, so I can't offer any thoughts on why the time is an hour off nor any suggestions on what to do to fix it.  That'd be specific to the app.
0
 
Andy BrownAuthor Commented:
Thanks BlueDevilFan (yet again).

I don't think that our app isn't the important part here as it simply creates the text file (attached).  The value DTSTART:20090421T080000Z is correct and matches the record on our database, but when you open it (double click) in Outlook, it adds the hour.
0
 
David LeeCommented:
"We have noticed that the time stored in the file (when viewed from Notepad) is an hour earlier then it should be"  
"but when you open it (double click) in Outlook, it adds the hour."

Sorry, now I'm confused.  The top quote says the time is off in the file.  The second quote says the time is off after opening in Outlook.    
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Andy BrownAuthor Commented:
Sorry, the actual file is correct.  But when you open it in Outlook - it adds an hour.
0
 
David LeeCommented:
No problem.  I assume you've checked your time and timezone settings in both Outlook and Windows and they are correct.  Right?
0
 
Andy BrownAuthor Commented:
How does this look?

Outlook - (GMT) with a tick in Adjust for daylight saving time
Vista - Correct time, Timezone = GMT
Vista - Internet time settings - Sychronize with Internet time server, server = time.windows.com
0
 
David LeeCommented:
Is Windows also adjusting for DST?
0
 
Andy BrownAuthor Commented:
Yes.
0
 
David LeeCommented:
Have you run the time zone data update tool for Outlook?

http://support.microsoft.com/kb/931667/en-us
0
 
Andy BrownAuthor Commented:
That didn't do the trick (although I am going to try again a little later).

Is there anyway from within Access if we can see if DST is in effect (I could then do the update from within VBA)?
0
 
David LeeCommented:
Using the Now function to retrieve the time from the system should give the DST adjusted date and time.
0
 
Andy BrownAuthor Commented:
When I run the Now() function in access it gives me the correct time, which in theory I could write to the iCal text file (after setting the format).  However, when I open up the text file within Outlook - it will still be an hour out.
0
 
David LeeCommented:
Try this.  Create an appointment on your calendar, save it as an iCal file, then delete the appointment from your calendar.  Now, import the iCal file.  Is it an hour off?
0
 
Andy BrownAuthor Commented:
Did that - the only place where it was an hour out, was in the test.ics file that I created.  The appointment was set for 09:00.

BEGIN:VCALENDAR
PRODID:-//Microsoft Corporation//Outlook 12.0 MIMEDIR//EN
VERSION:2.0
METHOD:PUBLISH
X-MS-OLK-FORCEINSPECTOROPEN:TRUE
BEGIN:VEVENT
CLASS:PUBLIC
CREATED:20090423T082034Z
DESCRIPTION:\n
DTEND:20090423T083000Z
DTSTAMP:20090423T082034Z
DTSTART:20090423T080000Z
LAST-MODIFIED:20090423T082034Z
PRIORITY:5
SEQUENCE:0
SUMMARY;LANGUAGE=en-gb:Today
TRANSP:OPAQUE
UID:040000008200E00074C5B7101A82E008000000009025BDB8F4C3C901000000000000000
      010000000E413A9E4DF1E8545938A6EF579765688
X-ALT-DESC;FMTTYPE=text/html:<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//E
      N">\n<HTML>\n<HEAD>\n<META NAME="Generator" CONTENT="MS Exchange Server ve
      rsion 08.00.0681.000">\n<TITLE></TITLE>\n</HEAD>\n<BODY>\n<!-- Converted f
      rom text/rtf format -->\n\n<P DIR=LTR><SPAN LANG="en-gb"></SPAN></P>\n\n</
      BODY>\n</HTML>
X-MICROSOFT-CDO-BUSYSTATUS:BUSY
X-MICROSOFT-CDO-IMPORTANCE:1
X-MICROSOFT-DISALLOW-COUNTER:FALSE
X-MS-OLK-ALLOWEXTERNCHECK:TRUE
X-MS-OLK-CONFTYPE:0
BEGIN:VALARM
TRIGGER:-PT15M
ACTION:DISPLAY
DESCRIPTION:Reminder
END:VALARM
END:VEVENT
END:VCALENDAR
0
 
Andy BrownAuthor Commented:
Also - every other time reference in the text file is an hour out.
0
 
David LeeCommented:
Then the time, timezone, or DST calculation must be off in Outlook.  I can't think of any other explanation.  Try running this code in Outlook.

Sub ShowTime()
    MsgBox Now()
End Sub

Open in new window

0
 
Andy BrownAuthor Commented:
I've not programmed in Outlook before, but found my way to the Immediate pane.  I typed in "msgbox Now()" and it came up with the correct date and time.
0
 
Andy BrownAuthor Commented:
Could you take a look at the last post on this website, and tell me if it means anything to you.  Just for the record, we are based in the UK so are using GMT.

http://forums.macosxhints.com/archive/index.php/t-73284.html
0
 
David LeeCommented:
Then I'm stumped.  I can't understand how it's possible for Outlook to have the right date/time and timezone while being an hour off when importing or exporting appointments.  It doesn't make sense.  Both operations (i.e. importing and exporting an .ics file) use GMT time.  Outlook gets the timezone offset from GMT defined in the system and applies it to the dates/times in the appointment.  If the offset is correct, then the appointments should be at the right time.  Since they aren't I have to think that the offset is wrong.  Maybe you should try changing your timezone in Outlook, see what effect that has on your appointments, and then set it back to GMT and see what happens.
0
 
Andy BrownAuthor Commented:
You might be correct - but changing Outlook, may impact on my clients setup (and it was one of my clients that spotted it initially).  I might be better off finding a way to detect DST and adjust the time from within my Access application.  I'm a little nervous about doing this as I don't know what will happen in other timezones - but I can't see what choice I have have.

I'll take another look in the next day or two and let you know if I find anything.

Thanks for all of your help on this one.
0
 
David LeeCommented:
Are you on a Mac?  If not, then I don't think that post applies.  It says it's for the Mac OS and the behavior described is nothing I ever seen in Outlook.  As a test I saved the iCal data you posted above and imported the resulting file into my Outlook.  The appointment showed up at the correct date and time.  
0
 
Andy BrownAuthor Commented:
I'm on a PC.  If you copied and pated my example into a .ics file and then double click on it - does it work on your PC?
0
 
David LeeCommented:
Understand.  I don't think the time in Access is the issue.  If it were, then running Now should produce the wrong time and the time in the .ics file should be wrong.  That's not the case though.  The problem appears to be isolated to Outlook, especially since when Outlook saved an appointment to .ics format and then reimported it the appointment shifted to be an hour off.  Access was never involved.  
0
 
Andy BrownAuthor Commented:
I understand - just pulling my hair out to come up with a possible fix.
0
 
Andy BrownAuthor Commented:
I am running a temporary fix from the Access database for the time being.  Thanks for all of your help though.
0
 
David LeeCommented:
You're welcome.
0
 
BobPCommented:
The way that I do it is to have a SystemTimInfo class that can return the tgimezone name and the DST offset, and chan ge all of the timestamps to UTC and specify the timezone as a TZID property.
0
 
Andy BrownAuthor Commented:
That sounds like a great idea - thank you.

Do you have any code examples (I could post a new question if you'ld like)?
0
 
BobPCommented:
This is what the iCal file looks like this below. Note that the timestamps do not have a Z at the end, that signifies UTC, so we take the start and end times, pass the timezone, and adjust the local times to UTC as I said.

I will dig out my code and post that later.


BEGIN:VCALENDAR
PRODID:-//Microsoft Corporation//Outlook 12.0 MIMEDIR//EN
VERSION:2.0
METHOD:PUBLISH
X-MS-OLK-FORCEINSPECTOROPEN:TRUE
BEGIN:VEVENT
CLASS:PUBLIC
CREATED;TZID=GMT Standard Time::20090421T145441
DTEND;TZID=GMT Standard Time::20090421T083000
DTSTART;TZID=GMT Standard Time:20090421T080000
PRIORITY:5
SEQUENCE:0
SUMMARY;LANGUAGE=en-gb:Test
END:VEVENT
END:VCALENDAR

Open in new window

0
 
Andy BrownAuthor Commented:
Perfect - it's a big help.
0
 
BobPCommented:
This is the class code

and you use it like so

    mpStartTime = "DTSTART" & ";" & _
                  "TZID=" & mpTimeZone & ":" & _
                  Format(Int(StartTime), "YYYYMMDD") & _
                  "T" & Format(StartTime - Int(StartTime) + TimeSerial(0, mpDSTBias, 0), "hhmmss")
                 
    mpEndTime = "DTEND" & ";" & _
                  "TZID=" & mpTimeZone & ":" & _
                  Format(Int(EndTime), "YYYYMMDD") & _
                  "T" & Format(EndTime - Int(EndTime) + TimeSerial(0, mpDSTBias, 0), "hhmmss")

Option Explicit
 
Private Const dhcErrBase = vbObjectError + 9156
Private Const dhcErrCantSetTime = dhcErrBase + 0
Private Const dhcErrCantSetTZI = dhcErrBase + 1
 
Private Const CLASS_NAME As String = "SystemTimeInfo"
Private Const ERROR_SET_TIME As String = "Unable to set system time"
Private Const ERROR_SET_STANDARD_TZ = "Unable to set standard time zone name"
 
Private Type SYSTEMTIME
    Year As Integer
    Month As Integer
    DayOfWeek As Integer
    Day As Integer
    Hour As Integer
    Minute As Integer
    Second As Integer
    Milliseconds As Integer
End Type
 
Private Enum TimeZoneID
    TIME_ZONE_ID_UNKNOWN = 0        ' Cannot determine DST
    TIME_ZONE_ID_STANDARD = 1       ' Standard Time, not Daylight
    TIME_ZONE_ID_DAYLIGHT = 2       ' Daylight Time, not Standard
End Enum
 
Private Type TIME_ZONE_INFORMATION
    Bias As Long
    StandardName(0 To 63) As Byte
    StandardDate As SYSTEMTIME
    StandardBias As Long
    DaylightName(0 To 63) As Byte
    DaylightDate As SYSTEMTIME
    DaylightBias As Long
End Type
 
Private Declare Sub GetSystemTime Lib "kernel32" ( _
    lpSystemTime As SYSTEMTIME)
 
Private Declare Function SetSystemTime Lib "kernel32" ( _
    lpSystemTime As SYSTEMTIME) As Long
 
Private Declare Function GetTimeZoneInformation Lib "kernel32" ( _
    lpTimeZoneInformation As TIME_ZONE_INFORMATION) As Long
 
Private Declare Function SetTimeZoneInformation Lib "kernel32" ( _
    lpTimeZoneInformation As TIME_ZONE_INFORMATION) As Long
 
Public Property Get SystemDateTime() As Date
Dim ST As SYSTEMTIME
 
    Call GetSystemTime(ST)
    SystemDateTime = SystemTimeToVBADate(ST)
End Property
 
Public Property Get IsDST() As Boolean
Dim TZID As TimeZoneID
Dim TZI As TIME_ZONE_INFORMATION
 
    TZID = GetTimeZoneInformation(TZI)
    IsDST = (TZID = TIME_ZONE_ID_DAYLIGHT)
End Property
 
Public Property Get Bias() As Long
Dim lngBias As Long
Dim TZID As TimeZoneID
Dim TZI As TIME_ZONE_INFORMATION
    
    TZID = GetTimeZoneInformation(TZI)
    Select Case TZID
    
        Case TIME_ZONE_ID_UNKNOWN
            lngBias = TZI.Bias
            
        Case TIME_ZONE_ID_STANDARD
            lngBias = TZI.Bias + TZI.StandardBias
            
        Case TIME_ZONE_ID_DAYLIGHT
            lngBias = TZI.Bias + TZI.DaylightBias
    End Select
    
    Bias = lngBias
End Property
 
Public Property Get CurrentTimeZoneName() As String
Dim TZI As TIME_ZONE_INFORMATION
Dim TZID As TimeZoneID
Dim StdName() As Byte
    
    TZID = GetTimeZoneInformation(TZI)
    
    Select Case TZID
    
        Case TIME_ZONE_ID_UNKNOWN
            StdName = TZI.StandardName
            
        Case TIME_ZONE_ID_STANDARD
            StdName = TZI.StandardName
            
        Case TIME_ZONE_ID_DAYLIGHT
            StdName = TZI.DaylightName
    End Select
    
    CurrentTimeZoneName = GetTZName(StdName())
End Property
 
Public Property Get StandardTimeZoneName() As String
Dim TZI As TIME_ZONE_INFORMATION
    
    Call GetTimeZoneInformation(TZI)
    StandardTimeZoneName = GetTZName(TZI.StandardName)
End Property
 
Public Property Get DaylightTimeZoneName() As String
Dim TZI As TIME_ZONE_INFORMATION
    
    Call GetTimeZoneInformation(TZI)
    DaylightTimeZoneName = GetTZName(TZI.DaylightName)
End Property
 
Public Property Get DaylightBias() As Long
Dim TZID As TimeZoneID
Dim TZI As TIME_ZONE_INFORMATION
    
    TZID = GetTimeZoneInformation(TZI)
    DaylightBias = TZI.DaylightBias
End Property
 
Public Property Get StandardBias() As Long
Dim TZID As TimeZoneID
Dim TZI As TIME_ZONE_INFORMATION
    
    TZID = GetTimeZoneInformation(TZI)
    StandardBias = TZI.StandardBias
End Property
 
Private Function SystemTimeToVBADate(ST As SYSTEMTIME) As Date
Dim dtm As Date
    
    With ST
    
        dtm = DateSerial(.Year, .Month, .Day) + _
         TimeSerial(.Hour, .Minute, .Second)
    End With
    
    SystemTimeToVBADate = dtm
End Function
 
Private Function GetTZName(InVector() As Byte) As String
Dim Pos As Long
Dim strTemp As String
    
    strTemp = CStr(InVector)
    
    ' Set the length of the string, looking for
    ' the final vbNullChar.
    Pos = InStr(1, strTemp, vbNullChar)
    
    If Pos > 0 Then
    
        strTemp = Left$(strTemp, Pos - 1)
    End If
    
    GetTZName = strTemp
End Function

Open in new window

0
 
BobPCommented:
Forgot the class setup

Dim STI As SystemTimeInfo
 
    Set STI = New SystemTimeInfo
    
    mpTimeZone = STI.CurrentTimeZoneName
    mpDSTBias = STI.DaylightBias

Open in new window

0
 
Andy BrownAuthor Commented:
That is perfect, thank you - spot on.

All the best.
0
 
BobPCommented:
My pleasure. I was building an iCal application recently, which is why I had the code.
0
 
Andy BrownAuthor Commented:
It's been a big help - thanks again.
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 18
  • 12
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now