Not possible to use the "auto" interval

[andnofence]

I couldn't figure out how to achieve this, hope I haven't missed something obvious 😅

What happened:
I want to use the "auto" interval option in Grafana so that the interval adjusts itself, but I also want the user to be able to to override it by selecting an interval.

What you expected to happen:
The auto interval should be be compatible with the Clickhouse interval format.

How to reproduce it (as minimally and precisely as possible):
Configure an "interval" variable and pass it to any query, e.g.

select toStartOfInterval(time, interval ${interval}), count(*) from metric group by 1

Since the interval format in Grafana isn't compatible with Clickhouse this fails (e.g. Grafana passes in 1m):

default :) select toStartOfInterval(time, interval 1m), count(*) from metric group by 1

Syntax error: failed at position 43 (')'):

It was fairly simple to work around this using a Clickhouse function:

create or replace function _grafana_interval_to_clickhouse_interval as (grafana_interval) ->
  -- grafana uses the following format for intervals: 1s, 1m, 1h, 1d
  -- clickhouse uses 1 second, 1 minute, 1 hour, 1 day
  concat(
    substring(grafana_interval, 1, length(grafana_interval) - 1),
    ' ',
    multiIf(
      right(grafana_interval, 1) = 's', 'second',
      right(grafana_interval, 1) = 'm', 'minute',
      right(grafana_interval, 1) = 'h', 'hour',
      right(grafana_interval, 1) = 'd', 'day',
      'unknown interval'));

But it would be great if the plugin had a macro for this so you could write something like:

select toStartOfInterval(time, interval ${__fromGrafanaInterval(interval)}), count(*) from metric group by 1

Environment:

• Grafana version: 9.4.2
• Plugin version: 3.3.0

[SpencerTorres]

If it wasn't for the requirement of overriding the interval, I would suggest using the time interval macros:

Macro	Description	Output example
$__timeInterval(columnName)	Replaced by a function calculating the interval based on window size in seconds, useful when grouping	toStartOfInterval(toDateTime(column), INTERVAL 20 second)
$__timeInterval_ms(columnName)	Replaced by a function calculating the interval based on window size in milliseconds, useful when grouping	toStartOfInterval(toDateTime64(column, 3), INTERVAL 20 millisecond)

I think it would be useful to have the $__fromGrafanaInterval macro for this use case though.

[aangelisc]

Does the $__timeInterval macro meet these requirements?

[SpencerTorres]

Does the $__timeInterval macro meet these requirements?

$__timeInterval uses the "AUTO" interval from the panel. In this case they want to use their own interval variable from the dashboard, but Grafana uses its own syntax:

Grafana	ClickHouse
1m	1 minute

The suggested macro would allow queries to use Grafana intervals by converting it to the ClickHouse syntax.

[krokwen]

use INTERVAL $__interval_s second but it works correctly on small time intervals (btw covers most cases)
there are only issues when you try to aggregate to start of week/month/year, then you need to use toMonday, toStartOfMonth, toStartOfYear instead.

i added materialized views with time-aggregated data by different time slices. to change the table suffix according my time interval i added extra query vars that calculate best time-aggregated table and minutes per datapoint for different sizes of visualizations.
i use 'toMonday' conversion in the select query of the materialized view

maybe it could be done with better way (recently i noticed 'interval' variable type in dashboard, but not yet played with it)
my solution:
timeSlicedSuffix1of4 query var:

with toUnixTimestamp($__toTime) as tsTo,
    toUnixTimestamp($__fromTime) as tsFrom,
    -- datapoints1of4 is a constant equal to 20 for 1/4 screen-size-visualization. 20 datapoints is comfortable value to display on 13-14" laptops... --
    ${datapoints1of4} as maxDP,
    toUInt32((tsTo - tsFrom) / maxDP) as tsPerDP,
    map(
            '1m', 60,
            '3m', 180,
            '9m', 540,
            '30m', 1800,
            '1h', 3600,
            '6h', 21600,
            '1d', 86400,
            '3d', 259200,
            '1w', 604800
    ) as sliceMap,
    mapKeys(
            mapReverseSort(
                    (k, v) -> v,
                    mapFilter(
                            (k, v) -> (v <= tsPerDP),
                            sliceMap
                    )
            )
    )[1] as preselSlice
select if(preselSlice != '', preselSlice, mapKeys(sliceMap)[1]) as value;

minutesInSlice1of4 query var

with toUnixTimestamp($__toTime) as tsTo,
    toUnixTimestamp($__fromTime) as tsFrom,
    ${datapoints1of4} as maxDP,
    toUInt32((tsTo - tsFrom) / maxDP) as tsPerDP,
    map(
            '1', 60,
            '3', 180,
            '9', 540,
            '30', 1800,
            '60', 3600,
            '360', 21600,
            '1440', 86400,
            '4320', 259200,
            '10080', 604800
    ) as sliceMap,
    mapKeys(
            mapReverseSort(
                    (k, v) -> v,
                    mapFilter(
                            (k, v) -> (v <= tsPerDP),
                            sliceMap
                    )
            )
    )[1] as preselSlice
select if(preselSlice != '', preselSlice, mapKeys(sliceMap)[1]) as value;

visualization query example

SELECT
  Timestamp,
  HttpHost,
  -- i need to show approx value per minute, but time slice could be bigger than 1 minute, so i divide it by dashboard calculated value --
  sumArray(statusRpm) / ${minutesInSlice2of4} as rpm
FROM (
  SELECT Timestamp,
    HttpHost,
    sumMapMerge(HttpStatusSumState).2 as statusRpm
  -- one of custom materialized view for http stat --
  -- set table suffix to choose the right table --
  FROM otel_http_log_stat_${timeSlicedSuffix2of4} as t
  WHERE $__timeFilter(Timestamp)
    AND $__conditionalAll(HostName in (${hostname:singlequote}), ${hostname})
    AND $__conditionalAll(HttpHost in (${logsHttpHost:singlequote}), ${logsHttpHost})
  GROUP BY HttpHost, Timestamp
)
GROUP BY HttpHost, Timestamp
ORDER BY Timestamp ASC

btw, nested select is ok due to very small amount of data

[SpencerTorres]

I think the function is a simpler workaround for now, it would be easy to add the suggested macro.

That is a good note on the timeframes though, since it can be hard to find the start of calendar weeks/months