Some notes on accessing / exporting Apple's Screen Time data
See also:
⇒ ./extract-coredata-model-hierarchy.py ~/Library/Application\ Support/Knowledge/knowledgeC.db
- 1: AdditionChangeSet (Table: ZADDITIONCHANGESET)
- 2: ContextualChangeRegistration (Table: ZCONTEXTUALCHANGEREGISTRATION)
- 3: ContextualKeyPath (Table: ZCONTEXTUALKEYPATH)
- 4: CustomMetadata (Table: ZCUSTOMMETADATA)
- 5: DeletionChangeSet (Table: ZDELETIONCHANGESET)
- 6: Histogram (Table: ZHISTOGRAM)
- 7: HistogramValue (Table: ZHISTOGRAMVALUE)
- 8: KeyValue (Table: ZKEYVALUE)
- 9: Object (Table: ZOBJECT)
- 10: Category
- 11: Event
- 12: Identifier
- 13: Quantity
- 14: Source (Table: ZSOURCE)
- 15: StructuredMetadata (Table: ZSTRUCTUREDMETADATA)
- 16: SyncPeer (Table: ZSYNCPEER)
- From ChatGPT (Ref):
- Note: This seems to be wrong, at least on macOS Ventura.. not sure if it was ever correct.
-
On macOS, the Screen Time data is stored in a SQLite database located at:
-
~/Library/Application Support/ScreenTimeAgent/Database/CoreDuetData.db
-
To access this database, you can use a SQLite browser tool, such as DB Browser for SQLite, which is a free, open-source tool that can be downloaded from their website at https://sqlitebrowser.org/.
-
Once you have installed DB Browser for SQLite, you can open the
CoreDuetData.db
file and browse the tables to view the Screen Time data. Note that the data is stored in a compressed format, so you may need to use a SQLite extension or command-line tool to decompress the data for viewing.
- https://stackoverflow.com/questions/66935741/how-to-get-screen-time-data-on-macos/70627001#70627001
-
The data screenTime is stored in
/private/var/db/CoreDuet/Knowledge
/Users/wgnb/Library/Application Support/Knowledge
~/Library/Application\ Support/Knowledge
~/Library/Application\ Support/Knowledge/knowledgeC.db
-
read Sarah blog for details
- 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
-
The
knowledgeC.db
database can be found on macOS and iOS devices. On Mac systems there will be a system context database located in the/private/var/db/CoreDuet/Knowledge
directory, while a user context database is located in the user’s~/Library/Application Support/Knowledge/
directory. *Update 08/7/18: The data in this article is specific to macOS 10.13 and iOS 11. Other versions may contain the same data but the schemas/contents may be slightly different. A note about iOS 10 database is at the end of this article. -
On iOS there is only one main
knowledgeC.db
database located in/private/var/mobile/Library/CoreDuet/Knowledge/
that appears to merge the contents of the user and system context databases found on macOS. -
The database has many tables which have many columns. This article will only go over three of these that I have found to be particularly interesting.
-
ZOBJECT
– Contains potentially thousands of usage entries for approximately 4 weeks. -
ZSOURCE
– Source of the ZOBJECT entries -
ZSTRUCTUREDMETADATA
– Additional metadata associated withZOBJECT
entries
-
- 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
-
- https://stackoverflow.com/questions/66935741/how-to-get-screen-time-data-on-macos/66946521#66946521
-
The closest I've come is this Github Repo: https://github.com/Evian-Zhang/ScreenTimeController
-
He confirmed the research I had already done by looking at the open file descriptors for the Screen Time agent. The ScreenTime database is stored here:
-
/var/folders/XYZ/xxxxxxxxxx/0/com.apple.ScreenTimeAgent/Store
-
-
You can read more about this part of the filesystem here
-
In this folder, there are several SQLite files which is what the agent writes to. Unfortunately, this folder is really locked down by MacOS.
-
You can read about how its locked down here and here.
-
- https://www.r-bloggers.com/2019/10/spelunking-macos-screentime-app-usage-with-r/
-
Thankfully, Sarah Edwards has foraged through the macOS filesystem for us and explained where these bits of knowledge are in her post, Knowledge is Power! Using the macOS/iOS knowledgeC.db Database to Determine Precise User and Application Usage, which ultimately reveals the data lurks in
~/Library/Application Support/Knowledge/knowledgeC.db
. Sarah also has a neat little Python utility dubbed APOLLO (Apple Pattern of Life Lazy Output’er) which has a smattering of knowledgeC.db canned SQL queries that cover a myriad of tracked items.
-
- https://apple.stackexchange.com/questions/424955/is-there-a-way-to-save-or-export-screentime-data
-
Is there a way to save or export Screentime data?
- My Answer
-
- https://apple.stackexchange.com/questions/346091/is-there-a-way-to-download-screen-time-data-from-iphone-or-other-apple-ios-devic
-
Is there a way to download Screen Time data from iPhone or other Apple iOS devices?
- My Answer
-
- https://apple.stackexchange.com/questions/354668/screen-time-statistic-for-longer-periods
-
Screen time statistic for longer periods
- My Answer
-
- https://apple.stackexchange.com/questions/444417/copy-the-screen-time-data/459161#459161
-
Copy the screen time data
- My Answer
-
- How can I list the open file descriptors for a process running on macOS? (ChatGPT Ref)
- Get the PID:
pgrep ScreenTimeAgent
- List open file descriptors:
lsof -p <pid>
- One liner:
ST_PID=$(pgrep ScreenTimeAgent); lsof -p $ST_PID
- Get the PID:
⇒ ST_PID=$(pgrep ScreenTimeAgent); lsof -p $ST_PID
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
ScreenTim 826 devalias cwd DIR 1,4 384 40725 /Users/devalias/Library/Containers/com.apple.ScreenTimeAgent/Data
ScreenTim 826 devalias txt REG 1,4 2858656 1152921500312301519 /System/Library/PrivateFrameworks/ScreenTimeCore.framework/Versions/A/ScreenTimeAgent
ScreenTim 826 devalias txt REG 1,4 52300 119749843 /Library/Preferences/Logging/.plist-cache.SaRrvI6F
ScreenTim 826 devalias txt REG 1,4 281152 1152921500312437674 /usr/lib/libobjc-trampolines.dylib
ScreenTim 826 devalias txt REG 1,4 32768 41700 /private/var/folders/j4/kxtq1cjs1l98xfqncjbsbx1c0000gn/0/com.apple.ScreenTimeAgent/Store/RMAdminStore-Local.sqlite-shm
ScreenTim 826 devalias txt REG 1,4 110 1152921500312100418 /System/Library/CoreServices/SystemVersion.bundle/English.lproj/SystemVersion.strings
ScreenTim 826 devalias txt REG 1,4 237664 107197209 /private/var/db/timezone/tz/2023c.1.0/icutz/icutz44l.dat
ScreenTim 826 devalias txt REG 1,4 32768 41754 /private/var/folders/j4/kxtq1cjs1l98xfqncjbsbx1c0000gn/0/com.apple.ScreenTimeAgent/Store/RMAdminStore-Cloud.sqlite-shm
ScreenTim 826 devalias txt REG 1,4 30447056 1152921500312449382 /usr/share/icu/icudt70l.dat
ScreenTim 826 devalias txt REG 1,4 134457 1152921500312134360 /System/Library/Frameworks/CoreData.framework/Versions/A/Resources/CoreDataErrors.loctable
ScreenTim 826 devalias txt REG 1,4 32768 82945 /Users/devalias/Library/Containers/com.apple.ScreenTimeAgent/Data/Library/Caches/com.apple.ScreenTimeAgent/Cache.db-shm
ScreenTim 826 devalias txt REG 1,4 626688 104208182 /private/var/folders/j4/kxtq1cjs1l98xfqncjbsbx1c0000gn/C/com.apple.iconservices/store.index
ScreenTim 826 devalias txt REG 1,4 825440 1152921500312138426 /System/Library/Frameworks/Foundation.framework/Versions/C/Resources/FoundationErrors.loctable
ScreenTim 826 devalias txt REG 1,4 132070 121959101 /private/var/db/analyticsd/events.allowlist
ScreenTim 826 devalias 0r CHR 3,2 0t0 314 /dev/null
ScreenTim 826 devalias 1u CHR 3,2 0t0 314 /dev/null
ScreenTim 826 devalias 2u CHR 3,2 0t7142668 314 /dev/null
ScreenTim 826 devalias 3u REG 1,4 14741504 41682 /private/var/folders/j4/kxtq1cjs1l98xfqncjbsbx1c0000gn/0/com.apple.ScreenTimeAgent/Store/RMAdminStore-Local.sqlite
ScreenTim 826 devalias 4u REG 1,4 2550312 41699 /private/var/folders/j4/kxtq1cjs1l98xfqncjbsbx1c0000gn/0/com.apple.ScreenTimeAgent/Store/RMAdminStore-Local.sqlite-wal
ScreenTim 826 devalias 5u REG 1,4 32768 41700 /private/var/folders/j4/kxtq1cjs1l98xfqncjbsbx1c0000gn/0/com.apple.ScreenTimeAgent/Store/RMAdminStore-Local.sqlite-shm
ScreenTim 826 devalias 6u REG 1,4 14741504 41682 /private/var/folders/j4/kxtq1cjs1l98xfqncjbsbx1c0000gn/0/com.apple.ScreenTimeAgent/Store/RMAdminStore-Local.sqlite
ScreenTim 826 devalias 7u REG 1,4 2550312 41699 /private/var/folders/j4/kxtq1cjs1l98xfqncjbsbx1c0000gn/0/com.apple.ScreenTimeAgent/Store/RMAdminStore-Local.sqlite-wal
ScreenTim 826 devalias 8u REG 1,4 13381632 41725 /private/var/folders/j4/kxtq1cjs1l98xfqncjbsbx1c0000gn/0/com.apple.ScreenTimeAgent/Store/RMAdminStore-Cloud.sqlite
ScreenTim 826 devalias 9u REG 1,4 218392 41753 /private/var/folders/j4/kxtq1cjs1l98xfqncjbsbx1c0000gn/0/com.apple.ScreenTimeAgent/Store/RMAdminStore-Cloud.sqlite-wal
ScreenTim 826 devalias 10u REG 1,4 32768 41754 /private/var/folders/j4/kxtq1cjs1l98xfqncjbsbx1c0000gn/0/com.apple.ScreenTimeAgent/Store/RMAdminStore-Cloud.sqlite-shm
ScreenTim 826 devalias 11u REG 1,4 13381632 41725 /private/var/folders/j4/kxtq1cjs1l98xfqncjbsbx1c0000gn/0/com.apple.ScreenTimeAgent/Store/RMAdminStore-Cloud.sqlite
ScreenTim 826 devalias 12u REG 1,4 13381632 41725 /private/var/folders/j4/kxtq1cjs1l98xfqncjbsbx1c0000gn/0/com.apple.ScreenTimeAgent/Store/RMAdminStore-Cloud.sqlite
ScreenTim 826 devalias 13u REG 1,4 218392 41753 /private/var/folders/j4/kxtq1cjs1l98xfqncjbsbx1c0000gn/0/com.apple.ScreenTimeAgent/Store/RMAdminStore-Cloud.sqlite-wal
ScreenTim 826 devalias 14u REG 1,4 218392 41753 /private/var/folders/j4/kxtq1cjs1l98xfqncjbsbx1c0000gn/0/com.apple.ScreenTimeAgent/Store/RMAdminStore-Cloud.sqlite-wal
ScreenTim 826 devalias 15u REG 1,4 13381632 41725 /private/var/folders/j4/kxtq1cjs1l98xfqncjbsbx1c0000gn/0/com.apple.ScreenTimeAgent/Store/RMAdminStore-Cloud.sqlite
ScreenTim 826 devalias 16u REG 1,4 218392 41753 /private/var/folders/j4/kxtq1cjs1l98xfqncjbsbx1c0000gn/0/com.apple.ScreenTimeAgent/Store/RMAdminStore-Cloud.sqlite-wal
ScreenTim 826 devalias 17u REG 1,4 14741504 41682 /private/var/folders/j4/kxtq1cjs1l98xfqncjbsbx1c0000gn/0/com.apple.ScreenTimeAgent/Store/RMAdminStore-Local.sqlite
ScreenTim 826 devalias 18u REG 1,4 2550312 41699 /private/var/folders/j4/kxtq1cjs1l98xfqncjbsbx1c0000gn/0/com.apple.ScreenTimeAgent/Store/RMAdminStore-Local.sqlite-wal
ScreenTim 826 devalias 19u REG 1,4 14741504 41682 /private/var/folders/j4/kxtq1cjs1l98xfqncjbsbx1c0000gn/0/com.apple.ScreenTimeAgent/Store/RMAdminStore-Local.sqlite
ScreenTim 826 devalias 20u REG 1,4 2550312 41699 /private/var/folders/j4/kxtq1cjs1l98xfqncjbsbx1c0000gn/0/com.apple.ScreenTimeAgent/Store/RMAdminStore-Local.sqlite-wal
ScreenTim 826 devalias 21 NPOLICY
ScreenTim 826 devalias 22u REG 1,4 14741504 41682 /private/var/folders/j4/kxtq1cjs1l98xfqncjbsbx1c0000gn/0/com.apple.ScreenTimeAgent/Store/RMAdminStore-Local.sqlite
ScreenTim 826 devalias 23u REG 1,4 2550312 41699 /private/var/folders/j4/kxtq1cjs1l98xfqncjbsbx1c0000gn/0/com.apple.ScreenTimeAgent/Store/RMAdminStore-Local.sqlite-wal
ScreenTim 826 devalias 24u REG 1,4 14741504 41682 /private/var/folders/j4/kxtq1cjs1l98xfqncjbsbx1c0000gn/0/com.apple.ScreenTimeAgent/Store/RMAdminStore-Local.sqlite
ScreenTim 826 devalias 25u REG 1,4 49152 82941 /Users/devalias/Library/Containers/com.apple.ScreenTimeAgent/Data/Library/Caches/com.apple.ScreenTimeAgent/Cache.db
ScreenTim 826 devalias 26u REG 1,4 14741504 41682 /private/var/folders/j4/kxtq1cjs1l98xfqncjbsbx1c0000gn/0/com.apple.ScreenTimeAgent/Store/RMAdminStore-Local.sqlite
ScreenTim 826 devalias 27u REG 1,4 778712 82944 /Users/devalias/Library/Containers/com.apple.ScreenTimeAgent/Data/Library/Caches/com.apple.ScreenTimeAgent/Cache.db-wal
ScreenTim 826 devalias 28u REG 1,4 32768 82945 /Users/devalias/Library/Containers/com.apple.ScreenTimeAgent/Data/Library/Caches/com.apple.ScreenTimeAgent/Cache.db-shm
ScreenTim 826 devalias 29u REG 1,4 49152 82941 /Users/devalias/Library/Containers/com.apple.ScreenTimeAgent/Data/Library/Caches/com.apple.ScreenTimeAgent/Cache.db
ScreenTim 826 devalias 30u REG 1,4 778712 82944 /Users/devalias/Library/Containers/com.apple.ScreenTimeAgent/Data/Library/Caches/com.apple.ScreenTimeAgent/Cache.db-wal
ScreenTim 826 devalias 31u REG 1,4 2550312 41699 /private/var/folders/j4/kxtq1cjs1l98xfqncjbsbx1c0000gn/0/com.apple.ScreenTimeAgent/Store/RMAdminStore-Local.sqlite-wal
- Accessing / Exporting Apple's Reminders Data on macOS (0xdevalias' gist)
- Accessing Apple's iCloud Synced Passwords Keychain (0xdevalias' gist)
- Decompile Apple Shortcuts into raw XML 'source code' (0xdevalias' gist)
- AppleScript Automation Snippets (0xdevalias' gist)
- macOS Launchpad DB Tricks
- Reverse Engineering on macOS (0xdevalias' gist)