Query consumption/time based on another field (energy consumption per day when sun is down)

[larsxschneider]

• What Grafana version and what operating system are you using?

Grafana 9.4.9 OSS with InfluxDB 1.8 on Ubuntu 22.04. I can write queries against InfluxDB with with InfluxQL and Flux.

• What are you trying to achieve?

I regularly push the value of my power meter to Influx (a monotonic increasing number). With the following query I successfully chart the consumption per day:

SELECT difference(max("value")) FROM "kWh" WHERE ("entity_id" = 'energy_total_kwh') AND $timeFilter GROUP BY time(1d) fill(none)

I also regularly push the state of the sun to Influx (1 means sun is above the horizon, 0 means it is below the horizon).

Now, I would like to query my power consumption per day when the sun is below the horizon. Is that possible with InfluxQL or Flux? If yes, can you give me a hint how?

[grant2]

@larsxschneider

I also regularly push the state of the sun to Influx (1 means sun is above the horizon, 0 means it is below the horizon).

Are you storing the state of the sun (1 or 0) as a field or tag? Could you push the state of the sun at the same timestamp that you push the value of your power meter? That would make it very straightforward to query.

[larsxschneider]

Thank you @grant2! I believe the state of the sun in stored as field (how can I check?). I also believe it is pushed at the same time. I don’t know for sure because the push happens automatically via Home Assistant (https://www.home-assistant.io).

I’ve experimented with Grafana Math expressions (e.g. $A * (-$B+1); A being the consumption and B the sun) but I always get “No data”.

[mariusbach]

sorry, misposted. disregard.

[grant2]

Hi @larsxschneider

@larsxschneider

I believe the state of the sun in stored as field (how can I check?

Can you inspect your data in Influx and/or post here as a .csv? I threw together some sample data and mine looks like this. You will see I am recording the SunState and the energy consumed value at the same time.

_time	                SunState	energy_consumed
2023-05-07T11:11:35.167Z	1	662
2023-05-07T11:12:27.703Z	1	664
2023-05-07T11:13:08.953Z	1	665
2023-05-07T11:14:11.474Z	1	666
2023-05-07T11:14:55.25Z	    0	667
2023-05-07T11:16:23.786Z    0	668
2023-05-07T11:17:57.03Z	    0	669
2023-05-07T11:19:02.621Z	1	670
2023-05-07T11:19:50.631Z	1	671
2023-05-07T11:20:21.991Z	1	672
2023-05-07T11:21:06.324Z	1	673
2023-05-07T11:23:15.726Z	1	674
2023-05-07T11:24:03.697Z	0	675
2023-05-07T11:25:02.512Z	0	676
2023-05-07T11:25:36.968Z	0	677
2023-05-07T11:26:41.276Z	0	678
2023-05-07T11:28:11.162Z	1	679

Flux query to see everything looks like this:

from(bucket: "RetroEncabulator")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "kWh")
  |> filter(fn: (r) => r["_field"] == "SunState" or r["_field"] == "energy_consumed")
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")   // need to pivot data so that energy_consumed and SunState are in the same row
  |> yield(name: "consumption_at_night")

gives this:

If one wants to know the consumption when SunState = 0, add this filter:

  |> filter(fn: (r) => r["SunState"] == 0)

which gives this:

And then to query the power consumption when the sun is below the horizon (i.e. when SunState = 0), add a difference function (since the values of the power meter are always increasing, we need to know the difference between subsequent values), the same filter function as above, and then a sum() function to get the total energy consumed:

  |> difference(nonNegative: false, columns: ["energy_consumed"])
  |> filter(fn: (r) => r["SunState"] == 0)
  |> sum(column: "energy_consumed")

which gives this:

If you wanted to aggregate by day, you could use an aggregateWindow function like this:

    |> aggregateWindow(every: 1d, fn: sum, column:"energy_consumed")

PS: I am not a regular user of InfluxQL, so if that is how you plan to approach this, you might find this article helpful.

[grant2]

It may also help to see what the unfiltered data looks like in Grafana:

[larsxschneider]

Thank you very much for your detailed explainations and examples!

Unfortunately, it turns out that my kWh data and sun state is not pushed at the same time (see screenshots below). Are you still aware of a way to join these two data sets with Flux?

[grant2]

Hi @larsxschneider

In that event, I believe you would have to join the two tables, then do a pivot, then fill any missing values using previous value using a fill statement like

|> fill(column: "SunState", usePrevious: true)

Maybe @ldrascic or @fercasjr or @yosiasz can prescribe a better way to handle this. In any event, I am sure there is a solution to your problem.

[yosiasz]

@larsxschneider

Unfortunately, it turns out that my kWh data and sun state is not pushed at the same time

what is a common data point that these two data points share other than time field

Can you please share two sampling of data where the time of one is in comparison to the other.
Which data point’s date_time wins where there is a difference?

Can you modify your data push code to be combined so that they have the same datetime?

[fercasjr]

Haha, thanks for the invite @grant2 I do have an Idea but dont have ny computer at the moment.

Using flux

@larsxschneider

Unfortunately, it turns out that my kWh data and sun state is not pushed at the same time (see screenshots below). Are you still aware of a way to join these two data sets with Flux?

This doesn’t really matter you could “fake” timestamps inbetween and truncate to the same “precision”. Then do the pivot.

Aggregate window has an option to ad “null” values based on every, then you can use fill() to replace the null with previous ones, and with precision you can round the timestamps ie to 1m so if first data point was at 12:00.30 and the other table has 12:00.06 both will be rounded to 12:00

Brb with the functions

[fercasjr]

 |> aggregateWindow(every: 10s, fn: mean, createEmpty: true)
|> fill(usePrevious: true)
|> truncateTimeColumn(unit: 1m)

[grant2]

I generated some more random data, this time with completely different timestamps between the SunState and the energy_consumed for the period 10:33 to 10:40:

Inserting these functions suggested by @fercasjr …

  |> aggregateWindow(every: 10s, fn: mean, createEmpty: true)
  |> fill(usePrevious: true)
  |> truncateTimeColumn(unit: 10s)

we can see the data for both energy_consumed and SunState is indeed now populated every 10 seconds beginning at 10:33:40

We can see from the above 3 screenshots that when SunState = 0, the difference in energy is:
675 - 668 = 7
684 - 680 = 4

Using the same difference & sum functions when SunState=0 (as we did previously), we get 11 units of energy consumed:

[larsxschneider]

Thank you @grant2 ! Everything works up to the sum. I’ve tried lots of different things but I always get a 500 error (see screenshot). Do you have a clue what might be wrong?

I also had to use the keep to make pivot return everything in one row.

[grant2]

@larsxschneider

Perhaps that error is being caused by an empty response somewhere. Does changing your time picker to a narrower or completely different time range help or do you get the same error?

Can you put the same query (with and without the sum() function) into Influx Data Explorer and toggle to Raw Data View, then run the query and see if the same error appears?

[fercasjr]

the query looks good, it is hard to tell what’s going on.

need to see the entire data table but sum should be able to work with null values, non-subsequent times, gaps, and any I can think off.

However, if you want the sum as a single value you could use Grafana reduce transformation or use total on the calculate options in the panel.