# Query consumption/time based on another field (energy consumption per day when sun is down) **URL:** \ **Category:** InfluxDB\ **Created:** [May 6, 2023, 8:49am UTC](https://community.grafana.com/t/query-consumption-time-based-on-another-field-energy-consumption-per-day-when-sun-is-down/87166 "2023-05-06T08:49:02Z")\ **Posts on this page:** 15\ **Page:** 1
**Author:** ![larsxschneider](https://avatars.discourse-cdn.com/v4/letter/l/dc4da7/32.png) [@larsxschneider](https://community.grafana.com/u/larsxschneider)\ **Post date:** [May 6, 2023, 8:49am UTC](https://community.grafana.com/t/query-consumption-time-based-on-another-field-energy-consumption-per-day-when-sun-is-down/87166/1 "2023-05-06T08:49:02Z")
- 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: ```auto 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? ---
**Author:** ![grant2](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/grant2/32/42281_2.png) [@grant2](https://community.grafana.com/u/grant2)\ **Post date:** [May 6, 2023, 11:46am UTC](https://community.grafana.com/t/query-consumption-time-based-on-another-field-energy-consumption-per-day-when-sun-is-down/87166/2 "2023-05-06T11:46:39Z")
> [@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. ---
**Author:** ![larsxschneider](https://avatars.discourse-cdn.com/v4/letter/l/dc4da7/32.png) [@larsxschneider](https://community.grafana.com/u/larsxschneider)\ **Post date:** [May 6, 2023, 6:59pm UTC](https://community.grafana.com/t/query-consumption-time-based-on-another-field-energy-consumption-per-day-when-sun-is-down/87166/3 "2023-05-06T18:59:10Z")
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](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”. ---
**Author:** ![mariusbach](https://avatars.discourse-cdn.com/v4/letter/m/b9bd4f/32.png) [@mariusbach](https://community.grafana.com/u/mariusbach)\ **Post date:** [May 7, 2023, 8:36am UTC](https://community.grafana.com/t/query-consumption-time-based-on-another-field-energy-consumption-per-day-when-sun-is-down/87166/4 "2023-05-07T08:36:52Z")
sorry, misposted. disregard. ---
**Author:** ![grant2](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/grant2/32/42281_2.png) [@grant2](https://community.grafana.com/u/grant2)\ **Post date:** [May 7, 2023, 12:11pm UTC](https://community.grafana.com/t/query-consumption-time-based-on-another-field-energy-consumption-per-day-when-sun-is-down/87166/5 "2023-05-07T12:11:24Z")
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. ```auto _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: ```auto 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: ![image](https://us1.discourse-cdn.com/grafana/original/3X/4/5/45deee4589a83e139e4d82c9f2bafb2c5b2bb2f2.png) If one wants to know the consumption when SunState = 0, add this filter: ```auto |> filter(fn: (r) => r["SunState"] == 0) ``` which gives this: ![image](https://us1.discourse-cdn.com/grafana/original/3X/a/0/a0c6bbc075fd808934832e3fa4082d4213efbb88.png) 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: ```auto |> difference(nonNegative: false, columns: ["energy_consumed"]) |> filter(fn: (r) => r["SunState"] == 0) |> sum(column: "energy_consumed") ``` which gives this: ![image](https://us1.discourse-cdn.com/grafana/original/3X/4/0/401f2fb6ba8db20d92e4a3875544dce8420054e6.png) If you wanted to aggregate by day, you could use an aggregateWindow function like this: ```auto |> 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](https://www.influxdata.com/blog/tldr-influxdb-tech-tips-converting-influxql-queries-flux-queries/) helpful. ---
**Author:** ![grant2](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/grant2/32/42281_2.png) [@grant2](https://community.grafana.com/u/grant2)\ **Post date:** [May 7, 2023, 12:35pm UTC](https://community.grafana.com/t/query-consumption-time-based-on-another-field-energy-consumption-per-day-when-sun-is-down/87166/6 "2023-05-07T12:35:25Z")
It may also help to see what the unfiltered data looks like in Grafana: ![image](https://us1.discourse-cdn.com/grafana/original/3X/6/a/6aeaee34ba05d73eed58c876b15f1445d7f446c3.png) ---
**Author:** ![larsxschneider](https://avatars.discourse-cdn.com/v4/letter/l/dc4da7/32.png) [@larsxschneider](https://community.grafana.com/u/larsxschneider)\ **Post date:** [May 11, 2023, 8:34pm UTC](https://community.grafana.com/t/query-consumption-time-based-on-another-field-energy-consumption-per-day-when-sun-is-down/87166/7 "2023-05-11T20:34:15Z")
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? ![Screenshot 2023-05-11 at 22.31.12](https://us1.discourse-cdn.com/grafana/original/3X/5/f/5f40e4b8609b7d4987b96519d14290355ad86890.png) ![Screenshot 2023-05-11 at 22.30.30](https://us1.discourse-cdn.com/grafana/original/3X/a/3/a366c4e4f467a351bec5057d5de76c31a3cc46ca.png) ---
**Author:** ![grant2](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/grant2/32/42281_2.png) [@grant2](https://community.grafana.com/u/grant2)\ **Post date:** [May 11, 2023, 8:53pm UTC](https://community.grafana.com/t/query-consumption-time-based-on-another-field-energy-consumption-per-day-when-sun-is-down/87166/8 "2023-05-11T20:53:05Z")
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 ```auto |> 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. ---
**Author:** ![yosiasz](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/yosiasz/32/45847_2.png) [@yosiasz](https://community.grafana.com/u/yosiasz)\ **Post date:** [May 11, 2023, 9:15pm UTC](https://community.grafana.com/t/query-consumption-time-based-on-another-field-energy-consumption-per-day-when-sun-is-down/87166/9 "2023-05-11T21:15:53Z")
> [@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? ---
**Author:** ![fercasjr](https://avatars.discourse-cdn.com/v4/letter/f/e495f1/32.png) [@fercasjr](https://community.grafana.com/u/fercasjr)\ **Post date:** [May 11, 2023, 11:02pm UTC](https://community.grafana.com/t/query-consumption-time-based-on-another-field-energy-consumption-per-day-when-sun-is-down/87166/10 "2023-05-11T23:02:55Z")
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 ---
**Author:** ![fercasjr](https://avatars.discourse-cdn.com/v4/letter/f/e495f1/32.png) [@fercasjr](https://community.grafana.com/u/fercasjr)\ **Post date:** [May 11, 2023, 11:12pm UTC](https://community.grafana.com/t/query-consumption-time-based-on-another-field-energy-consumption-per-day-when-sun-is-down/87166/11 "2023-05-11T23:12:11Z")
```auto |> aggregateWindow(every: 10s, fn: mean, createEmpty: true) |> fill(usePrevious: true) |> truncateTimeColumn(unit: 1m) ``` ---
**Author:** ![grant2](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/grant2/32/42281_2.png) [@grant2](https://community.grafana.com/u/grant2)\ **Post date:** [May 12, 2023, 11:11am UTC](https://community.grafana.com/t/query-consumption-time-based-on-another-field-energy-consumption-per-day-when-sun-is-down/87166/12 "2023-05-12T11:11:53Z")
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: ![image](https://us1.discourse-cdn.com/grafana/original/3X/f/d/fd25cd02c44280d4d4debdcaefdea84be60bb37e.png) Inserting these functions suggested by @fercasjr … ```auto |> 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 ![image](https://us1.discourse-cdn.com/grafana/original/3X/6/6/666a3a3eadeab796b03cf5487c71b0bc764cb1d2.png) ![image](https://us1.discourse-cdn.com/grafana/original/3X/8/6/863c694b4cb811540488a796e426432e49b7b858.png) ![image](https://us1.discourse-cdn.com/grafana/original/3X/6/a/6a906bc700ec7ad0d8233378ca90d0582047bd80.png) 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: ![image](https://us1.discourse-cdn.com/grafana/original/3X/8/0/80a992e1e25f4f1f5a6ae2dcf00c59c69ee865bd.png) ---
**Author:** ![larsxschneider](https://avatars.discourse-cdn.com/v4/letter/l/dc4da7/32.png) [@larsxschneider](https://community.grafana.com/u/larsxschneider)\ **Post date:** [May 24, 2023, 9:30pm UTC](https://community.grafana.com/t/query-consumption-time-based-on-another-field-energy-consumption-per-day-when-sun-is-down/87166/13 "2023-05-24T21:30:55Z")
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. ![Screenshot 2023-05-24 at 23.12.11](https://us1.discourse-cdn.com/grafana/original/3X/f/3/f3531c0e9e14bb9b64e8f70d50e3c067c5cfc0c8.png) ![Screenshot 2023-05-24 at 23.26.24](https://us1.discourse-cdn.com/grafana/original/3X/f/e/fe4e3fd587904e58dbf7578ff45f7077a8cc7115.png) ---
**Author:** ![grant2](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/grant2/32/42281_2.png) [@grant2](https://community.grafana.com/u/grant2)\ **Post date:** [May 25, 2023, 9:48am UTC](https://community.grafana.com/t/query-consumption-time-based-on-another-field-energy-consumption-per-day-when-sun-is-down/87166/14 "2023-05-25T09:48:23Z")
@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? ---
**Author:** ![fercasjr](https://avatars.discourse-cdn.com/v4/letter/f/e495f1/32.png) [@fercasjr](https://community.grafana.com/u/fercasjr)\ **Post date:** [May 25, 2023, 2:49pm UTC](https://community.grafana.com/t/query-consumption-time-based-on-another-field-energy-consumption-per-day-when-sun-is-down/87166/15 "2023-05-25T14:49:41Z")
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.