utilities/gadgetbridge_to_influxdb#16: SpO2 Data can't be collected from a Xiaomi Smart Band (timestamp value out of range)



Issue Information

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

Milestone: vnext
Created: 27-Feb-24 08:42



Description

A user raised Github 2 to report that they were getting write errors from InfluxDB:

HTTP response body: 
{"code":"invalid","message":"unable to parse 
'gadgetbridge,device=Xiaomi\\ Smart\\ Band\\ 7\\ 0001,type_num=0 spo2=98i 1696205380000000000000':do_not_litter: strconv.ParseInt: parsing \"1696205380000000000000\": value out of range\n
}

That timestamp definitely is too long

The code expects milliseconds:

spo2_data_query = ("SELECT TIMESTAMP, DEVICE_ID, TYPE_NUM, SPO2 FROM HUAMI_SPO2_SAMPLE "
        f"WHERE TIMESTAMP >= {query_start_bound_ms} "
        "ORDER BY TIMESTAMP ASC")

After retrieval, it converts them to nanos:

    for r in res.fetchall():
        row_ts = r[0] * 1000000 # Convert to nanos
        row = {
                "timestamp": row_ts, 
                "fields" : {
                    "spo2" : r[3]
                    },
                "tags" : {
                    "type_num" : r[2],
                    "device" : devices[f"dev-{r[1]}"]
                    }
            }
        results.append(row)

If we take the value in the output and divide by that though

1696206330000000000000 / 1000000 = 1696206330000000

It needs dividing by another million to get a second precision timestamp: it's a micro-second timestamp.



Toggle State Changes

Activity


assigned to @btasker

changed the description

changed title from SpO2 Data can't be collected from a Xiaomi Smart Band to SpO2 Data can't be collected from a Xiaomi Smart Band{+ (timestamp value out of range)+}

One thing I should probably check: is this unexpected, or is it me screwing up? Is it possible that variable I named query_start_bound_ms was meant as query_start_bound_MICROs rather than MILLIs?

Need to check some of the other data that uses ms to confirm (my original notes might also show where I got millis from).

In terms of a fix, it probably makes more sense to assume that I haven't screwed up and do some bounds checking:

  • Retrieve as we do now
  • Convert to nanos as we do now
  • If the value is out of bounds, convert the original from a different precision

The issue with that, though, is that the query will be wrong: it'll always return all data (because a ms timestamp will be smaller than all the us timestamps). So, in addition to the above, we should

  • Have the script print a warning if it has to reconvert
  • Expose an environment/config variable which can be used to set the precision (adjusting query precision etc as a result)

my original notes might also show where I got millis from

Ah, it was in utilities/gadgetbridge_to_influxdb#10 :

Screenshot_20240227_084830

The measurements that came across from my watch in that issue, though, were all the result of manually triggering SpO2 checks - there didn't seem to be an option to turn periodic monitoring on.

It'd be fairly weird for the two to use different precisions but :shrug: