I am changing my storage engine from MySQL to influxDB and am struggeling to convert my querys.
I have stored power use for electrical consumers (heating, hot water, car chagers and so on in MySql as watt every minute, in influxDB as amp every minute) and power price. I want to display a bar gauge showing how much I have spent on the different consumers. My SQL looked like this:
SELECT
now() as time,
names.name AS metric,
sum((data.value/60000)*((pp.price * 1.25)+0.0345)) as "value"
FROM sensor_data as data, sensor_names as names, power_prices as pp
WHERE
data.sensor_id = names.sensor_id AND
DATE_FORMAT(data.time, "%y-%m-%d:%H") = DATE_FORMAT(pp.time, "%y-%m-%d:%H") AND
$__timeFilter(data.time)
GROUP BY metric
How can I convert this to flux?
My flux query for showing a graph for all consumers is like this:
Does this query work fine on your end, and does it give you the results you expect? I do not see any errors, so it should work.
from(bucket: "smarthome")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "power")
|> filter(fn: (r) => r["_field"] == "value")
|> map(fn: (r) => ({r with _value: (r._value * .230)}))
|> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
|> yield(name: "all_consumers") // renamed this to make it more descriptive
And for power price, I do not see any reason why this should not work:
from(bucket: "smarthome")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "power")
|> filter(fn: (r) => r["_field"] == "price")
|> map(fn: (r) => ({r with _value: (r._value * 1.25)+0.0345}))
|> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
|> yield(name: "power_price") // renamed this to make it more descriptive
What do the above queries look like in Influx Data Explorer? Are the different device names (car charger, hot water heater, etc.) stored as tags in InfluxDB?
The flux querys I pasted are working. They are used in a time series graph. My question is how can I join this data, sum the kw consumption over time and multipy with power price to get the total price for a given timeframe like I do in the SQL query which I used for the MySQL database.
This is what I have in my old setup:
