Calculation with Multiple Queries

InfluxDB 2
, , , ,
1 
time1 = from(bucket: "iobroker")
  |> range(start: today(), stop: now())
  |> filter(fn: (r) => r["_measurement"] == "shelly.0.shellypro3em#c8f09e830d38#1.EMData0.TotalActiveEnergy")
  |> keep(columns: ["_time"])
  |> first(column: "_time")
  |> yield(name: "First")

time2 = from(bucket: "iobroker")
  |> range(start: today(), stop: now())
  |> filter(fn: (r) => r["_measurement"] == "shelly.0.shellypro3em#c8f09e830d38#1.EMData0.TotalActiveEnergy")
  |> keep(columns: ["_time"])
  |> last(column: "_time")
  |> yield(name: "Last")

I get as Output:
First: 2023-06-15T00:00:00.116Z
Last: 2023-06-15T11:29:00.121Z

And thats fine so far, but all i want to do is, getting the difference of both.
Like “11:29:00”

Instead of calculating with the difference, i could simply split that string? To split the date out of it and leave only the time?
Because the first time is always anyway midnight.

The reason for all this is, i have an “Shelly Pro 3 EM”, and that device outputs only ActivePower as an Wh value, which isn’t helpful. So i need to convert it to kW, and that calculation would be: kW = Wh / (1000 × h)
Means i need the “h” from the query, so i will need to convert the time additionally later to a number, means from 19:30:00 to 19,5
But thats anyway the last task, first i need to split somehow the hours and minutes from my “Last” query above.

Thanks for the help!
Sorry for beeing noobish!

2 
import "timezone"
import "date"
option location = timezone.location(name: "Europe/Amsterdam")

from(bucket: "iobroker")
  |> range(start: today(), stop: now())
  |> filter(fn: (r) => r["_measurement"] == "shelly.0.shellypro3em#c8f09e830d38#1.EMData0.TotalActiveEnergy" and r["_field"] == "value")
  |> keep(columns: ["_time","_value"])
  |> increase()
  |> last(column: "_time")
  |> map(fn: (r) => ({ r with relatime: (float(v: date.hour(t: r._time))) + (float(v: date.minute(t: r._time)) / 60.0) }))
  |> yield(name: "value")

Finally digged myself into that c…p and i got it working :slight_smile:
For anyone thats in the same situation, there is the query :slight_smile:

3

@Alexander_Kaplan,

Thanks for sharing your solution with the community!

1 Like