I am trying to do a time check using MySQL to find out if the current time is falling between two given timestamps or not.
I am trying to create a notification list if the range of the given notifications is between two given timestamps.
So, if someone told me to notify me through a TXT message between '18: 00: 01 'AND '07: 59: 59' THEN tell me through EMAIL BETWEEN '18: 00: 01 'AND '07: 59: 59 '.
Here is what I did.
So, I tried the following (if the current time is 17:00:00)
SELECT CURTIME() BETWEEN '08:00:00' AND '18:00:00' //this retured 1 which is correct
However, if I tried the following (if the current time is 19:00:00)
SELECT CURTIME() BETWEEN '18:00:01' AND '07:59:59' //this return 0
This is my current request.
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 ) )
That's where i'm 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 build the logic to fix the time correctly? Both start and end fields are of type TIME
In my ticketing_system_notifications table, I have the following 2 entries
1) start = '08: 00: 00 ' end = '18: 00: 00' TEXT
2) start = '17: 59: 59 ' end = '07: 59: 59'
Mike source share