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