Keeping timesheet of working hours
When working with clients it’s quite typical that they want to get a timesheet of working hours. It’s somehow very difficult for myself to actually mark my working hours to a sheet and so far there has been a mutual trust so I have marked just a static nine to five work day for each day and be done with it. Sometimes I work a bit less and sometimes bit more on somedays but if it roughly resembles the truth you’re done and nothing else is needed.
But we are not looking for sensible solutions for this problem in this blog post and if you anyway want to overengineer your timesheets I recommend using MacOS Screen Time which is built-in feature of Macs. This requires you to use MacOS and that you use that machine solely for work. It also requires some SQL knowledge because we are again using my favorite tool
duckdb.Overengineering the timesheet with MacOS Screen Time.
MacOS uses
sqlite database where all used activities are stored:$ ls -lah $HOME/Library/Application\ Support/Knowledge/knowledgeC.db -rw-r--r-- 1 onnimonni staff 27M 26 Huh 10:27 /Users/onnimonni/Library/Application Support/Knowledge/knowledgeC.db
We can easily connect to the database with duckdb by using the
sqlite extension and using the knowledgeC database inside it:
# Show which kind it's storing duckdb --json -c " INSTALL sqlite; LOAD sqlite; ATTACH '$HOME/Library/Application Support/Knowledge/knowledgeC.db' (TYPE sqlite); USE knowledgeC; SELECT * FROM ZOBJECT WHERE ZSTREAMNAME = '/app/usage' LIMIT 1; " | jq
This should result in a roughly following output:
[ { "Z_PK": 379494, "Z_ENT": 11, "Z_OPT": 1, "ZUUIDHASH": -1903675596643927782, "ZEVENT": null, "ZSOURCE": null, "ZCATEGORYTYPE": null, "ZINTEGERVALUE": null, "ZCOMPATIBILITYVERSION": 0, "ZENDDAYOFWEEK": 4, "ZENDSECONDOFDAY": 61045, "ZHASCUSTOMMETADATA": 0, "ZHASSTRUCTUREDMETADATA": 1, "ZSECONDSFROMGMT": 7200, "ZSHOULDSYNC": 0, "ZSTARTDAYOFWEEK": 4, "ZSTARTSECONDOFDAY": 61042, "ZVALUECLASS": 1, "ZVALUEINTEGER": 2961235614696431925, "ZVALUETYPECODE": 6584185901589580638, "ZSTRUCTUREDMETADATA": 2, "ZVALUE": null, "Z9_VALUE": null, "ZIDENTIFIERTYPE": null, "ZQUANTITYTYPE": null, "ZCREATIONDATE": "1994-04-02 16:57:25.750746", "ZLOCALCREATIONDATE": "1994-04-02 16:57:25.750746", "ZCONFIDENCE": 1.0, "ZENDDATE": "1994-04-02 16:57:25", "ZSTARTDATE": "1994-04-02 16:57:22", "ZVALUEDOUBLE": 2.961235614696432E+18, "ZDOUBLEVALUE": null, "ZUUID": "66EF8D99-3C9F-4FC3-95BA-F4C792BFB9CB", "ZSTREAMNAME": "/app/usage", "ZVALUESTRING": "com.apple.finder", "ZSTRING": null, "ZMETADATA": null } ]
The time stamps were pretty weird. I certainly did not use my Mac during
1994 😄. I learned from this blog post about macOS Screen Time that MacOS stores the times in a bit weird way and we need to offset them by 978307200 seconds aka 31 years to get the current dates.Let’s do that next:
duckdb --markdown -c " INSTALL sqlite; LOAD sqlite; ATTACH '$HOME/Library/Application Support/Knowledge/knowledgeC.db' (TYPE sqlite); USE knowledgeC; WITH app_usage AS ( SELECT split(ZOBJECT.ZVALUESTRING,'.')[-1] AS App, epoch(ZOBJECT.ZENDDATE - ZOBJECT.ZSTARTDATE) AS Duration_seconds, timezone('UTC', ZOBJECT.ZSTARTDATE + INTERVAL 31 YEARS) AS Start_timestamp, timezone('UTC', ZOBJECT.ZENDDATE + INTERVAL 31 YEARS) AS End_timestamp, FROM ZOBJECT WHERE ZSTREAMNAME = '/app/usage' ) SELECT * FROM app_usage LIMIT 5 "
We can then copy paste the resulting markdown table here to my blog editor and the results that I was context switching between way too much during
2025-04-02 evening:App | Duration_seconds | Start_timestamp | End_timestamp |
finder | 3.0 | 2025-04-02 18:57:22+02 | 2025-04-02 18:57:25+02 |
slackmacgap | 5.0 | 2025-04-02 18:57:25+02 | 2025-04-02 18:57:30+02 |
Safari | 39.0 | 2025-04-02 18:57:30+02 | 2025-04-02 18:58:09+02 |
iterm2 | 3.0 | 2025-04-02 18:58:09+02 | 2025-04-02 18:58:12+02 |
Chrome | 15.0 | 2025-04-02 18:58:12+02 | 2025-04-02 18:58:27+02 |
Let’s extend this to get weekly timesheet from all of the tracked hours and also the times when we started the working day and let’s also calculate the working hours from start to end:
duckdb --markdown -c " INSTALL sqlite; LOAD sqlite; ATTACH '$HOME/Library/Application Support/Knowledge/knowledgeC.db' (TYPE sqlite); USE knowledgeC; WITH app_usage AS ( SELECT split(ZOBJECT.ZVALUESTRING,'.')[-1] AS App, epoch(ZOBJECT.ZENDDATE - ZOBJECT.ZSTARTDATE) AS Duration_seconds, timezone('UTC', ZOBJECT.ZSTARTDATE + INTERVAL 31 YEARS) AS Start_timestamp, timezone('UTC', ZOBJECT.ZENDDATE + INTERVAL 31 YEARS) AS End_timestamp, FROM ZOBJECT WHERE ZSTREAMNAME = '/app/usage' ) SELECT Start_timestamp::date as working_date, INTERVAL (SUM(Duration_seconds)) SECONDS AS computer_usage_time, MAX(End_timestamp) - MIN(Start_timestamp) AS workday_duration, strftime(MIN(Start_timestamp), '%H:%M:%S') as started_at, strftime(MAX(End_timestamp), '%H:%M:%S') as ended_at FROM app_usage WHERE Start_timestamp >= current_localtimestamp() - INTERVAL '7 days' GROUP BY working_date ORDER BY working_date DESC "
We can then see nice results of this data:
working_date | computer_usage_time | workday_duration | started_at | ended_at |
2025-04-26 | 00:44:25 | 03:41:06 | 10:09:22 | 13:50:28 |
2025-04-25 | 06:23:25 | 08:28:40 | 14:15:34 | 22:44:14 |
2025-04-24 | 06:43:49 | 15:17:27 | 08:22:30 | 23:39:57 |
2025-04-23 | 03:39:48 | 08:30:55 | 14:44:16 | 23:15:11 |
2025-04-22 | 02:05:52 | 05:11:24 | 18:16:05 | 23:27:29 |
2025-04-21 | 08:38:21 | 14:46:23 | 08:06:58 | 22:53:21 |
2025-04-20 | 00:27:59 | 02:25:00 | 19:34:29 | 21:59:29 |
2025-04-19 | 00:57:06 | 07:08:44 | 15:46:08 | 22:54:52 |
You probably want to use
--csv flag when you send this to your clients. I’m the kind of software contractor who will also invoice for the hours when sitting in the office in a toilet but sadly MacOS Screen Time can’t track that 🚽. (Also currently I’m not working for anyone except myself).
To actually use this one probably needs to still calculate some fair assumption from the computer usage time vs billable hours. Have fun mangling your own data and let me know if there’s a better way to use this data 📊