Historical Data query

[erwinvol]

Hi All,

I have Vuegraf up and running and ran a import from the beginning of the year when I installed my Vue. I see the data in the database but for the life of me I am unable to create a query and show the same YTD usage as in the Emporia app. This is the query I'm working with:

from(bucket: "vuegraf")
|> range(start: 2024-01-01T00:00:00Z)
|> filter(fn: (r) => r["_measurement"] == "energy_usage")
|> filter(fn: (r) => r["device_name"] == "VolVue")
|> filter(fn: (r) => (r["detailed"] == "Day"))
|> yield()

Which results in 42000. The Emporia app shows 4808. I read the discussions about coversions but /3600 or /60000 doesnt get me anywhere close.

Thanks for your help!

[jertel]

Is the app showing 4808 kWh or 4808 watts? I believe the "Day" metric needs to be divided by 1000 to convert from watts to kilowatts. For the discrepancy, it might be easier to diagnose if you look back 24 hours instead of the full year. Does the most recent "Day" record look reasonable?

[erwinvol]

Thanks for the quick answer. When I take september or last week as the dataset, and /1000 it aligns with the data from the Vue app. Jan and Feb are off though where Vuegraf shows more than Emporia itself but the difference is not large enough to care about but enough to throw me off.

For posterity, this query gives me an accurate kWh usage in the selected time range for the chosen device.

from(bucket: "vuegraf")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "energy_usage")
|> filter(fn: (r) => r["device_name"] == v.Device)
|> filter(fn: (r) => (r["detailed"] == "Day"))
|> map(fn: (r) => ({ r with _value: (r._value / 1000.0)}))
|> group()
|> sum()

[jertel]

Any chance you accidentally duplicated the data load for Jan and Feb by running Vuegraf multiple times with the load history flag?

…

On Tue, Oct 8, 2024 at 7:36 PM Erwin ***@***.***> wrote: Thanks for the quick answer. When I take september or last week as the dataset, and /1000 it aligns with the data from the Vue app. Jan and Feb are off though where Vuegraf shows more than Emporia itself but the difference is not large enough to care about but enough to throw me off. For posterity, this query gives me an accurate kWh usage in the selected time range for the chosen device. from(bucket: "vuegraf") |> range(start: v.timeRangeStart, stop: v.timeRangeStop) |> filter(fn: (r) => r["_measurement"] == "energy_usage") |> filter(fn: (r) => r["device_name"] == v.Device) |> filter(fn: (r) => (r["detailed"] == "Day")) |> map(fn: (r) => ({ r with _value: (r._value / 1000.0)})) |> group() |> sum() — Reply to this email directly, view it on GitHub <#165 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AD66M66RNMTDOBHYKMXFXYLZ2RT7RAVCNFSM6AAAAABPRDLWYSVHI2DSMVQWIX3LMV43URDJONRXK43TNFXW4Q3PNVWWK3TUHMYTAOBYGYYDMMA> . You are receiving this because you commented.Message ID: ***@***.***>

[erwinvol]

Not likely, I used the reset database flag when I did the history load a couple of days ago.

January is 239 kWh in Emporia and 283 kWh in Vuegraf (installed the Emporia on Jan 20). If i query data from 1-20 Jan, vuegraf returns 42kWh that shouldn't be there.

February is 583 kWh in Emporia and 553 kWh in Vuegraf
From March on they are in sync. Really odd

[cooldil]

Pretty sure that discrepancy that @erwinvol is seeing is due to the fact that when Vuegraf populates the historical DAY/DAILY data it does so based on the UTC timezone. As such, the daily value is based of the UTC 24 hours. Unless the power consumption is uniform across the entire 24 hours, there will be a difference in the daily value pulled by Vuegraf vs what the Emporia app shows since the latter is "timezone localized".

During normal runs (when the history flag is not set), the time difference is accounted for due to how the curDay and pastDay variables are set. However, I don't see a similar adjustment being made when the history flag is set since that particular section of the code goes off of the "non-localized" stopTime variable.

For example: I am in the US Eastern timezone (UTC-5). The daily usage pulled via Vuegraf show up in the database at 18:59:59 EST which is 23:59:59 UTC. Below is one line from the debug output of the influx lp protocol:

energy_usage,account_name=Primary\ Residence,device_name=someDevice,granularity=day usage=3.3506122150686055 1734393599000000000

1734393599000000000 = Dec 16, 2024 18:59:59 EST / Dec 16, 2024 23:59:59 UTC

Thought the fix would be as easy as "localizing" the stopTime variable but that does not seem to help at my first and only attempt so far to try to fix this.

[jertel]

I don't think this is related to this thread, primarily because the original problem report was specific only to Jan and Feb data being "off", while the rest of the months were fine. In October, the author stated:

Not likely, I used the reset database flag when I did the history load a couple of days ago.

If the day aggregations were skewed by the timezone it would have affected all months up to Oct, not just Jan and Feb.

I suggest creating a new discussion topic and we can continue the discussion there.