Exporting and Analyzing iOS Screen Time Data using Python and InfluxDB

Felix Kohlhas • GitHub: [ScreenFlux]
Tags: Data Science Python
A guide on how to export iOS (and iPadOS) screen time data by reading it directly from the knowledgeC.db, plus a script for importing it into InfluxDB.

If you’ve found this blog post, you’re likely on a frustrating journey to find a way to export Screen Time usage data from your iOS device. Look no further! In this article, we will explore methods to access and analyze Screen Time data beyond the limitations of the interface in the Settings app.
Update: You can also use ScreenTime2CSV to export the data to CSV.

Ever since Apple introduced the ability to track screen time with iOS 12, over 5 years ago, users have been looking for ways to access the generated data without the constraints of the interface in the Settings app. While the discussions in the official apple community seem to get closed with a simple “not possible”, there exist blog posts from creative developers that have found clever ways of tapping into this data. The most important ones to mention are “Spelunking macOS ‘ScreenTime’ App Usage with R” by rud.is and “Knowledge is Power! Using the macOS/iOS knowledgeC.db Database to Determine Precise User and Application Usage” by mac4n6.com. Both of these blog posts describe a method, in which MacOS Screen Time data is extracted directly from the knowledgeC.db database and further processed in R.

What I want to highlight in this article, because it is only mentioned as a side note in the others, is that this database also contains the Screen Time data from all other devices linked to the same iCloud Account. This finally makes it possible to export not only MacOS, but also iOS and iPadOS usage data. Here I will be describing how to export Screen Time data into InfluxDB, but the same concept can be used to export the data into whatever tool you prefer.


Enabling synchronisation of Screen Time data

To access your iOS or iPadOS device’s Screen Time data from your Mac, follow these steps:

  1. Sign in to both devices using the same iCloud account.
  2. On your iOS or iPadOS device, go to Settings > Screen Time.
  3. Enable the “Share across devices” option in the Screen Time settings.
Note: The data from the other devices may not show up immediately due to a possible syncing bug. Don’t be discouraged if the data doesn’t appear right away, as it might still be synced correctly and just not displayed in the Screen Time interface yet.

Reading Screen Time data using Python

After enabling the synchronisation the data of the other devices should be added to the local database at ~/Library/Application Support/Knowledge/knowledgeC.db. To access the data using Python, you can use the following function:

import sqlite3
from os.path import expanduser

def query_database():
    # Connect to the SQLite database
    knowledge_db = expanduser("~/Library/Application Support/Knowledge/knowledgeC.db")
    with sqlite3.connect(knowledge_db) as con:
        cur = con.cursor()
        
        # Execute the SQL query to fetch data
        # Modified from https://rud.is/b/2019/10/28/spelunking-macos-screentime-app-usage-with-r/
        query = """
        SELECT
            ZOBJECT.ZVALUESTRING AS "app", 
            (ZOBJECT.ZENDDATE - ZOBJECT.ZSTARTDATE) AS "usage",
            (ZOBJECT.ZSTARTDATE + 978307200) as "start_time", 
            (ZOBJECT.ZENDDATE + 978307200) as "end_time",
            (ZOBJECT.ZCREATIONDATE + 978307200) as "created_at", 
            ZOBJECT.ZSECONDSFROMGMT AS "tz",
            ZSOURCE.ZDEVICEID AS "device_id",
            ZMODEL AS "device_model"
        FROM
            ZOBJECT 
            LEFT JOIN
            ZSTRUCTUREDMETADATA 
            ON ZOBJECT.ZSTRUCTUREDMETADATA = ZSTRUCTUREDMETADATA.Z_PK 
            LEFT JOIN
            ZSOURCE 
            ON ZOBJECT.ZSOURCE = ZSOURCE.Z_PK 
            LEFT JOIN
            ZSYNCPEER
            ON ZSOURCE.ZDEVICEID = ZSYNCPEER.ZDEVICEID
        WHERE
            ZSTREAMNAME = "/app/usage"
        ORDER BY
            ZSTARTDATE DESC
        """
        cur.execute(query)
        
        # Fetch all rows from the result set
        return cur.fetchall()

The function returns a list of rows, each representing an event as a tuple, such as:

 ('com.vscodium', 14, 1686057894, 1686057908, 1686057908.392201, 7200, None, None)

or

('net.whatsapp.WhatsApp', 11, 1684343658, 1684343669, 1684343669.547068, 7200, '9B392A24-F0A5-5BF9-AC20-0CC38404D6CE', 'iPhone14,2')

The tuple contains the following fields:

FieldNoteExample
App NameBundle identifier1'org.chromium.Chromium', 'md.obsidian'
UsageApp usage in seconds
Start TimeUnix timestamp (UTC)
End TimeUnix timestamp (UTC)
Creation TimeUnix timestamp (UTC)
Time ZoneOffset from UTC in seconds3600, 7200
Device IDDevice UUID'9B392A24-F0A5-5BF9-AC20-0CC38404D6CE', 'B9C0C352-91F5-5940-AC26-64BE69B4EC75'
Device ModelDevice Model'iPhone14,2', 'iPad8,5'
The last two fields (device id and device model) are only set if the data is from a remote device, otherwise they are None.

Publishing Screen Time data to InfluxDB

To publish the data to InfluxDB, I have built the ScreenFlux script, which converts the data into the format expected by InfluxDB and publishes it in a single query. To use the script, follow these steps:

  1. Download the screenflux.py script from the ScreenFlux GitHub Repository.
  2. Install the influxdb_client Python package by running the command pip3 install influxdb_client.
  3. Modify the following lines in the script to configure the InfluxDB connection and target:
db_url = "..."
db_token = "..."
db_org = "..."
db_bucket = "screentime"
  1. Once the script is configured and all dependencies are installed, run it using the command python3 screenflux.py.

To automatically send the data to InfluxDB every day at midnight, you can create a cronjob with the following command:

0 0 * * * python3 /<path to screenflux>/screenflux.py

Conclusion

Exporting and analyzing Screen Time data from your iOS devices is now within reach. By following the steps outlined in this article, you can gain insights into your app usage patterns and make informed decisions about your device usage. Remember to explore the data, visualize it, and discover how you can optimize your digital habits.


Screenshots

Screenshot of InfluxDB Graph
Screen Time over 30 days (by app)

Screenshot of InfluxDB Graph
Screen Time over 30 days (by app, stacked)


Example queries for InfluxDB

Usage per hour by app

from(bucket: "screentime")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> group(columns: ["app"])
  |> aggregateWindow(every: 1d, fn: sum, createEmpty: true)
  |> map(fn: (r) => ({r with _value: float(v: r._value) / 3600.0}))
  |> yield(name: "sum")

Usage per hour by device

from(bucket: "screentime")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> group(columns: ["device_id"])
  |> aggregateWindow(every: 1d, fn: sum, createEmpty: true)
  |> map(fn: (r) => ({r with _value: float(v: r._value) / 3600.0}))
  |> yield(name: "sum")

Comments

This blog does not currently have a comment function. You can send me an email to [email protected] instead and I will add it here.