Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Extracting useful data from Things (Cloud beta) via its SQLite database
#Completed tasks (showing their title, start date, completion date and time to complete in days)
sqlite3 -csv -header ~/Library/Application\ Support/Cultured\ Code/Things\ beta/ThingsLibrary.db "SELECT
substr(ZTITLE,0,26) as title,
datetime(ZCREATIONDATE, 'unixepoch', '+31 years', 'localtime') as startdate,
datetime(ZSTOPPEDDATE, 'unixepoch', '+31 years', 'localtime') as completeddate,
round(julianday(datetime(ZSTOPPEDDATE, 'unixepoch', '+31 years', 'localtime')) - julianday
(datetime(ZCREATIONDATE, 'unixepoch', '+31 years', 'localtime')),5) as age
FROM ZTHING WHERE ZSTATUS = 3 ORDER BY completeddate;
#Tasks completed per day
sqlite3 -csv -header ~/Library/Application\ Support/Cultured\ Code/Things\ beta/ThingsLibrary.db "SELECT
date(ZSTOPPEDDATE, 'unixepoch', '+31 years', 'localtime') as date,
count(Z_PK) as completed
from ZTHING WHERE ZSTATUS = 3 GROUP BY date;"
#Tasks created per day
sqlite3 -csv -header ~/Library/Application\ Support/Cultured\ Code/Things\ beta/ThingsLibrary.db "SELECT
date(ZCREATIONDATE, 'unixepoch', '+31 years', 'localtime') as date,
count(Z_PK) as created
FROM ZTHING WHERE ZCREATIONDATE != '' GROUP BY date;"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment