Calculation with Multiple Queries

``````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!

``````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
For anyone thats in the same situation, there is the query

Thanks for sharing your solution with the community!

1 Like