Filter Spark DataFrame if between any set of points in list
<p>I have a spark dataframe (using the scala interface) that has columns of timestamp, asset (a string), tag (a string) and value (a double). Here is an excerpt of it:</p>

<pre><code>+--------------------+-----+--------+-------------------+
|           timestamp|asset|     tag|              value|
+--------------------+-----+--------+-------------------+
|2013-01-03 23:36:...|   G4| BTGJ2_2|      116.985626221|
|2013-01-15 00:36:...|   G4| TTXD1_6|       66.887382507|
|2013-01-05 13:03:...|   G4|TTXD1_22|       40.913497925|
|2013-01-12 04:43:...|   G4|TTXD1_23|       60.834510803|
|2013-01-08 17:54:...|   G4|   LTB1D|      106.534744263|
|2013-01-02 04:15:...|   G4|    WEXH|      255.981292725|
|2013-01-07 10:54:...|   G4| BTTA1_7|      100.743843079|
|2013-01-05 11:29:...|   G4| CDFH_10|      388.560668945|
|2013-01-10 09:10:...|   G4|   LTB1D|      112.226242065|
|2013-01-13 15:09:...|   G4|TTXD1_15|       63.970848083|
|2013-01-15 01:23:...|   G4|    TTIB|       67.993904114|
</code></pre>

<p>I also have an <code>Array[List[Timestamp]]</code>, where each <code>List</code> is of size two and holds starting and ending times for intervals of interest. For example:</p>

<pre><code>event_times: Array[List[java.sql.Timestamp]] = Array(List(2013-01-02 00:00:00.0, 2013-01-02 12:00:00.0), List(2013-01-10 00:00:00.0, 2013-01-12 06:00:00.0))
</code></pre>

<p>holds two intervals of interest: one from midnight to 12:00 on 2013-01-02, and another from midnight 2013-01-10 to 6:00 on 2013-01-12</p>

<p>Here's my question: How can I filter the dataframe to return values such that the timestamp is in <em>any</em> of the intervals? For any one interval, I can do</p>

<pre><code>df.filter(df("timestamp").between(start, end))
</code></pre>

<p>Since I don't know how many elements are in the <code>Array</code> (how many intervals I have), I can't just have a long series of filters.</p>

<p>For the example above, I would want to keep rows 4, 6, and 9.</p>

<p>What I have now is a loop over the <code>Array</code>, and am getting the appropriate subset for each one. However, that is probably slower than having it all be in a big filter right?</p>