Compare data betweens yesterday and today

Grafana InfluxDB
,
1

Hello everyone,
I have been looking for a while to compare data (external temperature sensor) between yesterday and today.
For example, my value array is:

2023-10-17 02:00:00
5.81
2023-10-17 08:00:00
5.67
2023-10-17 14:00:00
7.87
2023-10-17 20:00:00
11.6
2023-10-18 02:00:00
9.68
2023-10-18 08:00:00
10.0
2023-10-18 14:00:00
11.9
2023-10-18 20:00:00
14.9
2023-10-18 22:26:12
13.3

So I have this simple graph:

image
image953×331 16.8 KB

Now I’m trying to superimpose yesterday’s temperature values on today’s temperature values to make the comparison, so I looked for a solution via the “experimental.alignTime” command but it doesn’t work well:

image
image949×335 26.9 KB

For information, I use InfluxDB2 :

import "experimental"
from(bucket: "JeedomVM_DB")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["Object"] == "Maison")
  |> filter(fn: (r) => r["_field"] == "Extérieur")
  |> filter(fn: (r) => r["CommandName"] == "Température")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> yield(name: "mean")
  |> window(every: 1d)
  |> experimental.alignTime(alignTo: v.timeRangeStart)

If you have an idea, I’m interested because comparing data with each other is still particularly interesting.
Thanks in advance,
Kind regards

2

Welcome @yourry

I agree with you that the experimental.alignTime function in Flux does not really work for this purpose. Having said that, this question (a day-over-day or week-over-week or year-over-year time series graph) comes up a lot and I have yet to see a definitive solution. I am going to try to work out the Flux functions needed. I am 90% sure this is possible. Just need to spend some time on it.

3

Thank you for your reply.
Already, I know it’s not the right solution, I’ve been looking for a while but I was afraid of doing something wrong.
For the initial request, it is certain that the comparison between data is really something that is missing.
I wonder if a solution would not be to offset the data like

   |> range(start: (v.timeRangeStart-1d), stop: (v.timeRangeStop-1d))

But I don’t know the InfluxDB language well.
For your information, in the meantime, I found a little solution which consists of displaying two graphs, it’s not the best either:

compare_value_original
compare_value_original1905×881 49.5 KB

Thank

4

Hi @yourry ,
I think this post might help you. Take a look at:

 

Best regards,
ldrascic

2 Likes
5

Thank you very much for your help on this subject.
Here is what I can do at the moment:

image
image1901×854 68.1 KB

I’m having a little trouble understanding the InfluxDB code but that’s because I’m a beginner in this language.
I was having trouble with the UTC offset and my TimeZone in UTC+2h with the line of code:

timeDifferenceDuration = duration(v: uint(v: now()) - uint(v: lastRow._time)) // UTC timestamps needs to be converted in UNIX/epoch timestamp with uint(). lastRow._time selects _time column from lastRow variable.

I tinkered with something, but it’s not the best when you change the time:

time1 = uint(v: date.add(d: 2h, to: now()))
//time1 = uint(v: system.time())
time2 = uint(v: lastRow._time)
timeDifferenceDuration = duration(v: time1 - time2) // UTC timestamps needs to be converted in UNIX/epoch timestamp with uint(). lastRow._time selects _time column from lastRow variable.

Now, I need to understand better how influxDB2 works, particularly so as not to fixe the dates like this:

 |> range(start: 2023-10-17T21:57:00Z, stop: 2023-10-18T21:57:00Z) // Here use time range in which you find your original (imported) data

In any case, thank you to both of you and then to the Grafana team.
I discovered the tool and I must admit that it is rather well designed and powerful.

Thank you all.