Skip to content

Instantly share code, notes, and snippets.

@hannes
Created April 20, 2018 07:42
Show Gist options
  • Save hannes/684400cf771b06bc1faaf79cd8eebe06 to your computer and use it in GitHub Desktop.
Save hannes/684400cf771b06bc1faaf79cd8eebe06 to your computer and use it in GitHub Desktop.
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")
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment