Grafana 8.3.4 on Windows. SQL Server 2012 datasource
I am trying to group a time series by interval variable. The datetime column has yyyyMMddHHmmss
format.
The macro does not recognise DateTime format:
SELECT $__timeGroup( customDateTimeColumn, ${time_interval}, previous) AS time
I tried
SELECT $__timeGroup( CONVERT( DATETIME2, STUFF(STUFF(STUFF(customDateTimeColumn,13,0,':'),11,0,':'),9,0,' ')) , ${time_interval}, previous) AS time
but I get the error:
interpolation failed: error parsing interval STUFF(STUFF(STUFF(customDateTimeColumn
If I try not to use the macro:
SELECT FLOOR(DATEDIFF(second, '1970-01-01', CONVERT( DATETIME2, STUFF(STUFF(STUFF(customDateTimeColumn,13,0,':'),11,0,':'),9,0,' ')) )/${time_interval})*${time_interval) AS time
but the variable $time_interval
shows as 10m, 1h, 6h...
and not in seconds.
If I use $__interval
, it always gets the automatic calculated interval and not the one I choose with the custom interval variable.
I tried modifying the values of the variable in the JSON model but every time I hit Save changes, it does not save them and goes back to stock.
A custom variable (key:value
like 1h: 3600
) won’t have the auto
option.
How could I apply time grouping to this column? (It´s a read-only datasource, so I can not change the column format)