OmniFocus sqlite completedDate

A while back, I posted about how OmniFocus maintains a sqlite database. This could be really interesting for writing software to do new stuff on that data.

Jared just dug into this and noticed that the completedDate field was an integer, but it did not appear to be a unix epoch time.

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

3 thoughts on “OmniFocus sqlite completedDate

  1. Hi Marc, I’m intrigued at what you discovered (access Omnifocus data from it’s SQLite database). I am trying to see if I can create a web front-end for Omnifocus data, so I can access my Omnifocus data at work on my Windows machine. Could you please provide any guidance on how to go about doing it. Thanks in advance.

    Roger

  2. Pingback: PyOmniFocus « Marc Abramowitz

Leave a Reply

Your email address will not be published.