title: Efficiently calculating time interval overlaps with data.table
body: <p>I have a large <code>data.table</code> with ~1.5 million rows of start and end <code>POSIXct</code> times. For each of these rows I want to calculate the percentage of the start-end interval that overalps with each start-end in the rest of the data, and save a subset of the high-overlap rows to a separate table. This is straightforward to do with a <code>for</code> loop, however my current approach takes a very long time to run. In the code below, looping through just the first 1,000 rows takes ~20 seconds on my desktop; it seems the main subseting (i.e., <code>temp &lt;- dt[end_time &gt; focal$start_time &amp; start_time &lt; focal$end_time]</code>) is the bulk of the processing. Is there another approach that would be faster?</p>
  <pre><code>library(data.table)
  set.seed(123)
  n &lt;- 1500000
  start_times &lt;- as.POSIXct(runif(n, as.POSIXct(&quot;2000-01-01&quot;), as.POSIXct(&quot;2010-01-01&quot;)), origin = &quot;1970-01-01&quot;)
  end_times &lt;- start_times + as.double(runif(n)*1000000)
  dt &lt;- data.table(
    &quot;id&quot; = 1:n,
    &quot;start_time&quot; = start_times,
    &quot;end_time&quot; = end_times)
  dt[, window_length_hours := as.double(end_time - start_time, units = &quot;hours&quot;)]
  
  start &lt;- Sys.time()
  choice_list &lt;- data.table(&quot;focal_id&quot; = character(), option_id = character(), overlap = double())
  for(i in 1:1000){
    focal &lt;- dt[i]
    temp &lt;- dt[end_time &gt; focal$start_time &amp; start_time &lt; focal$end_time]
    temp[, window_overlap_pct := (focal$window_length_hours - pmax(0, as.double(focal$end_time - end_time, units=&quot;hours&quot;)) - pmax(0, as.double(start_time - focal$start_time, units=&quot;hours&quot;)))/focal$window_length_hours]
  
    sample &lt;- unique(temp[window_overlap_pct &gt; .80][sample(.N, 2, replace = T)]) # save two rows that have high overlap
    
    choice_list &lt;- rbindlist(list(choice_list,
                                   list(&quot;focal_id&quot; = focal$id, &quot;option_id&quot; = sample$id, &quot;overlap&quot; = sample$window_overlap_pct),
                                   list(&quot;focal_id&quot; = focal$id, &quot;option_id&quot; = focal$id, &quot;overlap&quot; = NA)))
  }
  end &lt;- Sys.time()
  end-start
  </code></pre>