applemail.R

library("DBI")
library("ggplot2")

con <- dbConnect(RSQLite::SQLite(), dbname="~/Library/Mail/V5/MailData/Envelope Index", flags=RSQLite::SQLITE_RO)

# messages per month
msg_per_month <- dbGetQuery(con, "SELECT MIN(DATETIME(date_sent, 'unixepoch')) as start, STRFTIME('%m', DATETIME(date_sent, 'unixepoch')) AS month, STRFTIME('%Y', DATETIME(date_sent, 'unixepoch')) AS year, COUNT(*) AS n FROM messages WHERE mailbox IN (SELECT ROWID FROM mailboxes WHERE url LIKE '%Sent') GROUP BY year, month ORDER BY year, month")
msg_per_month$start <- as.Date(msg_per_month$start)

ggplot(msg_per_month, aes(x=start, y=n, group=1)) + geom_line()

# work life balance
msg_per_hour <- dbGetQuery(con, "SELECT STRFTIME('%w', DATETIME(date_sent, 'unixepoch')) AS weekday, STRFTIME('%H', DATETIME(date_sent, 'unixepoch')) AS hour, COUNT(*) AS n FROM messages WHERE mailbox IN (SELECT ROWID FROM mailboxes WHERE url LIKE '%Sent') group by weekday, hour ORDER BY weekday, hour")

msg_per_hour$weekday_name <- ordered(msg_per_hour$weekday, levels=c(0,6,5,4,3,2,1), labels=c("Sunday", "Saturday", "Friday", "Thursday", "Wednesday", "Tuesday", "Monday"))
ggplot(msg_per_hour, aes(y=weekday_name, x=hour)) + geom_tile(aes(fill = n), colour = "white") + scale_fill_gradient(low = "white", high = "red")