Using $__from and $__to to query SQL data

Grafana Dashboards
, ,
1

  • What Grafana version and what operating system are you using?

  • v8.0.1 (8849243d27)

  • What are you trying to achieve?

For my panel on Dashboard I use the following query to load data from my SQL db table:

SELECT sum(value)
FROM my_table
WHERE $__timeFilter(datetime_column)

It loads data for the chosen time range. I would like to load data for the time range larger than the chosen one. More specifically I would like to move the left side of the range, e.g., $__from - 10 min.

  • How are you trying to achieve it?
SELECT sum(value)
FROM my_table
WHERE datetime_column BETWEEN $__from - 10M AND $__to

and also

WHERE datetime_column BETWEEN DATE_ADD($__from, INTERVAL -10 MINUTE) AND $__to

  • What happened?
    Error

  • What did you expect to happen?
    For the chosen time range the data loaded for the range a bit larger than the requested one and calculated SUM (this is only one example).

  • Can you copy/paste the configuration(s) that you are having problems with?

SELECT sum(value)
FROM my_table
WHERE datetime_column BETWEEN $__from - 10M AND $__to
  • Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.

db query error: mssql: Operand type clash: datetimeoffset is incompatible with numeric

2

what data type of this column?

3

What about the following query:

SELECT sum(value)
FROM my_table
WHERE datetime_column BETWEEN DATEADD(minute, -10, CAST($__from AS DATETIMEOFFSET)) AND $__to
4

thanks for the idea. It gives db query error: mssql: Explicit conversion from data type numeric to datetimeoffset is not allowed.

5

datetimeoffset

6

You can use query time shift option:

If you hack timerange diff in the query manually, then some panels may show you error/warning: “data outside of dashboard range”, because returned data time range is outside of selected dashboard timerange.