How to check if a certain event hour is between a range of hours in PostgreSQL?

Question

I have a turn shifts table like this (simplified)

 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

And I have alarm events (timestamps) that I need to check against a turn shift.

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.

My first approach was like this:

SELECT id 
FROM shifts 
WHERE hr_start < '09:23:00':TIME AND hr_end > '09:23:00':TIME;

And yes this works ok with ids 1,2 and 4, but not 3.

Then I attempt a BETWEEN query like this:

SELECT id 
FROM shifts 
WHERE '09:23:00':TIME BETWEEN r_start AND hr_end;

With the same results

How I can retrieve the ID = 3 record for an event that occurs in the range of 22:00 Hrs to 03:59 Hrs?

My attempts in this sqlfiddle

Answer 1

Use CASE in WHERE condition:

SELECT id 
FROM shifts
CROSS JOIN (SELECT '23:00:00'::TIME AS event) sub
WHERE 
    CASE WHEN hr_start <= hr_end THEN hr_start <= event AND hr_end >= event
    ELSE hr_start <= event OR hr_end >= event END;

Note. I have added CROSS JOIN to easier test the query.