Gamify Personal Fitness Using A PostgreSQL Database

Gamification has exploded in recent years. Tech entrepreneur, philanthropist and internet personality, Elon Musk, attributes the lack of gamification as a part of why our current education system is failing. This inspired him to create his private school Ad Astra to gamify learning and spark curiosity in STEM fields for the next generation. So what is gamification anyway? It is the process of applying principles from gaming to other areas.

The average person spends 2 hours and 24 minutes on social media each day. Engineers at these big tech companies study the psychology of slot machines at Casino's and implement similar principles into their products. The swipe up to refresh mechanic on Twitter sends the same rush of dopamine, as pulling the level on that slot machine. These products, like games, create positive feedback loops that reward our behaviour and demand our attention.

But why?

Now if we had the autonomy and agency to reclaim this lost time and spend it more productively, would we? This was a question I looked to answer myself. A key and compelling feature of video games, mobile games, and social media is tracking. Whether it be gold coins, gems, experience, likes, or views, these platforms track our accomplishments through arbitrary metrics that often have no intrinsic value. Yet we still keep playing, swiping, and scrolling. I decided to start applying tracking metrics to areas of my life I wish to improve, exercise was one of these.

I chose to implement a PostgreSQL database to track the personal performance of my exercise. I am not so vain as to claim to be an athlete, but I complete a 5km run each day. I do this to maintain sufficient vitamin D from its natural source (the sun), cardio and general fitness, and mental wellbeing. One way I gamify the process is by creating an artificial scarcity of rewards. I only allow myself to listen to my favourite podcast while I am running. Nowadays, most mobile phones or smartwatches have step tracking functionality. This can be used to approximate the distance for each run. If not, you can use online tools to measure your runs. Now finally, onto the technical side of things.

Using A PostgreSQL Database To Track Personal Fitness

First, we create a database to store the analytics for our exercises in. This can be done through the terminal on a Linux system using the createdb command:

createdb exercise

Then we connect to the database.

psql exercise

Then we create a table to store the run data in. This table has four fields; an id as a primary key, date stores when we ran, distance is the length of the run in kilometres and time is how long it took in minutes.

create table runs (
    id serial primary key,
    date date not null,
    distance integer not null,
    time integer not null
);

We use the serial macro to create an auto-incrementing integer value sequence for our primary key. In Laymon's terms, a range of numbers; 1,2,3,4,... up to the length of our table.

We can add data to the table once we complete a run. Let's take the following example of a 5 km run that took 32 minutes on the 27th of January 2021. Notice we do not have to specify the id, this is done automatically with the serial macro.

insert into runs 
(date,distance,time)
values ('2021-01-27',5,32);

We write the date following the ISO 8061 conventions.

And with that, we have our database complete. This table can be viewed with the following command:

select * from runs;

After a couple of weeks following an exercise routine of daily runs, we can populate our table with our progression.

In terms of gamification, we can set milestones or achievements for ourselves. Say, I want to run 100km this year. The query below displays the total distance we have run over the year 2021.

select sum(distance) from runs 
where date <= '2021-01-01' and date <= '2021-12-31';

We can store that query as a view to monitor our progress towards this milestone.

create view goal
select sum(distance) from runs 
where date <= '2021-01-01' and date <= '2021-12-31';

In the future, a more complex analysis could be performed. Since we included dates, we can perform a time series analysis of our runs, and track our improvements over multiple metrics over time. For example, our speed (velocity), which can be measured as distance over time.

Backups

Now, it is always important to store backups. We cannot rely on this information being safely stored on a single local machine forever. I have chosen to use the pg_dump command-line utility and git to create backups on the cloud for recovery.

First, we create initialize git in the repository.

git init

Then we add a remote origin <remote-origin> (Insert your online Git repository URL in this placeholder).

git remote add origin <remote-origin>

This can be quite an arduous task, so for efficiency, I decided to automate it into a script.

#! /bin/bash
TODAY=`date --rfc-3339=date`
pg_dump exercise > exercise.dump
git add -A
git commit -m "Backup: ${TODAY}"
git push origin main

On Linux systems, we must change the permissions so that our bash script is executable. The

chmod

chmod u+x backup.sh

Now it can be run with this:

./backup.sh

TL;DR

• Gamification can be used to make us more productive
• Use PostgreSQL to track the progress of our exercise
• Write Queries to perform analysis of our performance
• Use git to back up our data to the cloud
• automate our git commits for efficiency

I will admit this is by far not the most elegant solution to the problem. I don't expect to see Fitbit shaking in their boots. As a developer, I have managed to merge my passion for programming into my exercise. Part of me is more motivated to run, so I can gather enough data to perform some meaningful analysis.

Previously published at https://hashnode.com/post/gamify-exercise-with-postgesql-ckkfa58mm00m7fps1bf0o89xz