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 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. Ad Astra The average person spends 2 hours and 24 minutes on 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. social media 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 . 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. tracking 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 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 to measure your runs. Now finally, onto the technical side of things. favourite podcast online tools 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 command: createdb 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 as a primary key, stores when we ran, the length of the run in kilometres and how long it took in minutes. id date distance is time is create table runs ( id serial primary key, date date not , distance integer not , time integer not ); null null null We use the 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. serial macro We can add data to the table once we complete a run. Let's take the following example of a run that took on the . Notice we do not have to specify the , this is done automatically with the serial macro. 5 km 32 minutes 27th of January 2021 id insert into runs (date,distance,time) values ( , , ); '2021-01-27' 5 32 We write the date following the conventions. ISO 8061 And with that, we have our database complete. This table can be viewed with the following command: select * runs; from 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, . The query below displays the total distance we have run over the year 2021. I want to run 100km this year select sum(distance) runs where date <= and date <= ; from '2021-01-01' '2021-12-31' We can store that query as a to monitor our progress towards this milestone. view create view goal select sum(distance) runs where date <= and date <= ; from '2021-01-01' '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 command-line utility and to create backups on the cloud for recovery. pg_dump git First, we create initialize in the repository. git 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. TODAY= pg_dump exercise > exercise.dump git add -A git commit -m git push origin main #! /bin/bash `date --rfc-3339=date` "Backup: ${TODAY}" On Linux systems, we must change the permissions so that our bash script is executable. The command below modifies the permissions such that it can be run on the system by the current user. 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 to track the progress of our exercise PostgreSQL Write Queries to perform analysis of our performance Use to back up our data to the cloud git automate our commits for efficiency git 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
Share Your Thoughts