Apple Health Data Query Skill

Apple Health Data Query Skill

Query and analyze health data from the local SQLite database containing 6.3M+ records across 43 health metrics.

Database Location

~/​data/​health.db

Query Methods

1. Python Script (Recommended for Common Queries)

Use scripts/​health_query.py for pre-built queries with automatic formatting:

# Daily summary
python ~/​.claude/​skills/​health-data/​scripts/​health_query.py --format markdown daily --date 2025-11-29

# Weekly trends
python ~/​.claude/​skills/​health-data/​scripts/​health_query.py --format json weekly --weeks 4

# Sleep analysis
python ~/​.claude/​skills/​health-data/​scripts/​health_query.py --format fhir sleep --days 7

# Latest vitals
python ~/​.claude/​skills/​health-data/​scripts/​health_query.py vitals

# Activity rings
python ~/​.claude/​skills/​health-data/​scripts/​health_query.py --format json activity --days 30

# Workout history
python ~/​.claude/​skills/​health-data/​scripts/​health_query.py workouts --days 30 --type Running

# Custom SQL
python ~/​.claude/​skills/​health-data/​scripts/​health_query.py --format json query "SELECT * FROM workouts LIMIT 5"

Output formats: markdown, json, fhir, ascii

2. Direct SQL (For Custom/​Ad-hoc Queries)

For flexible queries, run SQL directly against the database. See references/​schema.md for table structures and query templates.

sqlite3 ~/​data/​health.db "SELECT AVG(value) FROM health_records WHERE record_type LIKE '%HeartRate%' AND start_date LIKE '2025-11%'"

Pre-built Queries

Daily Health Summary

Get today's key metrics:

python ~/​.claude/​skills/​health-data/​scripts/​health_query.py daily

Returns: steps, calories, heart rate (avg/​min/​max), exercise minutes, distance, activity ring status.

Weekly Trends

Compare week-over-week performance:

python ~/​.claude/​skills/​health-data/​scripts/​health_query.py weekly --weeks 4

Returns: average daily steps, resting HR, exercise minutes, workout count per week.

Sleep Analysis

Analyze sleep patterns:

python ~/​.claude/​skills/​health-data/​scripts/​health_query.py sleep --days 14

Returns: nightly duration, sleep stages (Core, Deep, REM), average sleep hours.

Latest Vitals

Get most recent vital readings:

python ~/​.claude/​skills/​health-data/​scripts/​health_query.py vitals

Returns: Heart Rate, HRV, Resting HR, Blood Oxygen, Respiratory Rate with timestamps.

Activity Rings

Track ring completion:

python ~/​.claude/​skills/​health-data/​scripts/​health_query.py activity --days 30

Returns: daily ring values/​goals, completion percentages, perfect day count.

Workout History

Review exercise sessions:

python ~/​.claude/​skills/​health-data/​scripts/​health_query.py workouts --days 30 --type Running

Returns: workout type, duration, distance, calories, summary by type.

Output Formats

Markdown (default)

Human-readable tables and lists. Best for reports and summaries.

JSON

Structured data for programmatic use:

{
  "date": "2025-11-29",
  "metrics": {
    "steps": 8542,
    "active_calories": 450.5,
    "heart_rate": {"avg": 72.3, "min": 52, "max": 145}
  }
}

FHIR R4

Healthcare interoperability format. Outputs as FHIR Bundle with Observation resources using LOINC codes. See references/​fhir_mappings.md for code mappings.

ASCII

Terminal-friendly output with bar charts and statistics:

============================================================
  DAILY SUMMARY - 2025-11-29
============================================================

METRICS
----------------------------------------
  steps                      2620
  active_calories           234.5
  heart_rate           avg:  67.5  min:  52  max: 108

ACTIVITY RINGS
----------------------------------------
  move       [███████░░░░░░░░░░░░░]  36.7% (238/​650)
  exercise   [░░░░░░░░░░░░░░░░░░░░]   0.0% (0/​35)
  stand      [████████████████████] 100.0% (10/​10)

Common SQL Patterns

For ad-hoc queries, use these patterns from references/​schema.md:

Heart rate by hour (circadian pattern):

SELECT strftime('%H', start_date) as hour, ROUND(AVG(value), 1) as avg_hr
FROM health_records
WHERE record_type = 'HKQuantityTypeIdentifierHeartRate'
AND value BETWEEN 40 AND 200
GROUP BY hour ORDER BY hour;

Steps per day this month:

SELECT DATE(start_date) as day, SUM(value) as steps
FROM health_records
WHERE record_type = 'HKQuantityTypeIdentifierStepCount'
AND start_date >= DATE('now', 'start of month')
GROUP BY day ORDER BY day;

Sleep quality (deep + REM hours):

SELECT DATE(start_date) as night,
       ROUND(SUM(duration_minutes)/​60.0, 1) as quality_hours
FROM sleep_sessions
WHERE sleep_stage IN ('Deep', 'REM')
GROUP BY night ORDER BY night DESC LIMIT 14;

Workout summary:

SELECT REPLACE(workout_type, 'HKWorkoutActivityType', '') as type,
       COUNT(*) as count, ROUND(SUM(duration_minutes)) as total_min
FROM workouts
WHERE start_date >= DATE('now', '-30 days')
GROUP BY type ORDER BY count DESC;

Record Types Available

The database contains 43 health metric types including:

Vitals: Heart Rate, HRV, Resting HR, Blood Oxygen, Respiratory Rate, Blood Pressure

Activity: Steps, Distance, Active Calories, Basal Calories, Flights Climbed, Exercise Time, Stand Time

Mobility: Walking Speed, Step Length, Walking Asymmetry, Stair Speed, Walking Steadiness

Body: Weight, BMI, Body Fat %

Audio: Environmental Noise, Headphone Exposure

Other: VO2 Max, Time in Daylight, UV Exposure

Data Coverage

• Records: 6.3M+ measurements
• Date range: 2015-10-13 to present
• Workouts: 1,435 sessions
• Sleep sessions: 40,514 records
• Activity days: 1,875 daily summaries

Resources

scripts/

• health_query.py - Main query tool with Markdown/​JSON/​FHIR output

references/

• schema.md - Database schema, record type mappings, SQL query templates
• fhir_mappings.md - LOINC codes and FHIR R4 templates

Troubleshooting

Database not found: Ensure ~/​data/​health.db exists. Run the import script from /​Users/​server/​apple_health_export/:

python import_health.py --status

No data for date range: Check available date range:

SELECT MIN(start_date), MAX(start_date) FROM health_records;

Outlier values: Filter physiologically valid ranges (e.g., heart rate 40-200 bpm):

WHERE value BETWEEN 40 AND 200