Join two data frames SQL where and between overlaps [duplicate]

Question

I am trying to join two data frames which in SQL would utilise a where and a between statement for dates.

In SQL, the code would be:

select Date,(Value1-Test1) as Ans1,(Value2-Test2) as Ans2,ID
from Data a
 inner join Test b on a.ID=b.ID and a.Date between b.DateStart and c.DateEnd 

This is Data

Date                Value1  Value2  ID
01/01/16 19:30:00       10      30  A
01/01/16 19:50:20       20      40  B
01/01/16 19:55:30       30      50  C

This is Test

RowNumber   DateStart            DateEnd    Test1   Test2   ID
1   01/01/16 17:00:00   01/01/16 22:00:05   2          4    A
2   01/01/16 22:00:06   01/01/16 01:50:00   3          6    A
3   01/01/16 17:00:00   01/01/16 22:00:05   4          8    B
4   01/01/16 22:00:06   01/01/16 01:50:00   5          2    B
5   01/01/16 17:00:00   01/01/16 22:00:05   6          4    C
6   01/01/16 22:00:06   01/01/16 01:50:00   7          5    C

The results I am trying to create

Date                  Ans1    Ans2 ID
01/01/16 19:30:00        8      26  A
01/01/16 19:50:12       16      32  B
01/01/16 19:55:24       24      46  C

Any help and pointers would be great.

Answer 1

Following advice from @zx8754 I have tried to use data.table::foverlaps()

In Data, rename the Date field to DateStart and create a second date field where DateEnd=Date. Add the following code:

setkey(Data,ID,DateStart,DateEnd) 
setkey(Test,Id,DateStart,DateEnd) 
CompleteDataset <- foverlaps(Data, Test, type="any")

This give me exactly what I want.

Finding Overlaps between interval sets / Efficient Overlap Joins