calendar-stats

Calendar Stats — Meeting Volume and Time Audit

Show meeting statistics for a time period.

Arguments

$ARGUMENTS — time range (default: last 30 days):

• "last week", "this month", "last 90 days"
• "2026-01-01 to 2026-03-01" → date range
• Empty → last 30 days

Steps

DB="$HOME/Library/Group Containers/group.com.apple.calendar/Calendar.sqlitedb"
ARGS="$ARGUMENTS"

DAYS=30
if echo "$ARGS" | grep -qiE '[0-9]+ day'; then DAYS=$(echo "$ARGS" | grep -oiE '[0-9]+' | head -1); fi
if echo "$ARGS" | grep -qi 'week'; then DAYS=7; fi
if echo "$ARGS" | grep -qi '90'; then DAYS=90; fi

START_CD=$(( $(date +%s) - DAYS * 86400 - 978307200 ))
END_CD=$(( $(date +%s) - 978307200 ))

echo "=== OVERVIEW ==="
sqlite3 "$DB" "
SELECT
  COUNT(DISTINCT ci.ROWID) as total_events,
  SUM(CASE WHEN ci.all_day = 0 THEN
    MAX(0, MIN(COALESCE(oc.occurrence_end_date, oc.occurrence_date + 3600),
               COALESCE(oc.occurrence_start_date, oc.occurrence_date) + 86400)
        - COALESCE(oc.occurrence_start_date, oc.occurrence_date))
  ELSE 0 END) / 3600.0 as total_hours,
  COUNT(DISTINCT CASE WHEN ci.has_attendees = 1 THEN ci.ROWID END) as meetings_with_others,
  COUNT(DISTINCT CASE WHEN ci.all_day = 1 THEN ci.ROWID END) as all_day_events
FROM OccurrenceCache oc
JOIN CalendarItem ci ON oc.event_id = ci.ROWID
LEFT JOIN Store s ON (SELECT store_id FROM Calendar WHERE ROWID = ci.calendar_id)
WHERE oc.occurrence_date >= $START_CD
  AND oc.occurrence_date <= $END_CD
  AND ci.hidden = 0
  AND ci.status != 2
  AND s.type != 5
  AND s.disabled = 0;
"

echo ""
echo "=== BY DAY OF WEEK ==="
sqlite3 -separator '|' "$DB" "
SELECT
  CASE strftime('%w', oc.occurrence_date + 978307200, 'unixepoch')
    WHEN '0' THEN 'Sunday'
    WHEN '1' THEN 'Monday'
    WHEN '2' THEN 'Tuesday'
    WHEN '3' THEN 'Wednesday'
    WHEN '4' THEN 'Thursday'
    WHEN '5' THEN 'Friday'
    WHEN '6' THEN 'Saturday'
  END as weekday,
  COUNT(DISTINCT ci.ROWID) as events,
  ROUND(SUM(CASE WHEN ci.all_day = 0 THEN
    MAX(0, COALESCE(oc.occurrence_end_date, oc.occurrence_date + 3600)
        - COALESCE(oc.occurrence_start_date, oc.occurrence_date))
  ELSE 0 END) / 3600.0, 1) as hours
FROM OccurrenceCache oc
JOIN CalendarItem ci ON oc.event_id = ci.ROWID
LEFT JOIN Store s ON (SELECT store_id FROM Calendar WHERE ROWID = ci.calendar_id)
WHERE oc.occurrence_date >= $START_CD AND oc.occurrence_date <= $END_CD
  AND ci.hidden = 0 AND ci.status != 2 AND s.type != 5 AND s.disabled = 0
GROUP BY strftime('%w', oc.occurrence_date + 978307200, 'unixepoch')
ORDER BY events DESC;
"

echo ""
echo "=== TOP CALENDARS ==="
sqlite3 -separator '|' "$DB" "
SELECT c.title, s.name, COUNT(DISTINCT ci.ROWID) as events
FROM OccurrenceCache oc
JOIN CalendarItem ci ON oc.event_id = ci.ROWID
JOIN Calendar c ON ci.calendar_id = c.ROWID
JOIN Store s ON c.store_id = s.ROWID
WHERE oc.occurrence_date >= $START_CD AND oc.occurrence_date <= $END_CD
  AND ci.hidden = 0 AND ci.status != 2 AND s.type != 5 AND s.disabled = 0
GROUP BY c.ROWID ORDER BY events DESC LIMIT 10;
"

echo ""
echo "=== BUSIEST DAYS ==="
sqlite3 -separator '|' "$DB" "
SELECT
  date(oc.occurrence_date + 978307200, 'unixepoch', 'localtime') as day,
  COUNT(DISTINCT ci.ROWID) as events
FROM OccurrenceCache oc
JOIN CalendarItem ci ON oc.event_id = ci.ROWID
LEFT JOIN Store s ON (SELECT store_id FROM Calendar WHERE ROWID = ci.calendar_id)
WHERE oc.occurrence_date >= $START_CD AND oc.occurrence_date <= $END_CD
  AND ci.hidden = 0 AND ci.status != 2 AND s.type != 5 AND s.disabled = 0
GROUP BY day ORDER BY events DESC LIMIT 5;
"