Trying to show now and last year on the same graph

Fluxlang
, , ,
1

Hi !

I have all my home automation data in an influx bucket.

In grafana, I can show the last 24h temperatures from the “ext” sensor :

from(bucket: "telegraf/autogen")
          |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
          |> filter(fn: (r) => r["_measurement"] == "°C")
          |> filter(fn: (r) => r["_field"] == "value")
          |> filter(fn: (r) => r["domain"] == "sensor")
          |> filter(fn: (r) => r["entity_id"] == "ext")
          |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
          |> yield(name: "latest")

Capture d’écran 2024-10-09 à 13.53.34
Capture d’écran 2024-10-09 à 13.53.341886×2368 375 KB

On the graph, I can read temperatures from yesterday (2024-10-08) 14:00 to now (2024-10-08) 14:00.

What I want to see on the graph is :

  • temperatures from yesterday (2024-10-08) 14:00 to now (2024-10-09) 14:00.
  • temperatures from 2023-10-08 14:00 to 2023-10-08 14:00.

Can I achieve this ?

1 Like
2

Is it even possible ?

3

Hi @carmelo42

I’m a bit confused…Reread what you wrote when you described the graph…

Did you mean now = 2024-10-09?

The graph you shared shows exactly the two things that you asked for:

  1. The time from 2024-10-08 @ 14:00 to 2024-10-09 @ 14:00
  2. The first dot on the graph shows the temperature at 2024-10-08 @ 14:00. Is that indeed what you want? (your second bullet point above says you want “temperatures from 2023-10-08 14:00 to 2023-10-08 14:00”)

Also (and I think you already know this), but Grafana has a Relative Time option that you can set, where different time ranges for each panel can be achieved by specifying “Relative time” in Query Options like on picture:

image
image1141×579 104 KB

Also, you can specify the “first” value of your series to appear in the legend:

image
image1569×468 64.8 KB

4

Yes :wink:

I want to see 2 lines on my graph :

  • last day
  • and same day but a year before.

If I can explain another way : I want to see on the same graph the temperature “now” and the temperature from last year, to compare value between 2024-10-13 and 2023-10-13 for instance.

5

OK, got it. How about this? (no idea if this will work…just following the description here).

import "experimental"

from(bucket: "telegraf/autogen")
          |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
          |> filter(fn: (r) => r["_measurement"] == "°C")
          |> filter(fn: (r) => r["_field"] == "value")
          |> filter(fn: (r) => r["domain"] == "sensor")
          |> filter(fn: (r) => r["entity_id"] == "ext")
          |> window(every: 1y)
          |> experimental.alignTime(alignTo: v.timeRangeStart)
          |> yield(name: "latest")

Also, the solution on this thread uses SQL which (given InfluxDB v3 is here) might work better.

6

Thanks @grant1 for your answer. Just tried:

image
image3802×2544 504 KB

It seems it’s shifting value but theses are not values from last year.

7

@carmelo42

Nice solution here: