I came to the exact same conclusion yesterday and did not have time to post. However, I am not sure there is a way to differentiate between the two series. I think this function in general is not super useful.
Thankyou @coffedrinker and @grant2 for this. I’m still struggling with influx and grafana but slowly learning. I’ve been trying to find a way of overlapping energy data to be able to compare solar output and battery state of charge etc, which change strongly over time (months, seasons etc).
I can now plot these as overlays - even if I can’t easily see which series they are from!
I’m using a day on day comparison for battery state-of-charge (SoC %).
If someone knows how to get the series into the legend that will be brilliant!
We do have grafana on premise an using an mysql database. Our solution to display multiple time series from the same data source but with i.e. the day before, we finally used something like:
SELECT
UNIX_TIMESTAMP(DATE_ADD( timeseries.ts, INTERVAL 1 DAY)) as time_sec,
timeseries.meassure as value,
CONCAT( metadata.name, " yesterday") as metric
FROM timeseries
INNER JOIN metadata
ON metadata.id = timeseries.id
WHERE
timeseries.ts >= DATE_SUB( $__timeFrom(), INTERVAL 1 DAY) AND timeseries.ts <= DATE_SUB( $__timeTo(), INTERVAL 1 DAY)
AND metadata.name = “Test”
ORDER BY timeseries.ts ASC
Basically we subtract the time shift from/to an add the same time frame to the ‘time_sec’ for Grafana. Therefore you will see both time series in one graph.
Hoperfully this will help someone!
For me the following solution works well using Grafana v10.1.5 and InfluxDB v2.7.3.
I have added the InfluxDB with Flux as Query Language and then added 2 Queries in Grafana, one for the current year, one for the previous year (see dynamic range) using the timeShift() function:
Query A:
from(bucket: "openhab-rpi01")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "WS_HouseRoof_Temp")
|> filter(fn: (r) => r["_field"] == "value")
|> set(key: "_field", value: "Außentemperatur")
|> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
|> yield(name: "mean")
Query B:
import "date"
from(bucket: "openhab-rpi01")
|> range(start: date.add(d: -1y, to: v.timeRangeStart), stop: date.add(d: -1y, to: v.timeRangeStop))
|> filter(fn: (r) => r["_measurement"] == "WS_HouseRoof_Temp")
|> filter(fn: (r) => r["_field"] == "value")
|> set(key: "_field", value: "Außentemperatur Vorjahr")
|> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
|> timeShift(duration: 1y, columns: ["_start", "_stop", "_time"])
|> yield(name: "mean")
(Optional) Visual Tweaks:
- Set “${__field.name}” as the Display Name to avoid cryptic data series labels.
- Create an override to the previous year’s data series to display as dots
The result looks like this:
3 Likes
Thank you @crc for sharing that solution!