utilities/gadgetbridge_to_influxdb#6: Calculate non-sleep stress scores

Issue Information

Issue Type: issue
Status: closed
Reported By: btasker
Assigned To: btasker

Milestone: v0.4
Created: 26-Aug-23 10:48


When creating graphs for the Zepp data, I identified that the [Stress proportions graphs)(https://www.bentasker.co.uk/posts/blog/software-development/extracting-data-from-zepp-app-for-local-storage-in-influxdb.html#stress_proportions) are really not that informative when based on the raw data.

They include sleeping hours, which (normally) will drag the average down. So, you can have an average score despite having hugely stressful days as long as it's not disturbing your sleep.

Whilst the overall average is a useful measure to have, it's also useful to have a measure of the value outside of sleeping hours.

Toggle State Changes


assigned to @btasker

With the Zepp data, I used a Flux query:

import "date"

from(bucket: "health")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "zepp")
  |> filter(fn: (r) => r["_field"] == "current_stress_level")
  |> filter(fn: (r) => r["stress"] == "point_in_time")

  // Exclude sleeping hours
  |> filter(fn: (r) => not contains(value: date.hour(t: r._time), 
                       set: [0,1,2,3,4,5,6]))
  // Skip missed reads
  |> filter(fn: (r) => r._value > 0)

  // Turn the reads into thresholds and counts
  // the thresholds used come from the description
  // in the apps UI
  |> map(fn: (r) => ({
    _time: r._time,
    _value: 1,
    _field: if r._value <= 39
               then "relaxed"
               else if r._value >= 40 and r._value <= 59
                    then "normal"
                    else if r._value >= 60 and r._value <= 79
                         then "medium"
                         else if r._value > 80
                              then "high"
                              else "unknown"

  // Sum the values so we can get the relative proportions
  |> sum()
  |> keep(columns: ["_field", "_value"])

I'd quite like, though, not to need that complexity at query time - it'd be better if the script could write a second field which excludes normal sleeping hours (although it'd be ideal to do this dynamically, I don't think that there's currently a way for us to tell that the wearer is asleep).


mentioned in commit 680f5c665e1eaecedef628cb84beced9fc6e730d

Commit: 680f5c665e1eaecedef628cb84beced9fc6e730d 
Author: B Tasker                            
Date: 2023-08-26T11:59:41.000+01:00 


Add new field stress_exc_sleep utilities/gadgetbridge_to_influxdb#6

This adds a new calculated field containing reported stress scores, but only for hours not considered sleeping hours (defined in env var SLEEPING_HOURS).

This allows a daily average stress score to be calculated which excludes sleeping hours.

+16 -0 (16 lines changed)

We can now calculate a daily mean with

SELECT mean("stress_exc_sleep") AS "mean_stress_exc_sleep" FROM "testing_db"."autogen"."gadgetbridge" WHERE time > :dashboardTime: AND time < :upperDashboardTime: GROUP BY time(1d) 

mentioned in issue #2