title: Checking if a time falls within a time range in MySQL
body: <p>I know this can be accomplished on the backend - but I'm wondering if there's any native, or efficient MySQL function that can be used to check if a given <code>time</code> variable falls within a range covered by two <code>time</code> variables (a <code>start_time</code>, and an <code>end_time</code>).</p>
  
  <p>I have a database currently set up which looks like the following;</p>
  
  <pre><code>+----+--------+------------+----------+
  | id | job_id | start_time | end_time |
  +----+--------+------------+----------+
  |  1 |     40 | 13:00:00   | 14:00:00 |
  |  2 |     44 | 14:45:00   | 15:00:00 |
  |  3 |     45 | 15:10:00   | 15:30:00 |
  +----+--------+------------+----------+
  </code></pre>
  
  <p>The backend accepts a <code>start_time</code>, and an <code>end_time</code> with a <code>job_id</code> - and then it looks to see if it can be fit in anywhere. So for example, given a start_time of <code>13:30:00</code>, and an end_time of <code>13:45:00</code>, the backend should reject this job request as there is no time available for it (it would overlap with the entry at <code>id 1</code>.)</p>
  
  <p>However, if a job is submitted with a start_time of <code>14:10:00</code>, and an end time of <code>14:20:00</code>, it should be accepted - as this does not overlap with any existing tasks.</p>
  
  <p>The following query is great to tell if a job can be submitted for say, 13:00:00 until 14:00:00 (an exact duplicate of id 1);</p>
  
  <pre><code>SELECT * WHERE start_time &gt;= '13:00:00' AND end_time &lt;= '14:00:00';
  </code></pre>
  
  <p>But if the start_time becomes 13:01:00, then the query falls down - as the start_time is less than 13:01:00, at 13:00:00. So it'll get approved for insertion, as the above query will return no overlapping results.</p>
  
  <p>If we change the query to an <code>OR</code> clause on <code>end_time</code>, then literally any job that doesn't end before 14:00:00 would be rejected.</p>
  
  <p>Can anyone suggest a simple way of taking an input variable of a <code>time</code> type, and checking if it falls within range of all available <code>start_time</code>, and <code>end_time</code> variables as noted in the db above?</p>