Strava Heart Rate Zones (Postgresql) in Grafana

Hi,

I have two tables with details about my last bike rides from the Strava API. The first one (strava) collects timestamp (start_date_local) and the unique bike ride id of each bike tour (bike_id):

start_date_local average_speed max_speed average_watts kilojoules suffer_score ride_id
2021-07-05 06:18:23+00 6.44 11.3 184.4 1148.8 145 5577229383
2021-07-03 07:27:07+00 6.221 10.5 172.5 1121.9 65 5566354380
2021-07-02 07:38:18+00 6.492 10.8 190.3 1186.7 132 5561835553
2021-06-29 07:19:21+00 6.22 11.8 177.7 1156.3 116 5546354060
2021-06-28 07:19:59+00 6.589 11.3 194.9 1154.6 165 5541394157

and another table (strava_heart_rate) has got the bike_id and the recorded time (content) in seconds of different heartrate zones (heart_rate_zone1):

id ride_id heart_rate_zone1 content
1 5577229383 0_to_112 0
2 5577229383 112_to_148 2724
3 5577229383 148_to_166 3013
4 5577229383 166_to_184 493
5 5577229383 184_to_-1 0
6 5566354380 0_to_112 33
7 5566354380 112_to_148 5906
8 5566354380 148_to_166 565
9 5566354380 166_to_184 0
10 5566354380 184_to_-1 0

I want to display in Grafana a graph that shows these different heart rate zones of each bike ride, like this here:

This is what I have so far:

SELECT 
    start_date_local AS "time",
    ride_id,
    heart_rate_zone1 AS metric,
    content AS value
    
FROM strava
JOIN strava_heart_rate USING (ride_id)
WHERE
  $__timeFilter(start_date_local) 
GROUP BY 1,2,3,4
ORDER BY 1

But unfortunately, it looks not right. I think Grafana sorts it by bike_id and not by the different heart zones:

Does anybody know how to change it?

Hi @dromsjel1

A general hint. I would view your data using the table panel and then explore Grafana’s internal transformations feature. This can help you troubleshoot the queries and get everything displayed the way you want it :man_shrugging: