In accordance with a recent ruling of the European Court of Justice, employee hours will have to be tracked. In many cases, this will result in simple self-tracking. There are a number of time tracking applications available, but end-user solutions often don’t have open APIs and data are yielded to companies with questionable privacy policies. Commercial solutions specifically directed at businesses may include features for

  • planning ahead
  • screen recording, key and GPS logging
  • IP-address logging
  • clock-in/clock-out

In many cases, this level of surveillance is not required and you can easily reproduce the most important functionality by simply accessing pre-existing data from digital calendars. For anyone maintaining such a calendar in the first place, extracting the already maintained schedule for time-tracking purposes might be a welcome alternative to data-hungry commercial solutions. In this blog post, I will show you an approach for setting up a personal time tracker using Lightning, Thunderbird’s calendar plugin.

Calendar data in Thunderbird

To protect against possible exploits, the data of each account are stored in a folder with an 8 character, random, alphanumeric string followed by the profile name (the standard is “default”, e.g. “n54x0uxj.default”). Account-specific values or behaviours are called “preferences” in Mozilla programs and are saved in prefs.js for Thunderbird. We will retrieve calendar-specific properties (name, color, type (caldav, local), associated profile_id, and others) from there. Each calendar is also assigned a hash, which we then use to retrieve the actual calendar events for time-tracking.

library(tidyverse)
prefsJsLoc <- paste0("lightningconnector/n54x0uxj.testuser/prefs.js")
prefsJs <- readLines(prefsJsLoc)
calHashPattern <- "[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}"
calProperties <- c("color", "type", "name")
calPropertiesList <- paste0("calendar\\.registry\\..*", calProperties, ".*$")
names(calPropertiesList) <- calProperties

calPropList <- calPropertiesList %>%
  as.list() %>%
  lapply(function(pattern) {
    prefs_extr <- str_extract(string = prefsJs, pattern = pattern)
    data.frame(raw = prefs_extr[!is.na(prefs_extr)]) %>%
      tidyr::separate(col = raw, into = c("cal_id", "value"), sep = ",") %>%
      tidyr::separate(col = value, into = c("value"), sep = " \\(") %>%
      mutate(
        cal_id = str_extract(string = cal_id, pattern = calHashPattern),
        value = gsub("[, \"();]", "", value)
      )
  }) %>%
  Map(function(x, y) { z <- cbind(x, "param" = y) }, ., calProperties) %>%
  Reduce(rbind, .) %>%
  pivot_wider(id_cols = cal_id, names_from = param, values_from = value)

In this case the calendars are local (indicated by type = "storage"), but they could also be sourced from a Google Calendar or CalDav provider. Lightning events are saved in an SQLite database, which is unlocked as long as Thunderbird is closed. The location can be derived from the profile id and name. Note that synchronized calendars (such as CalDav) are stored in cache.sqlite instead of local.sqlite. Here we extract all tables:

cal_sql <- paste0(
  "lightningconnector/n54x0uxj.testuser/calendar-data/local.sqlite"
)

db_con <- DBI::dbConnect(RSQLite::SQLite(), cal_sql)
db_tables <- as.list(DBI::dbListTables(db_con))
names(db_tables) <- db_tables

tables <- lapply(db_tables, function(x) {
  dplyr::tbl(db_con, x) %>% as_tibble()
})

DBI::dbDisconnect(db_con)

All that is left to do is join the tables:

ToPOSIXct <- function(time) as.POSIXct(time / 10^6, origin = "1970-01-01")
timeColumns <- c(
  "time_created", "last_modified", "event_start", "event_end", "event_stamp"
)

calendarDf <- tables$cal_events %>%
  mutate_at(timeColumns, ~ToPOSIXct(.x)) %>%
  left_join(calPropList, by = "cal_id")

And visualize with original calendar colors included:

palette <- unique(calendarDf$color)
names(palette) <- unique(calendarDf$name)

calendarDf %>%
  mutate(
    midpoint = as.POSIXct(
      0.5 * (as.numeric(event_start) + as.numeric(event_end)),
      origin = "1970-01-01"
    )
  ) %>%
  ggplot(aes(fill = name)) +
  theme_minimal() +
  geom_rect(aes(xmin = 0.5, xmax = 1.5, ymax = event_start, ymin = event_end)) +
  geom_text(aes(x = 1, y = midpoint, label = title), angle = 90, size = 4) +
  scale_y_datetime(name = "") +
  scale_x_continuous(breaks = NULL, name = "") +
  coord_flip() +
  scale_fill_manual(name = "", values = palette)

Of course the main point is extracting data in tabular format:

calendarDf %>%
  filter(name == "Work") %>%
  mutate(time_duration = event_end - event_start,
         date = format(event_start, "%d.%M")) %>%
  select(date, time_duration, title) %>%
  knitr::kable()

Or visualizing your work-life balance using semi-informative pie charts:

calendarDf %>%
  mutate(event_duration = event_end - event_start,
         date = format(event_start, "%d.%M"),
         with_dave = ifelse(grepl("Dave", title), TRUE, FALSE)) %>%
  select(name, date, event_duration, title, with_dave) %>%
  group_by(name, with_dave) %>%
  summarize(event_duration_sum = sum(event_duration)) %>%
  ggplot(aes(x = "", y = event_duration_sum, fill = name, alpha = with_dave)) +
  geom_bar(width = 1, stat = "identity") +
  coord_polar("y", start = 0) +
  theme_minimal() +
  scale_alpha_discrete(range = c(0.5, 1)) +
  scale_fill_manual(name = "", values = palette) +
  scale_y_continuous(name = "", breaks = NULL) +
  scale_x_discrete(breaks = NULL, name = "")

Extensions

Using R makes it simple to access and parse Lightning calendar information and set it up in a way that lets you aggregate and visualize past and future time allocation. If you need timesheets and invoices containing billable hours, these can easily be created automatically.

With this approach, time tracking and planning are merged. The tracker doesn’t register possible differences between the expected and actual time usage. If you are willing to invest additional effort, it is possible to maintain a separate “tracking” calendar for this at the cost of giving up information about the event type. A deeper hierarchical ordering of events would be possible by using consistent separators when naming events, which is prone to error in my experience.