Datasette Queries

Datasette is amazing – however it relies on some knowledge of SQL syntax to make the most of it. The amount I know about SQL could be written on a small Post-it note – so these snippets serve primarily as a reference to me, but might be useful to you.

HealthKit

Steps captured by my Apple Watch, grouped by day and limited to single year.

select
  date(substr(creationDate, 1, 10)) as recordDate,
  substr(creationDate, 1, 10) as label,
  sum(value)
from
  rStepCount
where
  creationDate > '2020-01-01'
and
  creationDate < '2020-12-31'
and 
  sourceName LIKE '%Apple%'
group by
  label