First part of graph always missing

Grafana
1

Hi everyone,

I’m sure this is an easy one for you guys but I’m tearing my hair out trying to figure out why ALL my graphs are missing the first section (see screenshot), regardless of which time window I select, though it does get worse the more granular I go (see below). Once I get to last 1 hour I’ve lost a lot of info and anything under there it does not display any info at all.

These are from InfluxDB so I’m guessing something is wrong with my query somewhere? The data is definitely present in the DB (i.e. it’s not missing from the graph because it doesn’t exist in InfluxDB).

If anyone can point me in the right direction it would be appreciated!

Thanks,

Neil

grafFINAL
grafFINAL1114×1800 250 KB

grafquery
grafquery1497×295 27.6 KB

2

Remove the last() selector from your query. You do want a moving average over 10 data points, right?

3

Hi pmhausen, thanks for the response. If I remove last() then the graph goes completely blank.

I do note that if I remove the moving average then the graph is actually correct, but I’ve got the moving average there to smooth the graph off so then it becomes jaggedy.

With last() removed as suggested:

g3
g3632×809 45.3 KB

With moving_average() removed but now it’s lost line smoothing:

g1
g1661×704 35.4 KB

4

Could you remove all the GROUP BY fields?
This works for me here, you can set the handling of NULL values in the visualization section and GROUP BY time(…) is not necessary.

In addition to removing last() :wink:

5

Hi pmhausen,

Thanks again for the reply :slight_smile:

I’ve removed all GROUP BY and last() but it’s still not displaying the data.

If I change moving_average(10) to moving_average(5) then it displays a tiny bit more data, but still cuts it off.

The data is definitely there because if I change moving_average(10) to last(), it displays the data correctly.

I’m sure I’m doing something silly but I can’t figure it out!

g4
g4960×700 40.7 KB

6

How frequently do measurements get inserted into the database? Grafana constructs the InfluxDB query with a hard $timeFilter expression that matches the time interval you pick in the upper right corner of your dashboard. Now do a quick calculation in your head how many data points you will have in that interval and I bet there are less than 10 measurements from 6:40 to 7:35, so how should the system compute a moving average of 10 values?

So you hit a fundamental limitation rather than doing anyting wrong.

7

They’re fed into the DB every 5 mins (see below). That means that between 6:40 and 7:35 we should have 12 points (6:40 and 7:35 inclusive)?

As I mentioned, if I change moving_average(10) to moving_average(5), it still cuts off a bit of the first part of the graph (albeit less) but with readings every 5 mins I’d expect that to have sufficient data points?

Thanks again.

$ influx -database environmentals -execute "select * from temperature where host='humidpi1'" | tail
1593076200000000000 humidpi1 26.9
1593076500000000000 humidpi1 26.9
1593076800000000000 humidpi1 26.9
1593077100000000000 humidpi1 27
1593077400000000000 humidpi1 27
1593077700000000000 humidpi1 27
1593078000000000000 humidpi1 27
1593078300000000000 humidpi1 27
1593078600000000000 humidpi1 27
1593078900000000000 humidpi1 27

# Convert the unix timestamp
$ for x in $(influx -database environmentals -execute "select * from temperature where host='humidpi1'" | tail | awk '{print $1}') ; do date -d "@${x:0:10}" ; done
Thu Jun 25 10:10:00 BST 2020
Thu Jun 25 10:15:00 BST 2020
Thu Jun 25 10:20:00 BST 2020
Thu Jun 25 10:25:00 BST 2020
Thu Jun 25 10:30:00 BST 2020
Thu Jun 25 10:35:00 BST 2020
Thu Jun 25 10:40:00 BST 2020
Thu Jun 25 10:45:00 BST 2020
Thu Jun 25 10:50:00 BST 2020
Thu Jun 25 10:55:00 BST 2020
8

The first 4 (for a moving average of 5) or the first 9 (for 10) will always be missing.

9

That’ll be why then - thank you so much for your assistance :slight_smile: