Merge dataframes on timestamps and time intervals using data.table in R
You'll need to use by.x to get around setting x's key.

Also, note that foverlaps can merge on any number of keys, and then do the overlaps only on the final two. What that means here is that you can do the x.Receiver == i.Receiver filter up-front, which also simplifies the clean-up step.

x = as.data.table(x)
x[ , end := DateTimePST]
y = as.data.table(y)

setkey(y, Receiver, Start, End)

result = foverlaps(x, y, by.x = c('Receiver', 'DateTimePST', 'end'), type = 'within')
result[ , end := NULL]

setDF(result)


I do find it a bit awkward/strange that you've got to define end when by.x = c('Receiver', 'DateTimePST', 'DateTimePST') would appear to be fine, and have filed a feature request.

If you don't need DateTimePST in your output, you might find the non-equi-join version more compact / easier to read:

y[x, on = .(Receiver == Receiver, Start &lt; DateTimePST, End &gt; DateTimePST)]
#    Station Receiver               Start                 End
# 1:  YBAAG4   102140 2014-11-08 13:11:36 2014-11-08 13:11:36
# 2:  YBCSSW   112568 2013-10-02 18:47:24 2013-10-02 18:47:24
# 3:  YBCSSW   112568 2016-09-22 08:01:16 2016-09-22 08:01:16
# 4:   YBBCD   121907 2017-11-22 12:53:55 2017-11-22 12:53:55
#    TagID
# 1: 20161
# 2: 23055
# 3: 45428
# 4:  2627


Note the order y[x -- we're using x to "look up" rows of y.

If you do need DateTimePST, it's just a bit of an ugly extension:

y[x, c(.SD, list(DateTimePST=DateTimePST)),
  on = .(Receiver == Receiver, Start &lt; DateTimePST, End &gt; DateTimePST)]


Finally, we can think of this as adding columns to x by a non-equi-join with y by reversing the order:

x[y, `:=`(Start = i.Start, End = i.End, Station = i.Station),
  on = .(Receiver == Receiver, DateTimePST &gt; Start, DateTimePST &lt; End)]