Script to parse Mac's screen time

parse_screen_time.go

package main

import (
	"database/sql"
	"fmt"
	"log"
	"time"

	_ "github.com/mattn/go-sqlite3"
)

func main() {
	db, err := sql.Open("sqlite3", "file:./knowledgeC.db?_loc=auto") // copy from ~/Library/Application Support/Knowledge/knowledgeC.db
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()
  // https://rud.is/b/2019/10/28/spelunking-macos-screentime-app-usage-with-r/
	sqlStmt := `
SELECT
  ZOBJECT.ZVALUESTRING AS "app",
    (ZOBJECT.ZENDDATE - ZOBJECT.ZSTARTDATE) AS "usage",
    CASE ZOBJECT.ZSTARTDAYOFWEEK
      WHEN "1" THEN "Sunday"
      WHEN "2" THEN "Monday"
      WHEN "3" THEN "Tuesday"
      WHEN "4" THEN "Wednesday"
      WHEN "5" THEN "Thursday"
      WHEN "6" THEN "Friday"
      WHEN "7" THEN "Saturday"
    END "dow",
    ZOBJECT.ZSECONDSFROMGMT/3600 AS "tz",
    DATETIME(ZOBJECT.ZSTARTDATE + 978307200, 'UNIXEPOCH') as "start_time",
    DATETIME(ZOBJECT.ZENDDATE + 978307200, 'UNIXEPOCH') as "end_time",
    DATETIME(ZOBJECT.ZCREATIONDATE + 978307200, 'UNIXEPOCH') as "created_at",
    CASE ZMODEL
      WHEN ZMODEL THEN ZMODEL
      ELSE "Other"
    END "source"
  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"
	`
	rows, err := db.Query(sqlStmt)
	if err != nil {
		log.Panic(err)
	}
	defer rows.Close()
	groupByDate := map[string]time.Time{}
	endGroupByDate := map[string]time.Time{}
	for rows.Next() {
		var app, dow, source, startTimeStr, endTimeStr, createdAtStr string
		var usage, tz int
		err = rows.Scan(&app, &usage, &dow, &tz, &startTimeStr, &endTimeStr, &createdAtStr, &source)
		if err != nil {
			log.Panic(err)
		}
		startTime, err := parseTime(startTimeStr, tz)
		if err != nil {
			log.Panic(err)
		}
		endTime, err := parseTime(endTimeStr, tz)
		if err != nil {
			log.Panic(err)
		}
		// fmt.Println(app, usage, dow, tz, startTime, endTime, createdAtStr, source)
		if t, ok := groupByDate[startTime.Format("2006-01-02")]; !ok || t.After(startTime) {
			groupByDate[startTime.Format("2006-01-02")] = startTime
		}
		if t, ok := endGroupByDate[endTime.Format("2006-01-02")]; !ok || t.Before(endTime) {
			endGroupByDate[endTime.Format("2006-01-02")] = endTime
		}
	}
	for dt, t := range groupByDate {
		fmt.Printf("%v %v %v\n", dt, t, endGroupByDate[dt])
	}
}

func parseTime(value string, tz int) (time.Time, error) {
	startTimeUTC, err := time.ParseInLocation("2006-01-02  15:04:05", value, time.UTC)
	if err != nil {
		return time.Time{}, err
	}
	return startTimeUTC.In(time.FixedZone("zone", tz*int(time.Hour.Seconds()))), nil
}