title: How to check if a certain event hour is between a range of hours in PostgreSQL?
body: <p>I have a turn shifts table like this (simplified)</p>
  
  <pre><code> id | hr_start |  hr_end
  ----+----------+----------
    1 | 08:00:00 | 15:59:59
    2 | 16:00:00 | 21:59:59
    3 | 22:00:00 | 03:59:59
    4 | 04:00:00 | 07:59:59
  </code></pre>
  
  <p>And I have alarm events (timestamps) that I need to check against a turn shift.</p>
  
  <p>For example if the alarm event occurs at 14:34 Hrs then the shift 1 is assigned, if the event occurs at 23:20 Hrs then the shift 3 is assigned, and so on.</p>
  
  <p>My first approach was like this:</p>
  
  <pre><code>SELECT id 
  FROM shifts 
  WHERE hr_start &lt; '09:23:00':TIME AND hr_end &gt; '09:23:00':TIME;
  </code></pre>
  
  <p>And yes this works ok with ids 1,2 and 4, but not 3.</p>
  
  <p>Then I attempt a BETWEEN query like this:</p>
  
  <pre><code>SELECT id 
  FROM shifts 
  WHERE '09:23:00':TIME BETWEEN r_start AND hr_end;
  </code></pre>
  
  <p>With the same results</p>
  
  <p><strong>How I can retrieve the ID = 3 record for an event that occurs in the range of 22:00 Hrs to 03:59 Hrs?</strong></p>
  
  <p><a href="http://sqlfiddle.com/#!15/1bb0d/13" rel="nofollow">My attempts in this sqlfiddle</a></p>