@LAMBERT @aaronsnyder – I spent some time digging into this and wanted to correct (slightly) the query to exclude maintenance windows. I tested your query using a
TIMESERIES clause to ensure that the data was really getting excluded for the right time windows, and it appeared that it didn’t work So I have a couple queries that might help to clarify this use case:
First, to simply exclude maintenance windows (e.g., any window between 00:00 and 04:00 for any day of the week, run a query like this:
SELECT count(*) FROM PageView WHERE hourOf(timestamp) not in ('1:00', '2:00','3:00','4:00') timeseries since 2 days ago
You then get a nice graph that shows those windows excluded:
Now, maybe we want to exclude windows on each day PLUS a window that excludes an entire day. We run a query like this.
SELECT count(*) FROM PageView WHERE hourOf(timestamp) not in ('1:00', '2:00','3:00','4:00') and weekdayOf(timestamp) != 'Saturday' and weekdayOf(timestamp) != 'Sunday' timeseries 1 hour since 7 days ago
Now you get a nice timeseries graph that both excludes those time windows in general AND excludes all of Saturday and Sunday:
So in other words, don’t use OR here. You can just use AND to create those two rules in a single query. However, if you want to exclude a maintenance window on some weekdays and a different maintenance window on other weekdays, you’ll have to use filter() and the chart looks a bit messy. For example, maybe I want to exclude a particular maintenance window on Sunday and another maintenance window on other days of the week. Here’s a query that would do that:
SELECT filter(count(*), WHERE hourOf(timestamp) not in ('1:00', '2:00','3:00','4:00')), filter(count(*), WHERE hourOf(timestamp) not in ('5:00','6:00','7:00') and weekdayOf(timestamp) = 'Sunday') FROM PageView timeseries 1 hour since 7 days ago
Now we get a chart that has two lines, one for the first rule and one for the other (but it looks weird):
Hope this helps! It was a learning experience for me too, even as the Insights PM
Thanks for reaching out.