macOS: Screen Time data & Calendar Events to SQLite

install.sh

#!/usr/bin/env zsh

# just to make the setup a little more reproducible

ln -i longtermscreentime.sh /usr/local/bin
ln -i local.mmyrte.longtermscreentime.plist $HOME/Library/LaunchAgents

local.mmyrte.longtermscreentime.plist

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
    <dict>
        <key>Label</key>
        <string>local.mmyrte.longtermscreentime</string>
        <key>ProgramArguments</key>
        <array>
            <string>/usr/local/bin/longtermscreentime.sh</string>
        </array>
        <key>WatchPaths</key>
        <array>
            <string>~/Library/Application Support/Knowledge/knowledgeC.db</string>
            <string>~/Library/Calendars/Calendar.sqlitedb</string>
        </array>
    </dict>
</plist>

longtermscreentime.sh

#!/usr/bin/env zsh

# Purpose: Extract app usage data and screen state from the macOS Screen Time database
# at $HOME/Library/Application Support/Knowledge/knowledgeC.db

# Secondary Purpose: Extract current state of macOS Calendar app event data from
# $HOME/Library/Calendars/Calendar.sqlitedb into a similar format, allowing for joins
# between app usage and events for easy categorisation of app usage data.

# This runs with vanilla macOS tooling: `zsh` and `sqlite`
# The inspiration came from Bob Rudis' 2019 [R based approached](https://rud.is/b/2019/10/28/spelunking-macos-screentime-app-usage-with-r/)
# which in turn was inspired by Sarah Edwards' [Python based approach](https://www.mac4n6.com/blog/2018/8/5/knowledge-is-power-using-the-knowledgecdb-database-on-macos-and-ios-to-determine-precise-user-and-application-usage)

KNOWLEDGE_PATH="$HOME/Library/Application Support/Knowledge/knowledgeC.db"
CALENDAR_PATH="$HOME/Library/Calendars/Calendar.sqlitedb"
OUTPUT_PATH="$HOME/Documents/longtermscreentime.sqlite3"

sqlite3 $OUTPUT_PATH <<EOF

attach database '$KNOWLEDGE_PATH' as knowledge;

create table if not exists app_usage_t (
  "app" text,
  "usage" integer not null,
  "start_time" integer not null,
  "end_time" integer not null,
  "created_at" integer not null,
  -- id_device is not currently used; this future-proofs the schema. see below. 
  "id_device" text
);

create index if not exists app_usage_endtime_idx on
app_usage_t (end_time);

/* 
  The usage logs on my Mac are very different from those coming from iPhone; the Screen
  Time preference panel also doesn't allow me to select a device. Something is out of
  whack; disabling multidevice support for now.
*/
insert into
  app_usage_t
with
  last_inserts as (
    select
      -- either latest, or 0 for an empty table
      coalesce(max(end_time), 0) as end_time,
      id_device
    from
      app_usage_t
    group by
      id_device
  )
select
  zobject.zvaluestring as "app",
  (zobject.zenddate - zobject.zstartdate) as "usage",
  -- 978307200 is the offset in seconds from 1970-01-01 to 2000-01-01
  datetime(zobject.zstartdate + 978307200, 'unixepoch') as "start_time",
  datetime(zobject.zenddate + 978307200, 'unixepoch') as "end_time",
  datetime(zobject.zcreationdate + 978307200, 'unixepoch') as "created_at",
--   zsource.zdeviceid as "id_device"
  null as "id_device"
from
  knowledge.zobject
--   left join knowledge.zsource
--   left join last_inserts
where
  zobject.zstreamname = '/app/usage'
  and zobject.zsource is null
  and end_time > (select end_time from last_inserts)
-- these joins only make sense if local events aren't logged with a NULL zobject.zsource
--   and zobject.zsource = zsource.z_pk
--   and zsource.zdeviceid = last_inserts.id_device
--   and end_time > last_inserts.end_time
order by
  end_time desc;

/*
  We could also copy over the knowledge.zsyncpeer table if we want to augment our
  information about all iCloud devices. It 
*/

detach database knowledge;

EOF

# Second part: adding calendar events

sqlite3 $OUTPUT_PATH <<EOF

attach database '$CALENDAR_PATH' as calendars;

drop table if exists calendar_events_t;
create table calendar_events_t as 
select
  calendar.title as calendar_title,
  calendaritem.summary as event_title,
  datetime (calendaritem.start_date + 978307200, 'unixepoch') as start_time,
  datetime (calendaritem.end_date + 978307200, 'unixepoch') as end_time
from
  calendar
  join calendaritem on calendar.rowid = calendaritem.calendar_id
where
  calendar.title not like '%Birthdays%'
  and calendar.title not like 'Found%'
  and calendar.title not like '%Feiertage%'
  and calendar.title not like '%Holiday%'
order by 
  calendar_title;

create index if not exists calendar_endtime_idx on
calendar_events_t (end_time);

detach database calendars;

EOF

nice-application-names.sh

#!/usr/bin/env zsh

# Purpose: This generates a table of all installed (non-system) apps on macOS and
# inserts it into a sqlite database. 
# It is an addendum to the longtermscreentime.sh script meant to prettify the app
# identifiers that Screen Time logs, but not meant to be run at frequent intervals,
# since it is somewhat expensive to gather all application names and IDs. Running it
# simply overwrites the existing `app_names_t` table. The logic should probably be
# rewritten to insert new/non-duplicate names, since even after removing/uninstalling an
# app, you'd want its name to be reported nicely.

OUTPUT_PATH="$HOME/Documents/longtermscreentime.sqlite3"

# There's no -excludedir option in mdfind, so inverse grep for System
app_paths=$(mdfind 'kMDItemKind == "Application"' | grep -v System)

sqlite3 $OUTPUT_PATH <<EOF

create table if not exists app_names_tmp (
  "app" text not null,
  "prettyname" text not null,
  "path" text not null
);

EOF

echo "$app_paths" | while IFS= read -r app; do
  identifier=$(mdls -name kMDItemCFBundleIdentifier -raw "$app" |  tr -d '\0')
  prettyname=$(basename "$app" .app)
  echo "inserting $identifier / $prettyname"
  sqlite3 $OUTPUT_PATH "INSERT INTO app_names_tmp (app, prettyname, path) VALUES ('$identifier', '$prettyname', '$app');"
done

sqlite3 $OUTPUT_PATH <<EOF

drop table if exists app_names_t;

create table 
  app_names_t 
as 
select distinct 
  *
from 
  app_names_tmp
where 
  app <> '(null)'
  and prettyname <> '(null)'
;

drop table app_names_tmp;

EOF