utilities/gadgetbridge_to_influxdb#14: Sleep Data Support

The tables being written into are

$ grep "INSERT INTO" /tmp/sqlite_dump2 | awk '{print $3}' | uniq
android_metadata
USER_ATTRIBUTES
USER
DEVICE_ATTRIBUTES
DEVICE
MI_BAND_ACTIVITY_SAMPLE
HUAMI_STRESS_SAMPLE
HUAMI_SPO2_SAMPLE
HUAMI_HEART_RATE_MANUAL_SAMPLE
HUAMI_PAI_SAMPLE
BATTERY_LEVEL

You'd think it'd be MI_BAND_ACTIVITY_SAMPLE but it doesn't have any sleep related columns.

Oh.... UNLESS... what if we need to be looking at a combination of RAW_KIND and INTENSITY?

The GB interface says I slept from 01:02 to 08:59 so generating some timestamps in that period

$ date --date "01:05" +'%s'
1693094700
$ date --date "03:05" +'%s'
1693101900

Pulling a sample out

sqlite> select * FROM MI_BAND_ACTIVITY_SAMPLE WHERE TIMESTAMP > 1693094700 AND TIMESTAMP < 1693101900 LIMIT 50;
TIMESTAMP|DEVICE_ID|USER_ID|RAW_INTENSITY|STEPS|RAW_KIND|HEART_RATE
1693094760|1|1|0|0|240|80
1693094820|1|1|0|0|240|81
1693094880|1|1|0|0|240|79
1693094940|1|1|0|0|240|81
1693095000|1|1|0|0|240|81
1693095060|1|1|0|0|240|81
1693095120|1|1|0|0|240|81
1693095180|1|1|0|0|240|80
1693095240|1|1|0|0|240|82
1693095300|1|1|0|0|240|83

What's interesting is there are periods where RAW_INTENSITY change

1693095960|1|1|16|0|240|83
1693096020|1|1|13|0|240|83

There are also periods where RAW_KIND changes though

1693096080|1|1|0|0|250|81

It's occurred to me, it's stupid to try and reverse engineer this when there's an expert source available: Gadgetbridge is OSS after all.

It looks like the value of RAW_KIND is checked here

        for (final HuamiExtendedActivitySample sample : samples) {
            if (sample.getRawKind() == TYPE_SLEEP) {
                // Band reports type sleep regardless of sleep type, so we map it to custom raw types
                // These thresholds are arbitrary, but seem to somewhat match the data that's displayed on the band

                sample.setDeepSleep(sample.getDeepSleep() & 127);
                sample.setRemSleep(sample.getRemSleep() & 127);

                if (sample.getRemSleep() > 55) {
                    sample.setRawKind(TYPE_CUSTOM_REM_SLEEP);
                    sample.setRawIntensity(sample.getRemSleep());
                } else if (sample.getDeepSleep() > 42) {
                    sample.setRawKind(TYPE_CUSTOM_DEEP_SLEEP);
                    sample.setRawIntensity(sample.getDeepSleep());
                } else {
                    sample.setRawIntensity(sample.getSleep());
                }
            }
        }
    }

TYPE_SLEEP is defined further up

    public static final int TYPE_SLEEP = 120;
    public static final int TYPE_CUSTOM_DEEP_SLEEP = TYPE_SLEEP + 1;
    public static final int TYPE_CUSTOM_REM_SLEEP = TYPE_SLEEP + 2;

Although the code checks for 120, it looks like it overrides the type when writing into it's own database (so we can use RAW_KIND to identify the sleep type without having to reimplement the same logic)

So, we want rows where RAW_KIND is between 120 and 122 (inclusive)

sqlite> select * FROM MI_BAND_ACTIVITY_SAMPLE WHERE TIMESTAMP > 1693094700 AND TIMESTAMP < 1693101900 and RAW_KIND BETWEEN 120 AND 122; 
TIMESTAMP|DEVICE_ID|USER_ID|RAW_INTENSITY|STEPS|RAW_KIND|HEART_RATE
1693101540|1|1|0|0|122|60
1693101660|1|1|0|0|121|61
1693101720|1|1|0|0|122|61
1693101780|1|1|0|0|122|63
1693101840|1|1|0|0|122|63

That's a lot fewer than I was expecting - should look at what it's doing with intensity then.

I need to step away from this for a while, but it looks like the answer can probably be found here: That's where GB calculates sleep stats for display in the charts - makes more sense to interpret from there (the output side) than the Huami specific stuff (the input side).

Wait... I'm an idiot.

of course that query only returned a few samples - it was specifically filtered to run between a couple of timestamps.

select * FROM MI_BAND_ACTIVITY_SAMPLE WHERE TIMESTAMP > 1693094700 AND TIMESTAMP and RAW_KIND BETWEEN 120 AND 122;

Returns many more. Go figure.

It looks like the GB code works backwards, so if we want duration, we need to use LAG() to grab the last sleep timestamp.

I'll think about how I want to represent the sleep data once I get back, I was never quite happy with the way we did it in zepp_to_influxdb

The other thing we need to do, is work out what RAW_INTENSITY denotes in this context:

sqlite> select TIMESTAMP, RAW_INTENSITY, RAW_KIND FROM MI_BAND_ACTIVITY_SAMPLE WHERE TIMESTAMP > 1693094700 AND TIMESTAMP and RAW_KIND BETWEEN 120 AND 122 AND RAW_INTENSITY <> 0; 
TIMESTAMP|RAW_INTENSITY|RAW_KIND
1693108440|6|121
1693109640|7|121
1693118220|10|121

There's a bitwise AND performed on the value, and then under some circumstances the value is overridden

            if (sample.getRawKind() == TYPE_SLEEP) {
                // Band reports type sleep regardless of sleep type, so we map it to custom raw types
                // These thresholds are arbitrary, but seem to somewhat match the data that's displayed on the band

                sample.setDeepSleep(sample.getDeepSleep() & 127);
                sample.setRemSleep(sample.getRemSleep() & 127);

                if (sample.getRemSleep() > 55) {
                    sample.setRawKind(TYPE_CUSTOM_REM_SLEEP);
                    sample.setRawIntensity(sample.getRemSleep());
                } else if (sample.getDeepSleep() > 42) {
                    sample.setRawKind(TYPE_CUSTOM_DEEP_SLEEP);
                    sample.setRawIntensity(sample.getDeepSleep());
                } else {
                    sample.setRawIntensity(sample.getSleep());
                }
            }
        }

The bit I can't quite get my head around - the RAW_KIND is 121 in those results, so it should be a deep sleep. I don't see deep sleep in GB's graphs at that time.

Ahhh, that'd be because it only lasted about 5 mins

sqlite> select TIMESTAMP, RAW_INTENSITY, RAW_KIND FROM MI_BAND_ACTIVITY_SAMPLE WHERE TIMESTAMP > 1693108438 AND TIMESTAMP < 1693109640 and RAW_KIND BETWEEN 120 AND 122 ; 
TIMESTAMP|RAW_INTENSITY|RAW_KIND
1693108440|6|121
1693108740|0|122
1693109400|0|122
1693109460|0|122
1693109520|0|122
1693109580|0|122

OK, so lets start simple - pull out the sleep associated entries and insert them into a field indicating sleep + level, will see what that looks like when charted out

It's not very graph friendly, but this seems to work

    data_query = ("SELECT TIMESTAMP, DEVICE_ID, RAW_INTENSITY, RAW_KIND"
        " FROM MI_BAND_ACTIVITY_SAMPLE " 
        f"WHERE TIMESTAMP >= {query_start_bound} "
        "AND RAW_KIND BETWEEN 120 AND 122 "
        "ORDER BY TIMESTAMP ASC")  
    res = cur.execute(data_query)
    for r in res.fetchall():

        if r[3] == 120:
            sleep_type = "light"
        elif r[3] == 121:
            sleep_type = "deep"
        elif r[3] == 122:
            sleep_type = "REM"


        row_ts = r[0] * 1000000000
        row = {
                "timestamp": row_ts, # Convert to nanos
                "fields" : {
                    "intensity" : r[2],
                    f"{sleep_type}_sleep" : 1
                    },
                "tags" : {
                    "device" : devices[f"dev-{r[1]}"],
                    "sample_type" : "sleep"
                    }
            }

        results.append(row)     

I get increases about where the Gadgetbridge graph shows them (it's also just occurred to me that GB skips anything with a duration < 5 mins, which is why I couldn't match that other deep sleep)

So, the question now becomes how best to write it out to InfluxDB.

I think the answer is probably to write out 1 minute intervals (like we've just done in #13 for stress).

That way it's relatively easy to generate a graph showing state, as well as to calculate proportion of time spent in that state.

As noted above, Gadgetbridge seems to look backwards rather than forwards (i.e. that deep sleep marker marks the end of a period of deep sleep, not the start). So, we probably want to work backwards through the records

But, to do that, we also need to figure out how it records that I've woken up (so that we don't report that the whole day was spent asleep)

OK, so it looks like the watch will log me waking up with RAW_KIND 112 and RAW_INTENSITY 1

sqlite> select * FROM MI_BAND_ACTIVITY_SAMPLE WHERE RAW_KIND=112 and RAW_INTENSITY=1;
TIMESTAMP|DEVICE_ID|USER_ID|RAW_INTENSITY|STEPS|RAW_KIND|HEART_RATE
1692682020|1|1|1|0|112|60
1692682080|1|1|1|0|112|53
1692682140|1|1|1|0|112|56
1692771420|1|1|1|0|112|68
1692775380|1|1|1|0|112|70
1692926760|1|1|1|0|112|56
1692927660|1|1|1|0|112|74
1692927900|1|1|1|0|112|75
1692930420|1|1|1|0|112|74
1692931560|1|1|1|0|112|255
1692943080|1|1|1|0|112|52
1692945900|1|1|1|0|112|74
1693036320|1|1|1|0|112|59
1693081020|1|1|1|0|112|97
1693085820|1|1|1|0|112|88
1693087320|1|1|1|0|112|90
1693117440|1|1|1|0|112|57

I found it by checking waking times in Gadgetbridge and then looking for entries around that time.

With that in mind then, to work out time spent asleep we want to:

• Query for RAW kind 120, 121, 122 or 112
• Order by TIMESTAMP
• If the next point has raw_kind 112 don't insert sleeps

This does that

SELECT
    TIMESTAMP, 
    DEVICE_ID, 
    RAW_INTENSITY, 
    RAW_KIND,
    LEAD (TIMESTAMP, 1) OVER (PARTITION BY DEVICE_ID, USER_ID ORDER BY TIMESTAMP) NEXT_TS,
    LEAD (RAW_KIND, 1) OVER (PARTITION BY DEVICE_ID, USER_ID ORDER BY TIMESTAMP) NEXT_KIND


FROM MI_BAND_ACTIVITY_SAMPLE 
WHERE 
    ((RAW_KIND=112 AND RAW_INTENSITY=1) 
    OR RAW_KIND BETWEEN 120 AND 122)
ORDER BY TIMESTAMP;

But, there's still something we're missing.

If we look at the entries around that 23:02 timestamp:

22:49 1693086540|1|0|122|1693087080|122
22:58 1693087080|1|0|122|1693087320|112
23:02 1693087320|1|1|112|1693101540|122
02:59 1693101540|1|0|122|1693101660|121
03:01 1693101660|1|0|121|1693101720|122

There's just no way that I was awake from 23:02 -> 02:59. Gadgetbridge's report says I fell asleep at 01:02 (which still feels too late, but more reasonable).

There must be another entry that we're not accounting for here

Looking around the 01:02 mark:

sqlite> select * FROM MI_BAND_ACTIVITY_SAMPLE WHERE TIMESTAMP >= 1693094460 AND TIMESTAMP <= 1693094580;
TIMESTAMP|DEVICE_ID|USER_ID|RAW_INTENSITY|STEPS|RAW_KIND|HEART_RATE
1693094460|1|1|15|0|240|82
1693094520|1|1|10|0|249|86
1693094580|1|1|0|0|240|81

The middle timestamp is 01:02, suggesting that RAW_KIND 249 might indicate falling asleep (although, if so, what's that intensity?)

SELECT
    TIMESTAMP, 
    DEVICE_ID, 
    RAW_INTENSITY, 
    RAW_KIND,
    LEAD (TIMESTAMP, 1) OVER (PARTITION BY DEVICE_ID, USER_ID ORDER BY TIMESTAMP) NEXT_TS,
    LEAD (RAW_KIND, 1) OVER (PARTITION BY DEVICE_ID, USER_ID ORDER BY TIMESTAMP) NEXT_KIND


FROM MI_BAND_ACTIVITY_SAMPLE 
WHERE 
    ((RAW_KIND=112 AND RAW_INTENSITY=1) 
    OR RAW_KIND BETWEEN 120 AND 122
    OR RAW_KIND=249
    )
ORDER BY TIMESTAMP;

I'm not convinced this is right, but needs graphing out to be sure

I think, of all the things we've added, this one definitely needs playing with in Grafana before we consider this done.

It's possible to graph out, but I'm not sure that I believe the values

I don't want to rip the code back out, but I also don't want these questionable figures to be taken as read, so I'm going to add an experimental flag and hide this behind it.

I don't want this to block the release that we're (otherwise) storming towards, so I've updated the issue title and marked as experimental.

Will raise a follow-up issue once I've time to revisit this