Let's say i have a prometheus time series showing the power consumption from my power panel. I want to get the average consumption from 02:00 to 08:00 and from 15:00 to 17:00 on the same day and show them on the same panel in grafana. Assume that i have a time series of the above data starting from 00:00 and ending at 23:59 on the same day with a data point each second. I have tried playing with relative times, intervals and offsets but it is so frustrating i have been confused. Victoria metrics is also supported as i am using it to store the data series. I am thinking on changing to influxdb because it seems clearer to me how to do that with the language it supports but i would like to stay to just prometheus and victoriametrics.
i tried getting the first time range from 02:00 to 08:00 by using offsets and time intervals grafana offers but i failed. Even if it worked, i then would not have any clue how to do that with the other time range from 15:00-17:00.
To get average value of metric my_metric over window of time between 02:00 and 08:00 of current date, with preserving result till the midnight, you can use following query:
avg_over_time(
(my_metric
and on() (
(hour() >= 2<8)
and
floor(vector(time())/86400) == on() floor(topk(1,timestamp(up@end()))/86400)
)
)
[24h:])
What's happening here:
avg_over_time calculates average over specified range vector. In this case we need simple average, as all the filtration will happen inside range selector.my_metric and on() taking metric and filtering it base on following expressions ignoring all the labels.
hour() >= 2<8) first filtering expression: function hour() should return value between greater or equal than 2 and less than 8,and second expression must be true an the same time as first one. No need for on() since both of expressions don't have any labels.floor(vector(time())/86400) == on() floor(topk(1,timestamp(up@end()))/86400) second filtering expression, to take only those values of metric that are within day that correspond to end of query time range. A bit more explanation:
floor(vector(time())/86400) simply calculates whole division part of timestamp by 86400. This is to find 00:00:00 of day to which value belongs.floor(topk(1,timestamp(up@end()))/86400) does similar to previous one, but it calculates 00:00:00 of the day, to which belong the end of time range selected (end of range selected for dashboard in Grafana in your case). Here up@end() simply returns value of metric up on the range end, then timestamp extracts it's timestamp. topk(1, .. ) is used to get only one value of results (metric up has multiples, use of up is arbitrary and whole construct can be replaced with some metric that is guarantied to have a singe value at any time).Total query according to what you described in question will look like this (even though I still believe that there is no sence what so ever in addition of two averages):
avg_over_time(
(my_metric
and on() (
(hour() >= 2<8)
and
floor(vector(time())/86400) == on() floor(topk(1,timestamp(up@end()))/86400)
)
)
[24h:])
+
avg_over_time(
(my_metric
and on() (
(hour() >= 15<17)
and
floor(vector(time())/86400) == on() floor(topk(1,timestamp(up@end()))/86400)
)
)
[24h:])