Timeshift a series in Grafana with an InfluxDB database

Question

I have an InfluxDB 4.5.0 database running on Home assistant 2022.8.7. I want to plot two InfluxDB queries on the same Grafana 7.6.0 graph, but one series is timeshifted by +24hrs.

After several hours of research I see it it possible to timeshift all the series on a Grafana panel using the "Query Options" but I can find no way to timeshift just one of the series.

I note that there is a timeshift function in InfluxDB but am stumped as to how I can modify the query in Grafana to timeshift this by +24hrs

As an example, if the series I want to timeshift is given by the query

SELECT mean("value") FROM "kWh" WHERE ("entity_id" = 'energy_tomorrow') AND time >= now() - 7d and time <= now() GROUP BY time(5m) fill(linear)

is there anyway to modify this query to timeshift the result by +24h, or alternatively what other method is available to achieve this basic result in Grafana with InfluxDB ? Thanks in advance.

Marky0 · Answer 1 · 2022-09-10T11:10:19.100

After many hours of trying I've finally found a solution, and post for any others with the same problem. There are two ways of connecting influxDB to Grafana on home assistant

InfluxQL : which is an SQL like query language and the default connection method
Flux : which is InfluxData’s functional data scripting language

It does not seem possible to timeshift with (1), but there is a function in (2) which allows timeshifting. So the solution is to add a new datasource in grafana, through the UI

Configuration : Data Sources : Add Data Source : InfluxDB

Give this new datasource a recognisable name (eg FluxQuery) and configure this datasource to use Flux instead of the default InfluxQL. Then when adding a new panel in grafana, providing you select the appropriate datasource (eg FluxQuery), then flux querying is enabled and timeshifting possible.

As an example, if the InfluxDB database has the name HomeAssistant which includes two database entity_id's called energy_today and energy_tomorrow. The following flux query plots energy_today timeshfited +24hrs so it overlays correctly on energy_tomorrow

Query A

from(bucket: "HomeAssistant/autogen")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "kWh")
  |> filter(fn: (r) => r["entity_id"] == "energy_tomorrow")

Query B

from(bucket: "HomeAssistant/autogen")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> timeShift(duration: 24h)                    
  |> filter(fn: (r) => r["_measurement"] == "kWh")
  |> filter(fn: (r) => r["entity_id"] == "energy_today")

Timeshift a series in Grafana with an InfluxDB database

1 Answers1