$__timeGroupAlias error when using FROM_UNIXTIME

Grafana Dashboards
1

  • 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.
FROM_UNIXTIME(created_ts) Grid
2022-06-20 16:00:11 0.400

  • What happened?

  • 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.