<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Carmen Liu on Medium]]></title>
        <description><![CDATA[Stories by Carmen Liu on Medium]]></description>
        <link>https://medium.com/@carmenliu0208?source=rss-b5afc4befb28------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*ebcrt_ZBjiNz6pMj.</url>
            <title>Stories by Carmen Liu on Medium</title>
            <link>https://medium.com/@carmenliu0208?source=rss-b5afc4befb28------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sun, 22 Nov 2026 03:50:58 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@carmenliu0208/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[How to Retrieve Screen Time Data on macOS via the Command Line]]></title>
            <link>https://medium.com/@carmenliu0208/how-to-retrieve-screen-time-data-on-macos-via-the-command-line-66e269278ba5?source=rss-b5afc4befb28------2</link>
            <guid isPermaLink="false">https://medium.com/p/66e269278ba5</guid>
            <dc:creator><![CDATA[Carmen Liu]]></dc:creator>
            <pubDate>Sun, 02 Feb 2025 11:21:47 GMT</pubDate>
            <atom:updated>2025-07-16T14:56:39.209Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*cw5ZRjeq_bqVpXzQdcFoZg.png" /></figure><h3>1. Introduction</h3><p>Modern macOS systems include a powerful Screen Time feature that monitors and reports how much time you spend on various apps and websites. Whether you’re a developer, a system administrator, or simply a curious user, you might want to programmatically access this data for analysis, reporting, or even to build your custom monitoring tool.</p><p>In this post, I’ll walk you through how to locate and query the Screen Time data using command-line tools like sqlite3. We’ll cover how the data is stored, how to convert and filter timestamps (including time zone considerations), and how to aggregate usage data to produce meaningful statistics.</p><h3>2. Overview of macOS Screen Time Data Storage</h3><h4>Data Storage Location</h4><p>On macOS, Screen Time data is stored in system databases. A typical location is the SQLite database at</p><pre>~/Library/Application Support/Knowledge/knowledgeC.db</pre><p>In some cases, data may also be found in directories used by the ScreenTimeAgent (for example, under /var/folders/[random]/0/com.apple.ScreenTimeAgent/Store), but these folders are often protected by system integrity features.</p><h4>Database Structure Introduction</h4><p>The main table you’ll be interested in is ZOBJECT. This table contains fields such as:</p><ul><li><strong>ZSTARTDATE</strong> — The timestamp marking is when a usage session begins.</li><li><strong>ZENDDATE</strong> — The timestamp marking when the session ended.</li><li><strong>ZVALUESTRING</strong> — The application identifier (e.g., the Bundle ID).</li><li><strong>ZSTREAMNAME</strong> — A field that helps distinguish the type of record (e.g., /app/usage).</li></ul><h4>Timestamp Explanation</h4><p>macOS stores timestamps relative to a reference date (January 1, 2001). This is done by storing Unix timestamps that need an offset of <strong>978307200</strong> seconds (the Unix timestamp for 2001–01–01) when converting to standard Unix epoch time. We use functions like DATETIME(..., &#39;unixepoch&#39;) in SQLite to perform the conversion.</p><h3>3. Querying Screen Time Data with sqlite3</h3><p>Let’s start with a basic SQL query to extract usage records from the database. Here’s a sample query that converts the raw timestamps to local time and retrieves the start time, end time, app identifier, and duration:</p><pre>SELECT<br>    DATETIME(ZOBJECT.ZSTARTDATE + 978307200, &#39;unixepoch&#39;, &#39;localtime&#39;) AS &#39;Start Time&#39;,<br>    DATETIME(ZOBJECT.ZENDDATE + 978307200, &#39;unixepoch&#39;, &#39;localtime&#39;) AS &#39;End Time&#39;,<br>    ZOBJECT.ZVALUESTRING AS &#39;App Identifier&#39;,<br>    (ZOBJECT.ZENDDATE - ZOBJECT.ZSTARTDATE) AS &#39;Usage Duration (seconds)&#39;<br>FROM ZOBJECT<br>WHERE ZSTREAMNAME = &#39;/app/usage&#39;;</pre><p>In this query:</p><ul><li>The timestamps are converted by adding the offset (978307200) and then using the &#39;unixepoch&#39; modifier.</li><li>The &#39;localtime&#39; modifier converts the times into your local time format.</li></ul><h3>4. Aggregating Data: Calculating Total Usage per Application</h3><p>Often you might want to know how much time each app was used overall rather than viewing individual sessions. To do this, we can group the results by the application identifier and sum the durations.</p><p>For example, to get the total usage time (in seconds) for each app, then convert it to minutes, you can use</p><pre>SELECT<br>    ZOBJECT.ZVALUESTRING AS &#39;App Identifier&#39;,<br>    SUM(ZOBJECT.ZENDDATE - ZOBJECT.ZSTARTDATE) / 60.0 AS &#39;Usage Duration (minutes)&#39;<br>FROM ZOBJECT<br>WHERE ZSTREAMNAME = &#39;/app/usage&#39;<br>  AND date(DATETIME(ZOBJECT.ZSTARTDATE + 978307200, &#39;unixepoch&#39;, &#39;localtime&#39;)) = date(&#39;now&#39;, &#39;-1 day&#39;, &#39;localtime&#39;)<br>GROUP BY ZOBJECT.ZVALUESTRING<br>ORDER BY SUM(ZOBJECT.ZENDDATE - ZOBJECT.ZSTARTDATE) / 60.0 DESC;</pre><p>Here’s what’s happening:</p><ul><li>We filter the records only to include those from yesterday using the date() function on the start time.</li><li>The SUM() function aggregates the usage durations per application.</li><li>Dividing by 60 converts the total seconds into minutes.</li><li>Finally, the results are ordered in descending order so that the most-used apps appear first.</li></ul><h3>5. Handling Time Zones and Date Filtering</h3><p>Time zone handling is critical. If you are in a time zone other than your system’s default (for example, +8), you can adjust the conversion like so:sql</p><pre>SELECT<br>    ZOBJECT.ZVALUESTRING AS &#39;App Identifier&#39;,<br>    SUM(ZOBJECT.ZENDDATE - ZOBJECT.ZSTARTDATE) / 60.0 AS &#39;Usage Duration (minutes)&#39;<br>FROM ZOBJECT<br>WHERE ZSTREAMNAME = &#39;/app/usage&#39;<br>  AND date(DATETIME(ZOBJECT.ZSTARTDATE + 978307200, &#39;unixepoch&#39;, &#39;+10 hours&#39;)) = date(&#39;now&#39;, &#39;-1 day&#39;, &#39;+8 hours&#39;)<br>GROUP BY ZOBJECT.ZVALUESTRING<br>ORDER BY SUM(ZOBJECT.ZENDDATE - ZOBJECT.ZSTARTDATE) / 60.0 DESC;</pre><p>In this version:</p><ul><li>The &#39;+10 hours&#39; modifier ensures that the timestamps and the current date are interpreted in the +8 time zone.</li><li>This is especially useful if your work or life operates in a specific time zone different from the system’s default.</li></ul><h3>6. Considerations and Challenges</h3><h4>Permissions and System Protection</h4><p>Accessing these databases may require elevated permissions because parts of the file system (especially in /var/folders) are protected by macOS System Integrity Protection (SIP). Be cautious if you plan to alter these files and constantly work on backups.</p><h4>Database Structure Changes</h4><p>Note that as macOS updates, the internal database schema for Screen Time data might change. Always verify your macOS version&#39;s structure (e.g., table names and field names).</p><h4>Risk Warnings</h4><p>Directly modifying the Screen Time data is not recommended unless you fully understand the implications. Unintended modifications could affect how the Screen Time feature functions or how data is synchronized across devices.</p><h3>7. A Practical Example</h3><p>Imagine you need to generate a daily app usage report for monitoring purposes. You can place the SQL query inside a shell script and run it as a cron job. For example:</p><pre>#!/bin/bash<br># Save this script as screentime_report.sh and make it executable<br># Run the SQL query and save the output to a file<br>sqlite3 -readonly -json ~/Library/Application\ Support/Knowledge/knowledgeC.db &quot;<br>SELECT<br>    ZOBJECT.ZVALUESTRING AS &#39;App Identifier&#39;,<br>    SUM(ZOBJECT.ZENDDATE - ZOBJECT.ZSTARTDATE) / 60.0 AS &#39;Usage Duration (minutes)&#39;<br>FROM ZOBJECT<br>WHERE ZSTREAMNAME = &#39;/app/usage&#39;<br>  AND date(DATETIME(ZOBJECT.ZSTARTDATE + 978307200, &#39;unixepoch&#39;, &#39;+10 hours&#39;)) = date(&#39;now&#39;, &#39;-1 day&#39;, &#39;+8 hours&#39;)<br>GROUP BY ZOBJECT.ZVALUESTRING<br>ORDER BY SUM(ZOBJECT.ZENDDATE - ZOBJECT.ZSTARTDATE) / 60.0 DESC;<br>&quot; &gt; ~/Desktop/screentime_report_$(date +%F).json</pre><p>This script queries the Screen Time database for yesterday’s data (adjusted for the +10 time zone), aggregates usage by application, and outputs the result in JSON format. You can then integrate this data into other tools or dashboards.</p><h3>8. Conclusion and Further Extensions</h3><p>In this post, we:</p><ul><li>Explained where Screen Time data is stored and how it is structured.</li><li>Learned how to convert macOS timestamps and filter by date.</li><li>Aggregated usage time per application, converting seconds to minutes.</li><li>Addressed time zone issues for accurate reporting.</li></ul><p>For further extensions, you might consider:</p><ul><li>Integrating this data into a custom dashboard.</li><li>Building visual reports using tools like Grafana.</li><li>Automating data collection for long-term usage analysis.</li></ul><h4>Appendix</h4><h3>Complete SQL Code for Yesterday’s Data (Adjusted for +10 Time Zone)</h3><pre>SELECT<br>    ZOBJECT.ZVALUESTRING AS &#39;App Identifier&#39;,<br>    SUM(ZOBJECT.ZENDDATE - ZOBJECT.ZSTARTDATE) / 60.0 AS &#39;Usage Duration (minutes)&#39;<br>FROM ZOBJECT<br>WHERE ZSTREAMNAME = &#39;/app/usage&#39;<br>  AND date(DATETIME(ZOBJECT.ZSTARTDATE + 978307200, &#39;unixepoch&#39;, &#39;+10 hours&#39;)) = date(&#39;now&#39;, &#39;-1 day&#39;, &#39;+8 hours&#39;)<br>GROUP BY ZOBJECT.ZVALUESTRING<br>ORDER BY SUM(ZOBJECT.ZENDDATE - ZOBJECT.ZSTARTDATE) / 60.0 DESC;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/904/1*aoW2GjbmoQa8sah47vaT7A.png" /></figure><h4>References and Resources</h4><ul><li><a href="https://github.com/Evian-Zhang/ScreenTimeController">ScreenTimeController on GitHub</a></li><li>Discussions on <a href="https://stackoverflow.com/">Stack Overflow</a></li><li>Official SQLite documentation</li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=66e269278ba5" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>