Recently I was looking for a digital punch card. Of course, I could just use the w command, subtract my breaks and that’s that at the end of the day. But there’s always something not work related which needs to be remembered and after all: I am lazy.

So, I installed on my phone, which has a nice widget to literally punch (touch) in and out. The output of my Work Clock is a SQLite3 database. To use it in bash I need to do sudo apt-get install sqlite3.

The looks like this (via SO):

sqlite3 ~/PunchClock_*.db <<!
.headers on
.mode csv
.output out.csv
select punchId, strftime('%Y-%m',startTime) as context, strftime('%d',startTime) as day, round(strftime('%H',startTime) + (strftime('%M',startTime)/60.0),1) as start, round(strftime('%H',endTime) + (strftime('%M',endTime)/60.0),1) as end from WorkTimes;

This redirects basically everything inbetween the exclamation marks to the sqlite3 program. It turns headers on, sets CSV mode, defines the output file and states a SQL command which will output the data the way I need it (company time cards count and add hours in decimal mode). And that’s that.