在 MySQL 中根据时间从表中获取数据

Question

此查询出现问题。

晚上 10 点至上午 08 点（从 2200（晚上）到 0800（早上））

此查询不起作用。我知道日期在 24：00 小时后会更改，我认为这就是查询不起作用的原因。

SELECT date(callDate) AS dates,
       sum(calltype = 'MISSED') AS missedCall,
       sum(callType = 'INCOMING') AS incomingCall,
       sum(callType = 'OUTGOING') AS outgoingCall
FROM tbl_call_details
WHERE userId = '1' AND
      date(callDate) BETWEEN '2014-04-01' AND '2014-04-31' AND
      time(callDate) > '22:00' AND time(callDate) < '08:00'
GROUP BY date(callDate)
ORDER BY date(callDate) ASC

也尝试了以下方法，但这对我也不起作用。

SELECT  * FROM tbl_call_details
WHERE userId = '1' AND
      date(callDate) BETWEEN '2014-04-01' AND '2014-04-31' AND
      time(callDate) > '22:00' AND
      time(callDate) < '08:00'

我想使用选择查询获得 2200-0800 之间的结果。如果有人有任何想法，请指导我。

这工作得很好，因为我在同一天添加了 0800-2200。

SELECT date(callDate) AS dates,
       sum(calltype = 'MISSED') AS missedCall,
       sum(callType = 'INCOMING') AS incomingCall,
       sum(callType = 'OUTGOING') AS outgoingCall
FROM tbl_call_details
WHERE userId = '1' AND
      date(callDate) BETWEEN '2014-04-01' AND '2014-04-31' AND
      time(callDate) > '08:00' AND time(callDate) < '22:00'
GROUP BY date(callDate)
ORDER BY date(callDate) ASC

输出：-

dates     missedCall    incomingCall    outgoingCall
2014-04-01  0           5           22

Answer 1

> 您应该使用OR条件，您的查询会尝试选择时间在上午 8 点之前和晚上 22 点之后的数据，这是不可能的。请注意，您需要添加括号，如果不添加它们，结果会有所不同（尝试您的

）：

SELECT  date(callDate) AS dates, sum(calltype = 'MISSED') AS missedCall, sum(callType = 'INCOMING') AS incomingCall, sum(callType = 'OUTGOING') AS outgoingCall 
FROM tbl_call_details 
WHERE userId = '1' AND date(callDate) BETWEEN '2014-04-01' AND '2014-04-31' 
AND (time(callDate) > '22:00' OR time(callDate) < '08:00') 
GROUP BY date(callDate) ORDER BY date(callDate) ASC