bentasker
commented
6 months ago Are those values actually invalid/too big
Yeah they are, but it should be fixable.
InfluxDB expects nanosecond precision timestamps. Gadgetbridge uses a mix of second and millisecond (depending on the db table in question).
In this case, we're expecting 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")We then convert that 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 your output and divide by that, we get
1696206330000000000000 / 1000000 = 1696206330000000If that were ms, your readings would be from the year 55720 - it needs dividing by another million to get the real date.
But, that also means that the query will be wrong for your data - it's basically going to return all points in the data, ever. That's less than ideal.
Do you have a copy of Gadgetbridge's database to hand? Could you run
echo "SELECT TIMESTAMP, DEVICE_ID, TYPE_NUM, SPO2 FROM HUAMI_SPO2_SAMPLE ORDER BY TIMESTAMP ASC LIMIT 5;" | sqlite3 [database filename]and share the output? Just want to make sure I've narrowed it down properly before implementing the wrong thing.
Also, could you give me a pointer on how to setup grafana, just adding a influxql-connection and adding the dashboard.json seems not to be enough, it complains about Status: 500. Message: database not found: telegraf
The dashboard expects that your database will be called telegraf - the database name is included in the queries. If you want to use a different database name, the easiest way's probably to update it in the JSON before importing:
sed -i 's/telegraf/mydb/g' database.json
Hi,
i finally got around to install influxdb+grafana, at least some data is vislible in influxdb :+1:
Aside that gadgetbridge just fixed a bug (while i was writing another bug here ;)) for my xiaomi that caused "no data but battery" for the last two weeks, what's missing is the O2 value, i assume the reason are the (several thousands of lines) errors on import:
The batch item wasn't processed successfully because: (400) Reason: Bad Request HTTP response headers: HTTPHeaderDict({'Content-Type': 'application/json; charset=utf-8', 'X-Influxdb-Build': 'OSS', 'X-Influxdb-Version': 'v2.7.5', 'X-Platform-Error-Code': 'invalid', 'Date': 'Mon, 26 Feb 2024 14:36:51 GMT', 'Transfer-Encoding': 'chunked'}) HTTP response body: {"code":"invalid","message":"unable to parse 'gadgetbridge,device=Xiaomi\\ Smart\\ Band\\ 7\\ 0001,type_num=0 spo2=98i 1696205380000000000000': strconv.ParseInt: parsing \"1696205380000000000000\": value out of range\nunable to parse 'gadgetbridge,device=Xiaomi\\ Smart\\ Band\\ 7\\ 0001,type_num=0 spo2=96i 1696205730000000000000': strconv.ParseInt: parsing \"1696205730000000000000\": value out of range\nunable to parse 'gadgetbridge,device=Xiaomi\\ Smart\\ Band\\ 7\\ 0001,type_num=0 spo2=96i 1696206030000000000000': strconv.ParseInt: parsing \"1696206030000000000000\": value out of range\nunable to parse 'gadgetbridge,device=Xiaomi\\ Smart\\ Band\\ 7\\ 0001,type_num=0 spo2=98i 1696206330000000000000': strconv.ParseInt: parsing \"1696206330000000000000\":Are those values actually invalid/too big, or is this something you can easily fix...? Would be very interesting for me, the data goes back for at least eight month, but i got diagnosed with sleep apnea in December 2023...
Also, could you give me a pointer on how to setup grafana, just adding a influxql-connection and adding the dashboard.json seems not to be enough, it complains about Status: 500. Message: database not found: telegraf, do i need to add an influxdb-source into telegraf and then "connect" grafana to telegraf...?
Kinds Regards,
/hp