Advice on data format for daily, weekly and monthly views

I am trying to graph my home power usage by capturing data from my power meter and storing it in InfluxDB with Grafana for visualization.

The data coming from my meter is cumulative and so it is a large number that always grows, and I will be using Python to push the data to InfluxDB. I want to have the ability to view the data on a daily, weekly and monthly basis, and I am comfortable enough with Python that I could do some form of data transformation if needed.

This gets me to my question. What is the best way to store the data to be able to get the desired daily, weekly and monthly views?

My guess is that it would be easiest to store the changes as it would be easier to group the data. Is that right? (The one problem is that the data does not change all that rapidly.) If I stored the cumulative number, could I still access the desired views?

TIA in advance for any thoughts.

Hi @jl678! If your raw data represents cumulatively rising energy readings then I think it’s best to just store that directly. Influx has pretty nice functionality when it comes to converting that to consumption readings for any desired time period. E.g. the difference or derivative functions. Happy to give you some examples of what that could look like.

The main limitation of this is that if you’re using InfluxQL, then there is no concept of aggregating data on a calendar month basis - only over a certain number of days (e.g. 30-day periods). But maybe that’s not an issue. If you use Flux to query then this won’t be a problem.

Hi @svetb! This sounds great. I understand the issue regarding the month and am okay with that and can switch to flux in the future if my needs change. (I assume that that is a an easy change although I think that it requires influx 2.0 which I can deal with separately.)

I would love an example of the difference/derivative. The nature of the data is that I will get values frequently (maybe every 2 minutes or so), but it will change infrequently. (e.g. it could be the same value for an hour or it could change every 15 minutes depending on power usage, of course.) I am not sure if any of this matters to the choice of difference vs derivative.

Oh and my preferred output is a bar chart showing usage. (e.g. imagine seeing usage summed over 2 hour increments, 12 hours increments and 24 hour increments.)

TY!

Update:
I think that I figured this out, but I do not have enough data to verify. Does this look right for a 2 hour interval?

SELECT difference(last("value")) FROM "kWh" WHERE $timeFilter GROUP BY time(2h) fill(null)

Hi @jl678 yeah that indeed looks right!

I do think that difference is more appropriate than derivative in this use case. Also out of all the different aggregation functions, last is probably the best choice here. If you end up in a situation where your sample rate is much lower than the resolution of your chart, you may want to consider fill(linear), though it doesn’t sound like this is likely here (and fill(null) will serve you just fine overall).

Anyway, you can experiment a bit in case what you have doesn’t appear to quite do what you need it to, but it’s as good a starting point as any.

@svetb Awesome stuff. It works perfectly! Thank you so much for the guidance. I really appreciate it.

This topic was automatically closed after 365 days. New replies are no longer allowed.