I actually created a sqlite database to track exactly how long I worked at various jobs. I created this database, because I don’t trust the payroll system we use at work. I will share the schema and views in this post. If you don’t want to read through this post you can get it here.
This schema has three tables, they are
WorkSession. Now you can probably guess what the tables do, but I will
explain them anyways. The
WorkSession table tracks the sessions that you
worked. This database does not work for cross-midnight sessions, because
it uses the date and time concatenated together to calculate the time. It’s
schema is as so:
The next table is
JobPositions, which lists the various jobs that you
work with a description. This table exists to allow you to have
multiple jobs with different pay rates. Finally, the
PayRate table lists
your current pay rate with date that rate becomes effective.
Now the question is how do we do reporting on such a schema. Well we use
views to collect and process the information. The most important of the
vwWorkHours, because it performs the time math to find how long
you worked. The heart of this time math the sqlite,
modifies a timestring to be in a different format. Basically to get how
long one worked we convert the timestring to be a unix timestamp then
This gets us how long you worked, but how do we know what your payrate was for that session. Well to find that we execute a correlated query that looks like so:
This query looks for the first
PayRate entry for the current job whose
effective date is less than the date of the current
correlated to it. In the session pay rate view, this value is added to the
result set. It is then used to calculate the actual session pay in the
session pay view. This is done to avoid having another subquery here,
which I dislike the syntax of when they get too deep.
After all of this, you can now write your own queries to calculate how much you made at a job, how long it took you, and how much you made over a given time period. The other views given in the schema dump provide a good starting point for you to work from. Again you can get the schema dump here.