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

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'

+4
source share
1 answer

If the time interval ends at midnight, you should use this logic:

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

Think about it: the current time should be between β€œstart” and midnight, OR between midnight and β€œend”.

+8
source

Source: https://habr.com/ru/post/1483856/


All Articles