Reading your own time data with sqlite3 in the Terminal
· 7 min read
A lot of Mac apps keep their data in a single SQLite file, and SQLite is one of the few formats you can count on being readable in ten years. Your Mac already ships with the sqlite3 command, so you do not need to install anything to look inside. This is how to do it safely: copy the file, discover what is in it rather than assuming, and then ask your own questions of your own data.
Why this is worth knowing
If an app stores your time records in a file you own, in a format with a published specification, then you are not dependent on that app to read your history. That is the practical meaning of “your data is yours”. It is also the difference between a local file and a row in someone else’s database.
There are three ordinary reasons to open the file yourself:
- You want a total the app does not show, such as hours in one app across a specific fortnight.
- You want to check that the app is storing what it says it stores, and nothing more.
- You are archiving, and you want to be sure the archive is readable without the app.
Find the file
Most apps keep their data under Application Support in your own Library folder. That folder is hidden in Finder by default; hold Option while opening the Go menu to reveal Library, or press Command-Shift-G and type the path.
Punchcard keeps everything in one file at ~/Library/Application Support/Punchcard/punchcard.sqlite. There is no second copy anywhere, no cloud mirror and no account, because the app contains no networking code. Where Mac apps keep your data, and how to find it covers the general pattern for other apps.
Copy it before you touch it
This is the only rule that really matters. Never run queries against the live file an app is using. A copy costs nothing and removes every risk of locking or corrupting the original.
Open Terminal and run:
cd ~/Desktop
cp ~/Library/Application\ Support/Punchcard/punchcard.sqlite ./punchcard-copy.sqlite
If the app is running, quit it first, so the copy is taken at a clean moment. Now everything below happens on the copy on your Desktop, and the worst possible outcome is that you delete a file you can make again.
Open it and look around
sqlite3 punchcard-copy.sqlite
You get a sqlite> prompt. Before anything else, set up the display and see what exists:
.headers on
.mode column
.tables
.tables lists the tables in the file. Do not guess at names from a blog post, including this one: read what your own copy tells you, because schemas change between versions. Then, for any table you care about:
.schema <tablename>
That prints the exact column definitions. Read it before writing a query. Ten seconds here saves ten minutes of confusion about whether a column holds seconds, minutes or a timestamp.
To see a few real rows and learn the shape of the data:
SELECT * FROM <tablename> LIMIT 5;
Every statement ends with a semicolon. If the prompt changes to ...> you forgot one; type a semicolon and press Return. To leave, type .quit.
Useful queries once you know the columns
With the schema in front of you, the questions you actually want are short. Substitute your real table and column names into these shapes.
Total per app, biggest first:
SELECT app, SUM(seconds) AS total
FROM <table>
GROUP BY app
ORDER BY total DESC;
Seconds into readable hours and minutes:
SELECT app,
SUM(seconds)/3600 AS h,
(SUM(seconds)%3600)/60 AS m
FROM <table>
GROUP BY app
ORDER BY SUM(seconds) DESC;
One date range:
SELECT app, SUM(seconds) AS total
FROM <table>
WHERE day BETWEEN '2026-09-01' AND '2026-09-14'
GROUP BY app
ORDER BY total DESC;
A day total per date, to see the shape of a month:
SELECT day, SUM(seconds)/3600.0 AS hours
FROM <table>
GROUP BY day
ORDER BY day;
If timestamps are stored as Unix epoch numbers rather than date strings, wrap them: date(ts, 'unixepoch', 'localtime') gives you a date you can group by. That single function solves most of the awkwardness you will run into.
Getting the answer out of the Terminal
Two dot-commands turn any query into a file you can open in a spreadsheet:
.headers on
.mode csv
.output september.csv
SELECT day, app, seconds FROM <table> ORDER BY day;
.output stdout
The file lands in whatever folder you started sqlite3 from. You can also do the whole thing in one line without entering the prompt at all:
sqlite3 -header -csv punchcard-copy.sqlite "SELECT * FROM <table>;" > out.csv
That form is the one to remember, because it drops straight into a script if you ever want a monthly export without thinking about it.
Worth saying plainly: if all you want is a CSV of your time data, the app has an export for that and you should use it. Export your time data to CSV and keep it forever covers the built-in route. The Terminal is for the questions the export does not answer.
Read-only, and why you should keep it that way
SQLite will happily let you write to the file. Resist. An app expects its own schema invariants to hold, and an UPDATE that looks harmless can leave the data in a state the app does not handle. If you want to guarantee you cannot do damage, open the copy in read-only mode:
sqlite3 'file:punchcard-copy.sqlite?mode=ro'
Editing history is also the wrong instinct for a time record. A log you correct by hand is no longer evidence of anything. If a day is wrong, note why in the margin rather than rewriting it.
What the file tells you about the app
There is one more thing you get from doing this once. Looking at the actual columns is the most direct privacy audit available. If a tracker claims to record app names only, the schema either backs that up or it does not. You are not reading a policy; you are reading what is stored.
In Punchcard’s case the claim is that it stores app names and time, never window titles, document names, URLs, keystrokes or screen contents, and that it asks for no macOS permissions. The file is the place to check. Time tracker data: where it lives and who can read it explains why that check is worth doing for any tool that watches your day, and How to delete everything an app knows about you covers the other end of the lifecycle.
Questions
Do I need to install anything to read a SQLite file on a Mac?
No. The sqlite3 command is included with macOS. Open Terminal and run it against a copy of the file.
Is it safe to query the file while the app is running? Copy it first and quit the app if you can. Reading a live database can hit locks, and there is no reason to take the risk when a copy is one command away.
Can I break the app by looking at the file? Not by reading. You can break things by writing, so work on a copy and use read-only mode if you want a guarantee.
What if the tables are not named what I expected?
Trust .tables and .schema over any guide, including this one. Schemas change between app versions, and your own file is the only authority.