You are here: Home Tech Android TimeTracker data extraction

Android TimeTracker data extraction

by Chris Shenton last modified Jun 08, 2011 02:03 PM
How to generate a report from the database managed by TimeTracker for Android by ABS

I use the excellent TimeTracker by ABS to track my client work on my Android phone. I recently managed to trash the app and data with a misguided attempt at an upgrade, but fortunately I had taken a backup with Rerware's MyBackup.

I copied the data from the SD card under /rerware/MyBackup/AllAppsBackups/AppsMedia_2011_06_04/Apps/de.abs_net.TimeTracker_79.zip to my desktop, unzipped, and dig into it with sqlite3.  It was fairly straight forward to find what I needed, but the 'start' and 'stop' times in the 'period' table were in a format that looked like UNIX seconds since the epoch, but the numbers were too long.

ABS's Volker Lehnen answered my request for pointers promptly, with exactly what I needed:

the value in start, stop represent the number of miliseconds since Jan. 1. 1970, midnight GMT (Date.getTime() in Java)

I tweaked my SQL query to generate a report with the Client, Project, Task, Start, Stopped, fractional hours, and comments on each work item.  Here it is in case it helps anyone else:

 SELECT 
  datetime(start/1000,'unixepoch','localtime'), 
  datetime(stop/1000,'unixepoch','localtime'), 
  round((stop - start) / 1000.0 / 60.0 / 60.0, 1), 
  client.name, project.name, task.name, period.comment 
FROM 
  period, task, project, client 
WHERE 
  period.task_id = task.task_id AND 
  task.project_id = project.project_id AND 
  project.client_id = client.client_id
ORDER BY
  client.name;

Which resulted in something like this:

2011-04-25 22:13:16|2011-04-25 22:57:14|0.7|FooClient|HITSS2|proposal|review appdev doc.
2010-09-14 08:09:53|2010-09-14 19:35:03|11.4|BarClient|SID|work|dig into schema, map fields, find missing
2010-09-15 19:31:00|2010-09-15 20:01:54|0.5|BarClient|SID|work|start parsing changes
...

Easy enough.

Thanks for the help, Volker!
Share this: