Creating a Database to Track How Long You Worked

2016 June 23

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 PayRate, JobPositions, 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:

CREATE TABLE WorkSession (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    jobId      INTEGER,
    date       DATE                DEFAULT CURRENT_DATE,
    startTime  VARCHAR(10),
    endTime    VARCHAR(10),
    thingsDone TEXT,
    FOREIGN KEY (jobId) REFERENCES JobPositions (id)
);

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.

CREATE TABLE PayRate (
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    jobId         INTEGER             DEFAULT 1,
    dateEffective DATE                DEFAULT CURRENT_DATE,
    payRate       DOUBLE PRECISION,
    FOREIGN KEY (jobId) REFERENCES JobPositions (id)
);

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 views is vwWorkHours, because it performs the time math to find how long you worked. The heart of this time math the sqlite, strftime which 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 subtract them.

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:

SELECT payRate
FROM PayRate pr
WHERE
    pr.jobId = wh.jobId AND
    strftime('%s', pr.dateEffective) < strftime('%s', wh.date)
ORDER BY strftime('%s', pr.dateEffective) DESC
LIMIT 1;

This query looks for the first PayRate entry for the current job whose effective date is less than the date of the current WorkSession being 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.