How to check if CURTIME() is in between 2 different times?
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
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."