Latest (max) value of each day

InfluxDB 2
1

I am desperately trying to get this query run correctly.
1st of all, the values are not correctly assigned to the dates.
->The 200Wh on the right is the value of the 22. of November
→ The 300Wh on the right is the value of the 21st of November
→ The 100Wh on the right is the value of the 20. of November
→ The left 100Wh is the value of the 19th of November
→ The left 200Wh is the value of the 18th of November
→ The left 300Wh is the value of the 17th of November
→ The 16th of November doesn’t have a value.
So I dont want to have two Time comlums for the current day, which is I geuss the cause for the values to get shifted by one day.

2nd, As you can see the timestamps all go to 1:00 (instead of the now() timestamp on the current day) which is the wrong time. As the max value of each day is the last value of each day, I would like to set this time to 23:59 instead of 1:00.

3rd, I would like the timestamp on the x-axis to show “DD MMM” only.

Any help is deeply appreciated.

grafana2_LI
grafana2_LI2042×840 122 KB

2

How does your graph appear in Influx Data Explorer?

3

Seems to be the same:

influxdb
influxdb1436×542 107 KB

4

If you change the time in the upper right to UTC, do your x-axis labels look like this?

image
image1214×513 58.4 KB

5

I think you have the wrong timezone.
Starting a flux query with the following lines corrects the time-offets:

import “timezone”
option location = timezone.location(name: “Europe/Vienna”)
from(bucket: “Heating”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)

Second thing is (according to my understanding):
Doing a day aggregation means the results are reported for the next day 0:00.
Today (Nov 22nd 2022) you will get two records

  • 22.11.2022 0:00 with results for yesterday (21.11.22 0:00 - 21.11.22 23:59)
  • 22.11.2022 20:16 results for 22.11.22 0:00 - 22.11.22 20:15)
1 Like
6

This is the result after switching the time to UTC:

Screenshot 2022-11-23 132538
Screenshot 2022-11-23 1325382040×889 126 KB

7

your first suggestion results in:

 error @1:1-1:18: invalid import path timezone

According to your understanding: What would I need to change, in order to get the values of today and the previous week on a daily basis?

8

From what I see in your original screenshot we are doing similar things.
I have a flux query based on the energy counter of my pv inverter. I’m calculating the produced energy by using the difference of the last counter value of each day.

This query gives timestamps with 0:00:00 time.

import “timezone”
option location = timezone.location(name: “Europe/Vienna”)
from(bucket: “Electricity”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“sens”] == “solar”)
|> filter(fn: (r) => r[“channel”] == “3PhaseCounter”)
|> filter(fn: (r) => r[“_measurement”] == “Energy”)
|> aggregateWindow(every: 1d, fn: last)
|> difference()

Regarding the error message you got: sorry here I’m not sufficiently knowledgeable, for me the above query just worked.

9

@TimTom @codac

It seems that import timezone works in InfluxDB Cloud, but not OSS. See here and here.

Which version are each of you using?

10

I’m using InfluxDB OSS 2.4 and had no problems using timezone

11

Yes I’ve read that as well afterwards. I’M using InfluxDB OSS 2.0.9.
I’m running my instances as dockers.
So I’ve set the timezone of InflxuDB, Grafana and the data gathering Docker with parameters to Europe/Berlin.
Even witht he query of @TimTom I have the same issue, no matter if I use UTC or local time.

12

I like your way of calculating the values.
I tried your code and I still have the same problem that the time and values just dont match.

13

I had a similar issue as yours and solved it with the “offset” parameter in the aggregateWindow function.

14

This fixed it for me.

from(bucket: “XXX”)
|> range(start: -1w)
|> filter(fn: (r) => r[“_measurement”] == “XXXXXXXXXX”)
|> filter(fn: (r) => r[“_field”] == “energy”)
|> toFloat()
|> map(fn: (r) => ({r with _value: r._value / 10.0}))
|> aggregateWindow(every: 1d, fn: max, offset: -1h59m, createEmpty: true)

15

FWIW, works properly in OSS 2.6.

1 Like