Hello all,
I have a problem using the union function and option location to get the correct data.
My code I am using in Data Explorer is looking like this
import “date”
import “timezone”
option location = timezone.location(name: “Europe/Berlin”)
timeRangeStart = date.truncate(t: v.timeRangeStart, unit: 1d)
First_usage = from(bucket: “homeassistant”)
|> range(start: timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“domain”] == “sensor”)
|> filter(fn: (r) => r[“entity_id”] == “gesamtverbrauch”)
|> filter(fn: (r) => r[“_measurement”] == “kWh”)
|> filter(fn: (r) => r[“_field”] == “value”)
|> aggregateWindow(every: 1d, timeSrc: “_start”, fn: first, createEmpty: false)
|> set(key: “newValue”, value: “usage_first_value_of_the_day”)
|> yield(name: “first”)
Last_usage = from(bucket: “homeassistant”)
|> range(start: timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“domain”] == “sensor”)
|> filter(fn: (r) => r[“entity_id”] == “gesamtverbrauch”)
|> filter(fn: (r) => r[“_measurement”] == “kWh”)
|> filter(fn: (r) => r[“_field”] == “value”)
|> aggregateWindow(every: 1d, timeSrc: “_start”, fn: last, createEmpty: false)
|> set(key: “newValue”, value: “usage_last_value_of_the_day”)
|> yield(name: “last”)
union(tables: [First_usage, Last_usage])
|> pivot(rowKey:[“_time”], columnKey: [“newValue”], valueColumn: “_value”)
|> map(fn: (r) => ({ r with daily_difference_usage: r.usage_last_value_of_the_day - r.usage_first_value_of_the_day }))
My goal is to get the last value of the day and the first value and draw the difference in a bar chart. This code is working fine without “option location = timezone.location(name: “Europe/Berlin”)”. Unfortunately I recognized today that currently two hours of data is missing without the timezone option. So I tried to adjust.
If I take a look a raw data in data explorer that only 50% of the queries result in a situation where daily_difference_usage is caculated correctly (result contains three tables, otherwise result contains four tables and difference is not calculated). Removing the timezone option fixes the issue. Did I find a bug or is there something I am doing wrong?
Br
Michael
