How to check if CURTIME() is in between 2 different times?

Question

I am trying to do a time check using MySQL to see if the current time falls between 2 given timestamps or not.

What I am trying to do is to generate a notification list if the set notification range is in between the 2 given timestamps.

So if someone said notify me via TXT message between '18:00:01' AND '07:59:59' THEN notify me via EMAIL BETWEEN '18:00:01' AND '07:59:59'.

Here is what I have done.

So I have tried the following (assuming current time is 17:00:00)

SELECT CURTIME() BETWEEN '08:00:00' AND '18:00:00' //this retured 1 which is correct

However if when I tried the following (assuming current time is 19:00:00)

SELECT CURTIME() BETWEEN '18:00:01' AND '07:59:59'  //this return 0

This is my current query

 SELECT DISTINCT
TRIM(su.first_name) AS name,
CASE WHEN n.notification_method = "SMS" THEN 
CASE WHEN cc.gateway IS NOT NULL THEN CONCAT(su.phone_cell, cc.gateway) ELSE "" END
ELSE su.email1 END AS address
FROM  ticketing_system_notifications AS n
INNER JOIN(
SELECT DISTINCT created_by, issue_id FROM ticketing_system_discussions
WHERE status = 1
) AS ds ON n.notify = ds.created_by
INNER JOIN users AS su ON su.user_id = n.notify
LEFT JOIN cell_phone_carriers AS cc ON cc.carrier_id = su.carrier_id
WHERE ds.issue_id = 31 AND n.notify <> 12 AND n.notification_type = "REPLY"
AND n.category_id = 0 AND n.status = 1 AND (n.expired_on IS NULL OR n.expired_on > NOW() ) 

AND (  (n.start IS NULL OR n.end IS NULL) OR (  

CASE WHEN n.start <= n.end THEN
CURTIME() BETWEEN n.start AND n.end
ELSE
CURTIME() >= n.start AND CURTIME() <= n.end
END   )  ) 

This is where I am going wrong

CASE WHEN n.start <= n.end THEN
CURTIME() BETWEEN n.start AND n.end
ELSE
CURTIME() >= n.start AND CURTIME() <= n.end
END   )

Can someone please help me building the logic to capture the time correctly? Both the fields start and end are of type TIME

In my ticketing_system_notifications table I have the following 2 records
1) start = '08:00:00' end = '18:00:00' TEXT
2) start = '17:59:59' end = '07:59:59'

Answer 1

If the time range wraps over midnight you should use this logic instead:

 curtime() >= n.start OR curtime() <= n.end

Think about it: the current time has to be between the "start" and midnight, OR between the midnight and the "end."