SQL Query between a date range and a time range in MariaDB

I’m having trouble writing this query to give me any results. I’m using MariaDB as well.

SELECT CallDate AS Week_Of, AgentName,
        COUNT(*) AS TOTAL_Calls,
        SUM(case when Accepted = 'ANSWERED' then 1 ELSE 0 END) AS Answered,
        SUM(case when Accepted = 'NO ANSWER' then 1 ELSE 0 end) AS NoAnswer
FROM jshou_custom.afterhours
WHERE CallDate >= DATE_ADD(NOW(), INTERVAL -1 WEEK) 
AND TIME(CallDate) BETWEEN '17:00:00' AND '08:00:00'
GROUP BY AgentName

The DATE_ADD clause works just fine and gives results within that interval, but as soon as I add in the TIME function nothing is returned in the results. The CallDate format is 2021-09-21 HH:MM:SS I have tried using HOUR as well in place of TIME, but it also returns nothing.

I’m trying to pull calls from any day within the range specified in the DATE_ADD clause. As long as it’s between 1700 and 0800 (after hours calls).

Answer

I think you need to check both dates something like this and adjust the times used on each date as well

WHERE 
(CallDate > DATE_ADD(CURDATE(), INTERVAL -7 DAY) AND TIME(CallDate) > '17:00:00')
OR
(CallDate > DATE_ADD(CURDATE(), INTERVAL -6 DAY) AND TIME(CallDate) < '08:00:00')