NRQL to exclude hours of the day not working

[GHale]

Example NRQL

SELECT result, duration as MS, duration/1000 as Seconds, duration/1000/60 as Minutes, monthof(timestamp) as Month, weekdayof(timestamp) as DOW, monitorName
FROM SyntheticCheck
WHERE monitorName like ‘xxxxxxxxxxxx%’
and result = ‘FAILED’
and weekdayof(timestamp) not in (‘Saturday’,‘Sunday’)
and hourof(timestamp) not in (‘20:00’,‘21:00’,‘22:00’,‘23:00’,‘0:00’,‘1:00’,‘2:00’,‘3:00’,‘4:00’,‘5:00’,‘6:00’,‘7:00’)
since ‘2017-01-01 00:00:00’
limit 1000
order by timestamp

Seems simple, should work in my mind! I assume I need to use filter? Have not been able to apply filter and get this query working. Suggestions please. The first view rows return 03 hour. Is this a UTC to EST issue?

[stefan_garnham]

Hi @GHale - I think you are correct in that you’re correct in that you may want to add the timezone to the query. You should be able to use SINCE ‘2017-01-01 00:00:00 EST’.

Alternatively, start by removing the hourOf filter to ensure you have data that covers the times you want, then start adding them in. Another option would be to change the hourOf filter to use IN(…) instead of NOT IN(…)

[GHale]

Adding EST made no difference. Are you suggesting that “IN” works differently than “NOT IN”? Dates and times are very frustrating in NRQL! This should be an easy query, nothing special in my mind!

[AndyC]

Hey @GHale, I made a few tweaks to your query and I am seeing data that matches the intended exclusion pattern you’re going for. Can you give this query a try and let us know how it works for you?

SELECT result, duration as 'MS', duration/1000 as 'Seconds', duration/1000/60 as 'Minutes', hourof(timestamp) as 'Hour', monthof(timestamp) as 'Month', weekdayof(timestamp) as 'DOW', monitorName 
FROM SyntheticCheck 
WHERE result = 'FAILED' 
and weekdayof(timestamp) not in ('Saturday','Sunday')
and hourof(timestamp) not in ('20:00','21:00','22:00','23:00','0:00','1:00','2:00','3:00','4:00','5:00','6:00','7:00')
since '2017-01-01 00:00:00' 
limit 1000