Basic script to help me track time spent on tasks.

timetracker.sh

#!/bin/bash

#very simple script to track time spent on tasks
#assumes: 
# - you are single threaded, so no concurrent tasks
# - if you punch in to a task while you are punched into another one, you're punched out from the latter
# - you only care about accuracy down to minute intervals
# - you have a running mysql instance in localhost
# - you're not replicating this anywhere, you don't expect this to grow a lot, since the schema is stupid and has no keys other than PK, etc. This is only meant to help you track your daily work hours, you should be permanently saving those somewhere else. 
# - you just want to track time. you don't really care about performance for this little helper script (i.e. it runs the CLI several times in a row instead of keeping an open connection)

#what the script does depends on the name it was invoked as. suggested symlinks: 
# - pin  : punch in
# - pout : punch out
# - tsum : task summary
#config

# set this to a CLI invocation that will land you on a mysql instance with the right schema
MY="mysql timetracker"
# the same as above, but with no schema. it is only used to create the schema
MY_INSTALL="mysql"

fail()
{
	echo $*>&2
	exit 1
}

task=$(basename $0)

case $task in
		"timetracker_install")
				# "install" this
				# we create the db, table, and insert a dummy row for the sole purpose of avoiding having to write an edge case code for the first task
				$MY_INSTALL -e "create database if not exists timetracker; use timetracker;create table if not exists entries (id int unsigned not null auto_increment primary key, task tinytext, start timestamp default now(), end timestamp) engine = innodb; insert into timetracker.entries (task) values ('initial system task');select 'timetracker installed ok'"
				;;
		"pin")
			   # punch in to a task
				[ -z "$1" ] && fail "pi requires a task description to punch in" 
				# what comes next will give you warnings if binlog_format=STATEMENT
				$MY -e "update entries set end = if(end='000-00-00 00:00:00',now(),end) order by id desc limit 1"
				$MY -NB -e "select concat(task, ' ended at ', end) from entries order by id desc limit 1 "
				$MY -e "insert into entries (task) values (\"$*\")"
				$MY -NB -e "select concat(task, ' started at ', start) from entries order by id desc limit 1"
				;;
		"pout")
				# punch out from a task
				$MY -e "update entries set end = if(end='000-00-00 00:00:00',now(),end) order by id desc limit 1"
				$MY -NB -e "select concat(task, ' ended at ', end) from entries order by id desc limit 1 "
				;;
		"tsum")
				# summary will display time spent on tasks in minutes 
				# if you specify two arguments, they are assumed to be dates and are used to only display tasks that started or finished between those dates
				where=
				[ -n "$1" -a -n "$2" ] && where=" where (date(start) between \"$1\" and \"$2\") or (date(end) between \"$1\" and \"$2\") "	
				$MY -e "select count(id) as stages, task,sum(if(end='0000-00-00 00:00:00',time_to_sec(now()),time_to_sec(end))-time_to_sec(start))/60 as minutes, date(if(end='0000-00-00 00:00:00',now(),end)) as day_finished from entries $where group by task order by day_finished asc"
				;;
		"daily")
				# daily activity summary
				today=$(date "+%Y-%m-%d %H:%M:%S")
				where="where ((date(start) = date(\"$today\")) or (date(end) = date(\"$today\")))"
				current=$($MY -NBe "select task from entries where end = '0000-00-00 00:00:00' limit 1")
				[ -n "$current" ] &&  echo "Currently open task: $current"
				$MY -e "select count(id) as stages, task, sum(if(end='0000-00-00 00:00:00',time_to_sec(now()),time_to_sec(end))-time_to_sec(start))/60 as minutes , date(end) as day from entries $where group by task order by start asc"
				$MY -e "select sum(if(end='0000-00-00 00:00:00',time_to_sec(now()),time_to_sec(end))-time_to_sec(start))/3600 as work_hours from entries $where and task not like 'personal%'"
				$MY -e "select sum(if(end='0000-00-00 00:00:00',time_to_sec(now()),time_to_sec(end))-time_to_sec(start))/3600 as personal_hours from entries $where and task like 'personal%'"
				;;

esac