Compare an hour between two hour columns Please help. I am kinda new to this database world. I am using PostgreSQL.

I have a table with defined shifts like this:

CREATE TEMPORARY TABLE shifts AS
SELECT id, start_hour::time, end_hour::time
FROM ( VALUES 
  ( 1,'06:00:01','14:00:00' ),
  ( 2,'14:00:01','22:00:00' ),
  ( 3,'22:00:01','06:00:00' )
) AS t(id,start_hour,end_hour);


I have been using the between operator to compare a given hour with the start_hour and end_hour so I can get the corresponding shift, like this

SELECT * from Shifts S where given_hour BETWEEN start_hour AND end_hour;


It works when it is shift 1 or 2 but not with shift 3. It returns no value. Can you please give me a little advice on how should i do this?
Thank you!
You cannot have an interval defined by an "end" smaller than your "start". Your shift 3 does not work because you're crossing day boundaries, and your "end" (6 o'clock, is smaller than your end 22 o'clock). 

If you want to define your shifts by hours, you would need to have, in general, 2 intervals per shift, and shift 3 should have:

+----+--------------+------------+--------------+------------+
| id | start_hour_1 | end_hour_1 | start_hour_2 | end_hour_2 |
+----+--------------+------------+--------------+------------+
|  1 |     06:00:00 |   14:00:00 |              |            |
+----+--------------+------------+--------------+------------+
|  2 |     14:00:00 |   22:00:00 |              |            |
+----+--------------+------------+--------------+------------+
|  3 |     22:00:00 |   24:00:00 |    00:00:00  |   06:00:00 |
+----+--------------+------------+--------------+------------+


And you should rewrite your query with:

SELECT 
    * 
FROM 
    Shifts S 
WHERE
       (given_hour &gt;= start_hour_1 AND given_hour &lt; end_hour_1)
    OR (given_hour &gt;= start_hour_2 AND given_hour &lt; end_hour_2) ;


As a little advice, I would  use BETWEEN .. AND .. only with discrete variables (basically integers and texts). With continuous variables (and time is continuous), it's safer to include one of the ends and exclude the other (&gt;= AND &lt;):  given_hour &gt;= start_time AND given_hour &lt; end_time. That's why I changed :01 by :00 to work this way (and avoid problems with 14:00:00.123, which could eventually happen).