search

dogsheep / healthkit-to-sqlite

Convert an Apple Healthkit export zip to a SQLite database
https://datasette.io/tools/healthkit-to-sqlite
Apache License 2.0
191 stars 9 forks source link

Some workout columns should be float, not text #12

Open simonw opened 3 years ago

simonw commented 3 years ago

Columns duration, totalDistance and totalEnergyBurned should be converted to float.

https://github.com/dogsheep/healthkit-to-sqlite/blob/71e36e1cf034b96de2a8e6652265d782d3fdf63b/healthkit_to_sqlite/utils.py#L50-L57

Mjboothaus commented 2 years ago

Hi Simon -- just experimenting with your excellent software!

Up to this point in time I have been using the (paid) HealthFit App to export my workouts from my Apple Watch, one walk at the time into either .GPX or .FIT format and then using another library to suck it into Python and eventually here to my "Emmaus Walking" app:

https://share.streamlit.io/mjboothaus/emmaus_walking/emmaus_walking/app.py

I just used healthkit-to-sqlite to convert my export.zip file and it all "just worked".

I did notice the issue with various numeric fields being stored in the SQLite db as TEXT for now and just thought I'd flag it - but you're already self-reported this issue.

Keep up the great work!

I was curious if you have any thoughts about periodically exporting "export.zip" and how to just update the SQLite file instead of re-creating it each time. Hopefully Apple will give some thought to managing this data in a more sensible fashion as it grows over time. Ideally one could pull it from iCloud (where it is allegedly being backed up).

Mjboothaus commented 2 years ago

P.s. wondering if you have explored using the spatialite functionality with the location data in workouts?

simonw commented 2 years ago

Unfortunately I don't think updating the database is practical, because the export doesn't include unique identifiers which can be used to update existing records and create new ones. Recreating from scratch works around that limitation.

I've not explored workouts with SpatiaLite but that's a really good idea.

Mjboothaus commented 2 years ago

In terms of unique identifiers - could you use values stored in HKMetadataKeySyncIdentifier?