Shift timing calculation issue

I am trying get shift based on current time. i have a shift master which have start time to end time. The shift timing are like follo开发者_JS百科ws.

A - 06:00  --  14:00
B - 14:00  --  22:00
C - 22:00  --  06:00

i Will get first two shift based on current time. But i am not getting the third shift. e.g. if Current time is 01.30 then i should get "C" as out

You need to handle shifts that wrap to the next day, e.g.

where
curtime() >= starttime and
curtime() <  if(endtime>starttime,endtime,adddate(endtime,interval 1 day))

You can add another record for "C" like:

C - 22:00  --  24:00
C - 00:00  --  06:00

Also can create a somewhat complicated SQL:

SELECT shift
FROM table
WHERE end > '1:30'
    AND (
        start <= '1:30'
        OR
        start > end
    )