Recovering Lost/Missing Calendar Appointments from iPhone OS 3.1.2

UPDATE: After writing this article and getting peeved at trying to keep iTunes and Outlook syncing correctly I now use GoogleSync and Google Calendar Sync for Outlook to keep my calendar/contacts/emails syncronized wirelessly. So far it appears to be a much better option.

I rescued the mysterious loss of some calendar appointments using the following steps. Note you WILL need a backup of your entire iPhone made through iTunes.

(I did this with an iPhone running OS 3.1.2)

• Ran iPhoneBackupExtractor on my iTunes backup file
• Extracted Library/Calendar/Calendar.sqlitedb
• Opened using free Firefox Addon SQL Lite Manager
• Browsed the Event Table
• Selected the records I wanted to recover, right-clicked and copied as CSV (Excel compatible)
• Pasted into MS Excel

Recovering the original dates and times was quite confusing. I thought the times should have been seconds since 1/1/1970, but it seems mine were seconds since 1/1/2001. So I created a new column with this formula:

="1/1/2001" + F1/60/60/24 + 11/24

This assumes your number is in cell F1 (e.g. 281577600). The middle part converts the seconds to days (required for Excel) and the last bit adds 11 hours (because I'm in Eastern Australia timezone). This should give you the original dates and times back. You can then import those dates into Outlook, or your other calendar app.

Hope that helps someone.

Simon.