What Grafana version and what operating system are you using?
V9.4.7, Home Assistant Operating System (running Grafana as add-on)
What are you trying to achieve?
Read a MariaDb database table and group values by time (1d / 1h). My sql worked until the underlying structure of the table changed. The original grouping column (created) was of type datetime(6) and then a new column (created_ts) was added of type double. This original column’s values were all changed to null and the new column became unix time (e.g. 1681124411.7901478). Here is an example of a query that worked before the table change:
SELECT $__timeGroupAlias(created,1d), state AS "Grid" FROM homeassistant.statistics WHERE metadata_id = 168 GROUP BY 1 ORDER BY $__timeGroup(created,1d)
Because the original column now had null values, this obviously only return 1 row.
How are you trying to achieve it?
I tried the original query above, but used the new column, created_ts. Result, again 1 row returned, grouped by null value.
I then tried the following:
SELECT $__timeGroupAlias(FROM_UNIXTIME(created_ts),1d), state AS "Grid" FROM homeassistant.statistics WHERE metadata_id = 168 GROUP BY 1 ORDER BY 1
and received the following error: interpolation failed: macro __timeGroup needs time column and interval.
To check that the data was valid, I ran the following sql:
SELECT FROM_UNIXTIME(created_ts) AS "time", state AS "Grid" FROM homeassistant.statistics WHERE metadata_id = 168 GROUP BY 1 ORDER BY 1
and multiple rows were returned ok.
2022-06-20 16:00:11 0.400
What did you expect to happen?
I expected the $__timeGroupAlias macro to be able to interpret the FROM_UNIXTIME result as a valid time column.
Can you copy/paste the configuration(s) that you are having problems with?
See above sql.
Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.
interpolation failed: macro __timeGroup needs time column and interval.
I would appreciate any assistance as I have tried resolving this issue for a while now.