Command-line tool to convert Apple HealthKit data to a SQLite database.

Overview

healthkit-to-sqlite

ci crates.io Apache 2.0 License: MIT

Command-line tool to convert Apple HealthKit data to a SQLite database.

workout-routes-examples

Getting Started

  1. Open the Health app on your iOS device.
  2. Click your profile icon in the top-right corner.
  3. Click the "Export All Health Data" button.
  4. Share the resulting ZIP archive to your computer.
  5. Run healthkit-to-sqlite on the exported ZIP archive.
# You need to install Rust https://rustup.rs/
cargo install healthkit-to-sqlite-cli
healthkit-to-sqlite export.zip sqlite://healthkit.db

Please create an issue for all bugs, feature requests, or feedback.

Datasette

You can use https://datasette.io/ to view and explore the resulting SQLite database file. Install the https://datasette.io/plugins/datasette-geojson-map plugin to visualize the workout routes data on a map.

datasette install datasette-geojson-map
datasette healthkit.db

Example Queries

Here are a few example SQL queries to help you start exploring your HealthKit data:

  • Total walking, running, and hiking workout duration in hours for the month of December 2022:
select
  sum(duration) / 60 as total_duration
from
  workout
where
  (
    creationDate between '2022-12-01' and '2022-12-31'
  )
  and (
    workoutActivityType = 'HKWorkoutActivityTypeWalking' or
    workoutActivityType = 'HKWorkoutActivityTypeRunning' or
    workoutActivityType = 'HKWorkoutActivityTypeHiking'
  );
  • Total distance covered in miles across all workouts for the month of December 2022:
select
  sum(
    json_extract(
      workoutStatistics,
      "$.HKQuantityTypeIdentifierDistanceWalkingRunning.sum"
    )
  ) as total_distance_miles
from
  workout
where
  (
    creationDate between '2022-12-01'
    and '2022-12-31'
  );
  • The JSON data in the workoutStatistics column looks like:
{
    "HKQuantityTypeIdentifierActiveEnergyBurned": {
        "endDate": "2019-12-27 13:10:51 -0800",
        "startDate": "2019-12-27 12:30:15 -0800",
        "sum": 135.70199584960938,
        "type": "HKQuantityTypeIdentifierActiveEnergyBurned",
        "unit": "Cal"
    },
    "HKQuantityTypeIdentifierBasalEnergyBurned": {
        "endDate": "2019-12-27 13:10:51 -0800",
        "startDate": "2019-12-27 12:30:15 -0800",
        "sum": 67.24250030517578,
        "type": "HKQuantityTypeIdentifierBasalEnergyBurned",
        "unit": "Cal"
    },
    "HKQuantityTypeIdentifierDistanceWalkingRunning": {
        "endDate": "2019-12-27 13:10:51 -0800",
        "startDate": "2019-12-27 12:30:15 -0800",
        "sum": 1.4269200563430786,
        "type": "HKQuantityTypeIdentifierDistanceWalkingRunning",
        "unit": "mi"
    }
}

Decisions

  • Only the Record, Workout, and ActivitySummary elements are currently exported.
  • Record elements are inserted to a table with a name matching the value of the element's type attribute.
  • Workout elements are inserted to a table named "Workout".
    • The descendent workoutEvent and workoutStatistics elements are represented as JSON columns.
    • The descendent workoutRoute element is converted to a GeoJSON LineString and stored in a JSON column named "geometry" for easy integration with https://datasette.io/plugins/datasette-geojson-map.
  • ActivitySummary elements are inserted as rows to a table named "ActivitySummary".
  • In an attempt to future proof against Apple adding, removing, or changing element attributes, the code only assumes the existence of a limited number of attributes:
    • Record elements must have a type attribute.
    • Workout elements must have a workoutActivity attribute.
    • MetadataEntry elements must have key and value attributes.
    • FileReference elements must have a path attribute.

License

This project is licensed under either of

at your option.

You might also like...
Scalable and fast data store optimised for time series data such as financial data, events, metrics for real time analysis

OnTimeDB Scalable and fast data store optimised for time series data such as financial data, events, metrics for real time analysis OnTimeDB is a time

🧰 The Rust SQL Toolkit. An async, pure Rust SQL crate featuring compile-time checked queries without a DSL. Supports PostgreSQL, MySQL, SQLite, and MSSQL.

SQLx 🧰 The Rust SQL Toolkit Install | Usage | Docs Built with ❤️ by The LaunchBadge team SQLx is an async, pure Rust† SQL crate featuring compile-tim

Ergonomic bindings to SQLite for Rust

Rusqlite Rusqlite is an ergonomic wrapper for using SQLite from Rust. It attempts to expose an interface similar to rust-postgres. use rusqlite::{para

SQLite clone from scratch in Rust
SQLite clone from scratch in Rust

Rust-SQLite (SQLRite) Rust-SQLite, aka SQLRite , is a simple embedded database modeled off SQLite, but developed with Rust. The goal is get a better u

Some bunch of test scripts to generate a SQLite DB with 1B rows in fastest possible way

To find out the fastest way to create an SQLite DB with 1B random rows.

Provides a Rust-based SQLite extension for using Hypercore as the VFS for your databases.

SQLite and Hypercore A Rust library providing SQLite with an virtual file system to enable Hypercore as a means of storage. Contributing The primary r

ChiselStore is an embeddable, distributed SQLite for Rust, powered by Little Raft.

ChiselStore ChiselStore is an embeddable, distributed SQLite for Rust, powered by Little Raft. SQLite is a fast and compact relational database manage

Interface to SQLite

SQLite The package provides an interface to SQLite. Example Open a connection, create a table, and insert some rows: let connection = sqlite::open(":m

Rusqlite is an ergonomic wrapper for using SQLite from Rust

Rusqlite Rusqlite is an ergonomic wrapper for using SQLite from Rust. It attempts to expose an interface similar to rust-postgres. use rusqlite::{para

Comments
Releases(v0.0.4)
Owner
Justin Shrake
Justin Shrake
🐸Slippi DB ingests Slippi replays and puts the data into a SQLite database for easier parsing.

The primary goal of this project is to make it easier to analyze large amounts of Slippi data. Its end goal is to create something similar to Ballchasing.com but for Melee.

Max Timkovich 20 Jan 2, 2023
A Distributed SQL Database - Building the Database in the Public to Learn Database Internals

Table of Contents Overview Usage TODO MVCC in entangleDB SQL Query Execution in entangleDB entangleDB Raft Consensus Engine What I am trying to build

Sarthak Dalabehera 38 Jan 2, 2024
Grsql is a great tool to allow you set up your remote sqlite database as service and CRUD(create/read/update/delete) it using gRPC.

Grsql is a great tool to allow you set up your remote sqlite database as service and CRUD (create/ read/ update/ delete) it using gRPC. Why Create Thi

Bruce Yuan 33 Dec 16, 2022
cogo rust coroutine database driver (Mysql,Postgres,Sqlite)

cdbc Coroutine Database driver Connectivity.based on cogo High concurrency,based on coroutine No Future<'q,Output=*>,No async fn, No .await , no Poll*

co-rs 10 Nov 13, 2022
webmention-receiver - a simple program that receives webmentions, records them to a SQLite database

webmention-receiver is a simple program that receives webmentions, records them to a SQLite database, and allows viewing them via a webpage or RSS feed. It has no conception of an "account", and by default will accept webmentions for any domain. It is possible to configure it to only accept webmentions for a specific set of domains, if you'd prefer.

Wesley Aptekar-Cassels 11 Sep 3, 2022
Using embedded database modeled off SQLite - in Rust

Rust-SQLite (SQLRite) Rust-SQLite, aka SQLRite , is a simple embedded database modeled off SQLite, but developed with Rust. The goal is get a better u

Hand of Midas 3 May 19, 2023
Document your SQLite tables and columns with in-line comments

sqlite-docs A SQLite extension, CLI, and library for documentating SQLite tables, columns, and extensions. Warning sqlite-docs is still young and not

Alex Garcia 20 Jul 2, 2023
SQLite compiled to WASM with pluggable data storage

wasm-sqlite SQLite compiled to WASM with pluggable data storage. Useful to save SQLite in e.g. Cloudflare Durable Objects (example: https://github.com

Markus Ast 36 Dec 7, 2022
Simple and handy btrfs snapshoting tool. Supports unattended snapshots, tracking, restoring, automatic cleanup and more. Backed with SQLite.

Description Simple and handy btrfs snapshoting tool. Supports unattended snapshots, tracking, restoring, automatic cleanup and more. Backed with SQLit

Eduard Tolosa 27 Nov 22, 2022
A tool for automated migrations for PostgreSQL, SQLite and MySQL.

Models Models is an implementation for a SQL migration management tool. It supports PostgreSQL, MySQL, and SQLite. Quick Start install the CLI by runn

null 45 Nov 16, 2022