Looking to see if anyone has a query that produces an avg_over_time AND excludes business hours from the calculation so the average only considers business hours
Hi,
You typed that your query should both exclude and include business hours, so my response will be as if you meant include (exclude would be analogical but I wanted to point out that it might not be the answer you’re looking for off the bat 
).
The query I came up with looks like this:
avg_over_time((rand() and hour(time()) > 9 and hour(time()) < 17)[1h:1m])
where rand() should be replaced with your query. I don’t know how skilled you’re with PromQL, so I’ll explain it from the start 
First we limit the query only to the time within business hours (hence and hour(time())...) part). Using and operator we say to Prometheus “take the query I passed when the hour is > 9 and < 17”. The rest of the points (that are outside of the working hours) are stripped.
Then we calculate avg_over_time on the points that are still standing from the last 1h in 1m intervals.
Notice that the first one hour of the query will only take average of datapoints it has. I’ve noticed that the first point of the avg is always the same as the original query. The result looks like this:
Is it something you’ve looked for?
relatively new, thanks for the response. i tried this
avg_over_time((host_cpu_used_pct{host=~“hostname”} and hour(time()) > 9 and hour(time()) < 17)[1h:1m])
and get
bad_data: invalid parameter “query”: 1:67: parse error: expected type instant vector in call to function “hour”, got scalar
Right, sorry, I’m used to using VictoriaMetrics which allows some… simplifications. I run the following query in Prometheus, so it should be fine (please ignore the metric, as you’ll replace it with your own):
avg_over_time((go_gc_gogc_percent{} and on() hour(vector(time())) > 9 and on() hour(vector(time())) < 17)[1m:1m])
Why the changes?
First of all, the error you’ve seen indicates that time() function returns a scalar value (number) and hour() function expects an instant vector. Instant Vector is one value without any labels in time, so (I’ve seen in somewhere on Stack)
Instant Vector is a pair (t1, v1) (time, value)
whereas a Scalar is a v1 - just value.
Function vector creates a vector out of a scalar.
Second of all and operator matches vectors by labels. In my case the metric go_gc_gogc_percent{} contains some labels (like my machine instance) and expression hour(...) does not contain a single label. Therefore prometheus looks at both parts (right and left side of and), decides there are no vectors that are matching (that have the same set of equal labels), so job’s done. To tell him it’s not that easy, we use on() operator, which means “match the vectors on labels I’ll pass in the parenthesis” - in this case match on anything (since right side doesn’t have any labels).
Also, I might have forgotten writing it earlier - check out the hours. Due to different time zones, you might want to change the numbers (the best way to determine the numbers would be to write just hour(vector(time())) query now and determine which hour you have now according to Prometheus).
Hope this helps and sorry for the confusion on the first one!
this is very helpful. i have this much working to give me only where the hour is < 7, which gives me 00:00 thru 07:00 UTC. Now i want to show from 19:00 on one day thru 07:00 on the next day (overnight). i suspect an or would do this but cant seem to work that out
avg_over_time((host_cpu_used_pct{host=“<hostname”} and on() hour(vector(time())) <= 7)[1m:1m])
Ah, so I did get it wrong after all To get the values from 19:00 to 7:00, you can change the and between hour(...) queries to or. To be safe, I’ve also added additional parenthesis (I’m not sure how prometheus resolves boolean conditions), so query could look like this:
avg_over_time((vector(1) and on() (hour(vector(time())) < 9 or on() hour(vector(time())) > 17))[1m:1m])
Again, check your hours (timezones are still my bane ), and replace vector(1) with your query. (I also believe you have a typo in {host=“<hostname”}, I think it should be {host=~“hostname”} but probably just something in typing it here ).
If you need additional support, I think the best would be tag me in your response (I’m still new here and I didn’t get notification about your response )