Accessing OmniFocus data as a sqlite database

While playing with some OmniFocus AppleScripts last night, I realized that OmniFocus on the Mac uses a sqlite database (in addition to the zipped .xml files that it uses for synching). This is pretty interesting, because it could be a very convenient way to write tools that interact with OmniFocus (and use something like Python instead of AppleScript).

$ sqlite3 ~/Library/Caches/com.omnigroup.OmniFocus/OmniFocusDatabase2
SQLite version 3.7.3
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .mode column                                                                                                                         
sqlite> .headers on
sqlite> SELECT task.name AS task, context.name AS context
   ...> FROM task JOIN context ON task.context = context.persistentIdentifier
   ...> WHERE context.name LIKE '%email%' AND task.dateCompleted IS NULL;
task                                           context   
---------------------------------------------  ----------
Send Amanda new color photo via Cloud website  Email

Pretty cool. With a little Python and SQLAlchemy, I could probably create a nice little Python module for dealing with OmniFocus data.

6 thoughts on “Accessing OmniFocus data as a sqlite database”

`select name, dateCompleted from task where dateCompleted is not null`

All of the results returned have completed dates which are off by thirty years and a day from what is reported in the OmniFocus app.

Python’s datetime.fromtimestamp() returns: 1981-02-16 23:11:42.208916 for a date in one of the above items.
OmniFocus reports this date as Feb 17, 2012 11:11 PM.

And by looking at all those microseconds, I must be doing something wrong, or else this is not a traditional Unix timestamp.

What do you think?

Hey Jared,

That’s interesting. I just did a little experimentation and it seems that OmniFocus is setting dateCompleted to the number of seconds since Jan. 1, 2001 00:00:00 GMT.

I figured this out by setting my time zone in System Preferences to GMT and then I went into OmniFocus and created a task and set the completion date to “1/1/01 00:00:00”. Then in sqlite:

sqlite> select name, datecompleted, datetime(dateCompleted + 978307200, 'unixepoch', 'localtime') from task where dateCompleted is not null;
...
Take clock out of Honda                 351241538.054  2012-02-18 07:05:38                                          
Ate sushi                               351241498.856  2012-02-18 07:04:58                                          
Order replacement light bulb from Amaz  351241558.269  2012-02-18 07:05:58                                          
Test -- item completed on 2001-01-01 0  0              2001-01-01 00:00:00

and after setting my timezone back to GMT-8:

sqlite> select name, datecompleted, datetime(dateCompleted + 978307200, 'unixepoch', 'localtime') from task where dateCompleted is not null;
...
Take clock out of Honda                 351241538.054  2012-02-17 23:05:38                                          
Ate sushi                               351241498.856  2012-02-17 23:04:58                                          
Order replacement light bulb from Amaz  351241558.269  2012-02-17 23:05:58                                          
Test -- item completed on 2001-01-01 0  0              2000-12-31 16:00:00

Pingback: OmniFocus sqlite completedDate « Marc Abramowitz

Pingback: PyOmniFocus « Marc Abramowitz

To see a way of retrieving the full and nested data hierarchy from OmniFocus by reading the Sqlite cache with Python, filtering the data retrieved with SQL conditions, and using a recursive trigger to get nested (rather than flat tabular) results, you might find it interesting to look at this OmniFocus to OPML / iThoughtsHD / Markdown / FoldingText script at: http://forums.omnigroup.com/showthread.php?t=25965

Rob

Dan,Excellent work on all the OF scripts I just dcviosered them the other week but they already are making a big impact in my workflow. Thank you for sharing it.One question regarding the clear start and due dates script could the script be modified to also change the context of the task to, for instance, Someday ?

6 thoughts on “Accessing OmniFocus data as a sqlite database”

Leave a Reply