Postgres and the Running Data Behind a Personal Milestone
One of the quieter perks of Stripe’s internal culture is its collection of recreational clubs, most of which live as Slack channels. Among them is a running group that regularly meets to tackle routes around the Mission district, where the office sits. By the time I joined, the club had already established some formidable loops, the most notorious being “Triple Peaks”: a route that heads south from the office, climbs Bernal Heights, cuts west through Glen Canyon Park, ascends Twin Peaks, and finally returns via the Castro and the Mission. My previous routes around Heroku’s office in SOMA covered respectable distances, but they did not approach the elevation gain or terrain variation of these new trails.
Measuring the Change with Postgres
Between the new social pressure and the new terrain, I certainly felt like I was running more over the past month. To confirm that impression, I decided to crunch the numbers using Postgres, pulling data from a database created for my Black Swan project, which periodically scrapes my social media services. My runs are logged with Strava, and the data comes from its API.
The goal was to run the same aggregation query against two different time periods: one for my time at Stripe, one for my previous job. For this, I used a Postgres prepared statement—an underused but simple tool that deserves a spot in any Postgres user’s toolkit. Prepared statements work via the PREPARE ... AS command to create a server-side object that is parsed and pre-analyzed, followed by an EXECUTE command that runs the planner and executes the query.
Here, I define a prepared statement named running_totals that takes a start date and a duration (an interval in Postgres terms) to look back over:
PREPARE running_totals AS
SELECT sum((metadata -> 'distance')::decimal) AS distance,
sum((metadata -> 'total_elevation_gain')::decimal) AS elevation
FROM events
WHERE type = 'strava'
AND metadata -> 'type' = 'Run'
AND date_trunc('day', occurred_at) <= date_trunc('day', $1::timestamptz)
AND date_trunc('day', occurred_at) >= date_trunc('day', $1::timestamptz)
- $2::interval;
The parameters are type cast with ::timestamptz and ::interval so that I can pass in strings as input. Postgres is particularly good at converting loosely formatted text like September 9, 2015 and 30 days into concrete times and durations for calculation.
For the period before Stripe, I used the date of my final run at my last job and measured 30 days back from there:
# EXECUTE running_totals('September 9, 2015', '30 days');
distance | elevation
----------+-----------
113854.1 | 0.0
For the Stripe period, I measured from today, with the same duration:
# EXECUTE running_totals('October 24, 2015', '30 days');
distance | elevation
----------+-----------
219835.5 | 5176.1
The Results
The difference is notable: 220 km versus 114 km—an almost 100% increase in distance. Beyond the raw mileage, the elevation numbers show a marked improvement, with over 5 km of vertical gain accumulated in 30 days. That reflects the shift from flat waterfront runs in SOMA to the hillier terrain near the Mission. It remains to be seen whether these figures hold up beyond the honeymoon phase, but the data offers a clear, encouraging picture of the change.
