Help with power meter which resets energy usage at midnight

[gyrex]

Hi everyone,

I have a bunch of energy monitors and most of them record energy usage (kWh) cumulatively but I have some legacy ones (Meross) which record energy during the day but then reset at midnight every night. This is frustrating because when I try and use the queries I’ve developed for the other energy monitors on these plugs, at midnight, the energy usage goes into negative values and I’m hoping someone might be able to help me formulate a query where the consumption data rolls on rather than resetting at midnight? Here’s an example bar graph of what I’m talking about

This is the query I’m using:

SELECT difference(distinct("value"))/1000 FROM "Wh" WHERE ("entity_id"::tag = 'smart_plug_19090329639802251h1448e1e900eb97_energy') AND $timeFilter GROUP BY time(1h) fill(null) tz('Australia/Brisbane')

Side note: because my other energy monitors record the data to influxdb in kWh but these meross plugs record their energy in Wh, is using ‘/1000’ in the query the best way to convert this data or is there a better way?

Can someone help me please? Many thanks in advance.

[grant2]

Welcome @gyrex to the Grafana forum.

Have you tried the spread function and grouping by day? It returns the difference between the minimum and maximum field values.

[gyrex]

Wow! Thanks so much for your help Grant, I think that achieved exactly what I was looking for

Grant, as you can see I’ve used a basic formula (divide by 1000) in the query to convert Wh to kWh - is this the best way to achieve this?

Also, for anyone else who might see this in the future, this is my query for hourly consumption:

SELECT spread("value")/1000 FROM "Wh" WHERE ("entity_id"::tag = 'smart_plug_19090329639802251h1448e1e900eb97_energy') AND $timeFilter GROUP BY time(1h) fill(null) tz('Australia/Brisbane')

Daily consumption:

SELECT spread("value")/1000 FROM "Wh" WHERE ("entity_id"::tag = 'smart_plug_19090329639802251h1448e1e900eb97_energy') AND $timeFilter GROUP BY time(1d) fill(null) tz('Australia/Brisbane')

[grant2]

@gyrex

is this the best way to achieve this?

Another way is to keep everything in Wh, then do a transformation in Grafana like this:

But I prefer to do all the math in the query as you have done.

[gyrex]

Wonderful, thanks again for all your help Grant.