进行SQL服务时间查询的最佳方法
我正在寻找一个类似的查询
select * from table where HOUR(NOW()) between OpenHour and CloseHour;
但我要记住,像NightClubs一样,有些地方的开放时间比CloseHour高。

最佳答案

以下假设使用HOUR(NOW())暗示openhour和closehour是整数:

WHERE (    closehour   > openhour
       AND HOUR(NOW()) > openhour
       AND HOUR(NOW()) < closehour)
   OR (    closehour   < openhour
       AND HOUR(NOW()) < openhour
       AND HOUR(NOW()) > closehour)
   OR (    closehour   = openhour)


(最后一个OR子句测试“开放24小时”。)

如果使用的是TIME而不是INT,则必须将子表达式X <rel> Y替换为TIMEDIFF(X, Y) <rel> 0

09-19 12:31