我正在尝试使用MySQL进行时间检查,以查看当前时间是否在两个给定的时间戳之间。
我想要做的是,如果设置的通知范围在两个给定的时间戳之间,则生成一个通知列表。
因此,如果有人说在“18:00:01”和“07:59:59”之间通过TXT消息通知我,那么请在“18:00:01”和“07:59:59”之间通过电子邮件通知我。
以下是我的实现方法(假设当前时间为17:00:00)。
然而,如果我尝试以下操作(假设当前时间为19:00:00):
我想要做的是,如果设置的通知范围在两个给定的时间戳之间,则生成一个通知列表。
因此,如果有人说在“18:00:01”和“07:59:59”之间通过TXT消息通知我,那么请在“18:00:01”和“07:59:59”之间通过电子邮件通知我。
以下是我的实现方法(假设当前时间为17:00:00)。
SELECT CURTIME() BETWEEN '08:00:00' AND '18:00:00' //this retured 1 which is correct
然而,如果我尝试以下操作(假设当前时间为19:00:00):
SELECT CURTIME() BETWEEN '18:00:01' AND '07:59:59' //this return 0
这是我的当前查询
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 ) )
这就是我做错的地方
CASE WHEN n.start <= n.end THEN
CURTIME() BETWEEN n.start AND n.end
ELSE
CURTIME() >= n.start AND CURTIME() <= n.end
END )
有人能帮我构建正确捕获时间的逻辑吗?字段 start
和 end
都是 TIME
类型。
在我的 ticketing_system_notifications
表中,我有以下 2 条记录:
1)start
= '08:00:00',end
= '18:00:00',TEXT
2)start
= '17:59:59',end
= '07:59:59'