I have a need to prepare weekly stats on some of our metrics. Like for example weekly average memory usage or maximum weekly CPU %, or similar.
Further, I want this sliced on weeks starting on Monday and ending on Sunday.
I do an aggregation like this:
max(max_over_time(my_metric[1w]))
avg(avg_over_time(my_metric[1w]))
I then set the range in Grafana to show me last two weeks:
From: now-2w/w
To: now-1w/w
It properly shows the date range. At this specific timestamp of Friday, 9-Oct, it displays a range from 2020-09-20T22:00:00Z to 2020-10-04T21:59:00Z
Then I punch the query into Grafana.
If I click the Prometheus link I get proper query for Prometheus: ?g0.range_input=1209600s&g0.end_input=2020-10-04%2021%3A59&g0.step_input=604800
Range: 2 weeks x 7 days / week x 24 h / day x 60 min / h x 60 s / min = 1209600s. All good!
Step: 1 week x 7 days / week x 24 h / day x 60 min / h x 60 s / min = 604800. All good!
It’s now in UTC, and looks good. UI confirms it as well. See below.
Data fetched also looks good, with three data points, on the the beginning, one at the end and one in the middle (it’s two week range):
But if I inspect Grafana query, it is wrong: start=1600293600&end=1601503200&step=604800 1600293600 translates into 2020-09-16T22:00:00Z, so I got a shift back for 4 days.
The calendar range is right, the query was wrong and correspondingly wrong results were fetched and plotted:
Query: sum(sum_over_time(memgrid_tickets_gauge[1w]))
GET to Prometheus: "api/datasources/proxy/2/api/v1/query_range?query=sum(sum_over_time(memgrid_tickets_gauge%5B1w%5D))%20&start=1600293600&end=1601503200&step=604800"
Link from Grafana to Prometheus Graph: graph?g0.expr=sum(sum_over_time(memgrid_tickets_gauge%5B1w%5D))%20&g0.range_input=1209600s&g0.end_input=2020-10-04%2021%3A59&g0.step_input=604800&g0.tab=0
Welcome to the Grafana forum. Although I cannot offer a concrete solution, this subject does come up regularly. I presume you searched through the various threads and proposed solutions. Depending on your datasource (InfluxDB, MSSQL, etc.), you would have to modify your query. This post shows a MSSQL solution. What is your datasource?
This is a complete guess (since I know virtually nothing about Prometheus), but does putting in 3d (or 72h) or 4d (96h) into the Time shift field do anything?
the Time shift just makes it so that the data is 3 days or 4 days in the past but it doesnt change when the week starts. when i go into prometheus and type in the data i want then its correct. but in grafana the query it sends begins thursday.
maybe i should mention that i run prometheus and grafana in seperate docker containers. i also made sure all containers and the host system have the same time and timezone.
also whats interesting is when you set the min interval to 6 days because then its almost correct. itgets data from the 16.01.22 to 22.01.22 so 6 days but 7 days or 1w it start from the 20.01.22 and up to the 27.01.22.
i think i figured out why the data is like this. the issue is enterely in prometheus.
1 week in unix time is 604800. the date prometheus starts at (for example the 20.01.22) is in unix time 1642636800. this date is perfectly divisible with 604800.
my conclusion is that it starts on a thursday is because the unix time starts on a thursday…
