Extracting useful data from Things (Cloud beta) via its SQLite database

things_beta_list_completed_tasks.sh

#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;

things_beta_tasks_completed.sh

#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;"

things_beta_tasks_created.sh

#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;"