title: Hive: How do I join with a between dates condition?
body: <p>I have table of items:</p>
  
  <pre><code>| id | dateTimeUTC      | color | 
  +----+------------------+-------+
  | 1  | 1/1/2001 1:11:11 | Red   | 
  +----+------------------+-------+
  | 2  | 2/2/2002 2:22:22 | Blue  |
  +----+------------------+-------+
  </code></pre>
  
  <p>It contains some events with a dateTime in it. I also have an events table: </p>
  
  <pre><code>| eventID | startDate         | endDate          |
  +---------+-------------------+------------------+
  | 1       | 1/1/2001 1:11:11  | 2/2/2002 2:22:22 |
  +---------+-------------------+------------------+
  | 2       | 3/3/2003 00:00:00 | 3/3/2003 1:11:11 |
  +---------+-------------------+------------------+
  </code></pre>
  
  <p>I want to join the two, getting where the dateTimeUTC of the item table is in between the start and end date of the events table. Now, to do this in sql is pretty standard, but HQL not so much. Hive doesn't let you have anything but an "=" in the join clause. (Link to HIVE info here: <a href="https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Joins" rel="nofollow noreferrer">https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Joins</a>). Now, there was a question about a similar situation before <a href="https://stackoverflow.com/questions/35944968/join-tables-on-date-range-in-hive">here</a>, but I found that it's been 4 years since then and have hoped there was a solution. </p>
  
  <p>Any tips on how to make this happen? </p>