Grafana 8.3.3
I have a time series with calculated Energy cost per hour calculated by power consumption and hourly energy price (spot price) + addons.
I would like to get the sum of cost per day but I’m having a brain f*rt and cannot for the life of me figure it out.
SELECT ((mean(“ePrice”)+0.22+4.09+2.79372)*1.1)/100 * mean(“totPower”)/1000 FROM “modbus” WHERE “ePrice”>0 AND $timeFilter GROUP BY time($__interval) fill(null)
I have the time interval in the query set to 1hour, which I need to have to get the hourly cost, as the price and consumption changes per hour, but how do I go about showing the daily sum? If I set the query interval to 1day I get strange values.
This is what I have per hour, and this is correct. But how do I sum it per day?
I think I might have solved it using nested queries like this:
SELECT sum(cost) AS perday FROM (
SELECT ((mean(“ePrice”)+0.22+4.09+2.79372)*1.1)/100 * mean(“totPower”)/1000 AS cost FROM “modbus” WHERE “ePrice”>0 AND $timeFilter GROUP BY time(1h) fill(null)
) WHERE $timeFilter GROUP BY time(1d)
Now my only issue is that my days change at 02:00 not 00:00, which is probably because it changes day at UTC time, which would be 02.00 local. So the data is correct it just looks weird. Would be nice if i could tell it to show local days. I tried using tz(‘Europe/Helsinki’) but i just get the response “InfluxDB Error: error parsing query: unable to find time zone Europe/Helsinki”
Managed to fix the time zones parsing error by setting a time zone environment variable and uploading a zip on the server running influxdb using this suggestion Error while defining location parameter for aggregateWindow - #7 by mutigozel - Fluxlang - InfluxData Community Forums, but changing the tz() still does nothing about the grouping split point. Maybe it’s because I’m still on InfluxDB 1.7
