Compare an hour between two hour columns
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).