Time(interval) grouping and data scaling

How to make dashboard graph panel query scale time series values accordingly when time groupping interval changes?

Example:
I’m using InfluxDB datasource with Grafana 4.1.2.
Suppose I have time series data with bytes_per_5min values stored every 5min and I’m interested in bitrate.

For relatively short time range(24 hours) 5 min resolution is OK and query might look like:

SELECT sum(“bytes_per_5min”)*8/300 as ‘bitrate’ FROM “SomeMeasurement” WHERE $timeFilter GROUP BY time(5m),“device” fill(null)

For longer time range (90 days) 5 minutes steps are usually not needed and 1day steps might be enough.
So, the query should look like:

SELECT sum(“bytes_per_5min”)*8/86400 FROM “SomeMeasurement” WHERE $timeFilter GROUP BY time(1d),“device” fill(null)

Thus scaling factor in query must change synchronously with time range and interval changes. Preferably with intervals selected from a predefined set. Can it be done in current grafana?

Related question: if autoscaling describe above is not supported yet is there a way to restrict users from selecting arbitrary too wide time range windows for any given dashboard?
So that fixed-time-window daily-,weekly-,monthly-dashboards can be pre-created for users without the risk of blowing up their browsers or overloading influxdb server.

This auto scaling is done automatically by defaut in grafana with the built in $__interval and (auto) ($interval in older versons). This is set variable depending on time range and how wide the graph is so there is never more data points than the graphg has pixels. This is built and available in default queries and all queries that use these built in variables.

You can set a lower limit for this auto interval using expressions like >5m (in group by time interval field below your queries or in data source edit page).

You can also create a template variable of type Interval where you add an auto option that also adapt acroding to time range and where you can specify how many time buckets the time range should be divided in

Thanks for reply.

Unfortunately $__interval variable (and auto setting) doesn’t not help in my kind of queries:

  • $__interval variable cannot be used directly in SELECT math expressions (for example, as a scaling factor ). One can’t rewrite influxdb query like

SELECT sum(“bytes_per_5min”) / 86400 FROM “SomeMeasurement” WHERE $timeFilter GROUP BY time(1d),“device” fill(null)

as

SELECT sum(“bytes_per_5min”) / $__interval FROM “SomeMeasurement” WHERE $timeFilter GROUP BY time(1d),“device” fill(null)

  • $__interval variable takes values out of my control depending on browser window size and resolution while I need group by time steps be multiple of base step (N*5 minutes in my case).
    Also, as far as see in browser console, values look like “5m”,“1h”, …, i.e. not integers. This prevents using them in math calculations.

  • There is no way to restrict possible $__interval values to a set of values defined in a template variable of type Interval or make use interval type template variable as a value for $__interval.

  • related to previous: $timeFilter variable is not seen outside of graph query editor - right? Hence, user-defined interval type template variables know nothing about currenty chosen time window and there is no way to set a proper interval template variable value depending on current $timeFilter.

So far as a temporary solution I can only define many graphs with hardcorded scaling factors and explicit time ranges in WHERE clause instead of $timeFilter. But that’s not elegant.

  • $__interval cannot be used in many places, for example as part of graph title or be assigned to template variable.

  • It would be niceand handy to have more flexible access to other components of time range and interval related variables and in more global context - for use not only in query editor, but in template variables editor/queries, in Text plugin, dashboard and row titles, etc. Smth like $_variableName.fieldName.format - examples: $__timeRange.from.rfc3339 (as string), $__timeRange.from.seconds (as interger seconds from epoch), $__interval.ms (as interger $__interval value in ms). And this would allow me to scale data in my queries as needed.

You can use $__interval_ms for use in math scaling expression

2 Likes

In MySQL I have this table:

CREATE TABLE `log` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `timestamp` timestamp NULL DEFAULT NULL,
  `workerid` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `metric` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `value` decimal(25,10) DEFAULT NULL
  PRIMARY KEY (`id`),
  KEY `timestamp` (`timestamp`,`workerid`,`metric`,`value`),
  KEY `workerid` (`workerid`,`timestamp`,`value`)
)

I had to force the SQL aggregation:

SELECT 
  UNIX_TIMESTAMP(DATE_ADD(the_date, INTERVAL the_hour HOUR)) as time_sec, 
  the_sum as value,
  'hourly' as metric
from (
  select
    DATE(`timestamp`) as the_date,
    HOUR(`timestamp`) as the_hour,
    SUM(value) AS the_sum
  FROM log c
  WHERE workerid = "iotDevice1"
  AND   metric = "count"
  AND   $__timeFilter(`timestamp`)
  GROUP BY 1,2 
  ORDER BY `timestamp` ASC
) x

Can you be more clear about how to set minimum interval? Perhaps a screenshot or something? When I try to put time(>5s) into my query editor the graph goes blank, but time(5s) results in a functioning graph. I have tried quoting, doing time($__interval>5s) and any other thing I could think of but no matter what I can’t get my graph to render such that it uses auto-scaling, but has a minimum.

The reason I need a minimum is because my data only comes in every 5 seconds, so anything less than that and my graph starts to have holes (and I don’t want to use fill(whatever)).

1 Like

This should work:

SELECT mean(“bytes_per_5min”)*8/300 as ‘bitrate’ FROM “SomeMeasurement” WHERE $timeFilter GROUP 
BY time($__interval),“device” fill(null)

since mean = sum / count

For 1d, count = 288 (1440/5) so it has the same effect as changing ‘*8/300’ to *8/86400’.

Hi,

I’m having similar issues with data stored every 5 minutes. Backend is InfluxDB. An oddity of this data gathering is that when nothing is reported for a long time many zeros are not recorded so there can be large gaps in the data in this case. I don’t think Grafana/Influx like that!

I started graphing with fill(0) and with this, when viewing short periods (last 24 hours for example), because $__interval is calculated to be less than 5 minutes I see ‘spikes’ in the graph. This can be fixed by using fill(null) and connecting the nulls.

However, the above is not a good idea when viewing longer periods when $__interval is calculated to be 5 minutes or more. This is because over longer periods when there really was no data available for my metric I see connecting lines from one point to the next (that could have been days or weeks later). This is because of the fill(null) and connecting nulls. In this case though, fill(0) works perfectly.

So to sum up: for graphing short periods fill(null) works and for long periods fill(0) does.

I can solve this by forgetting all about the $__interval value and always using 5m but this is inefficient and makes graphs over long periods look untidy.

I can solve this by creating a subquery with a 5m mean, fill(0) and then using $__interval on that which looks nice by again seems very inefficient (and also has to be done manually).

Sorry if this is a bit of a noob thing, but it does seem odd that one cannot easily achieve this. What have I missed?

Thanks,

Robin

I have the exact problem as @micahzoltu and @robiin - did either of you come up with a suitable solution? I can’t for the life of me figure out how “>5m” applies to anything.

If I could, I’d use the syntax GROUP BY time(max($__interval, 5m) but of course that doesn’t actually work.

Hi there,

Had exactly the same issue. The way I found out to solve is - is to use Max Data Points field in query options which I set to 10 in my example. In that case Interval is automatically calculated for you based on the time range selected:

Hope this helps.
Kostya

Thanks so much for posting a solution! That looks very tidy, I’ll have to try it.