Store historical data in InMemory persistence - display Grafana dashboard

BG56 · December 3, 2024, 10:33am

I run Openhab 4.2.3 on Pi5 and use RRD4j as default persistence and store electricity data on InfluxDB2 with Grafana on a Proxmox server.
A smartmeter provides data of the electricity usage in my house and transfers the data via MQTT to my Openhab system. Some of the data is stored in RRD4j but the totals of the daily power usage and monthly totals are stored in InfluxDB2 (to avoid deleting by RRD4j).
Details stored in RRD4j can be displayed in a widget and on a chart on Openhab screen:

With Grafana I have created a dashboard which presents the yearly data by month totals. The data presented on the Grafana dashboard is stored in InfluxDB2.

Within Grafana I can share the dashboard: Link (within the Link I replace “localhost” by the IP of the Grafana server), Snapshot or Embed (iframe). The problem is, that I can’t display the shared dashboard on the Openhab screen. Therefor I run the Grafana dashboard at the end of every month and create a screenshot of the dashboard an store it in the “html” folder of Openhab [\10.49.101.40\openHAB-conf\html](file://10.49.101.40/openHAB-conf/html). This is a bit too complicated, and I will lose the “dynamic functionality” of the chart.

Therefore, I want to move the 12 monthly totals of electricity usage for every year into the InMemory persistence … but I don’t know how to get the historical data into the persistence.

It would be great if you could help me, to get the Grafana dashboard displayed on the Openhab screen … or provide a small rule which writes the monthly electricity totals into the InMemory persistence … if possible, two years overlapping on the Openhab screen.

rlkoshak · December 3, 2024, 3:58pm

To use the in memory persistence you need to install it (it’s a separate add-on) and configure the persistence strategy. Unlike other persistence add-ons, it’s default strategy does not save any data.

You’ll need at least one Item though you might need one Item per month. I don’t use bar charts in OH so don’t know how they work.

Either way you’ll need a rule that calculates the totals for each month (I don’t know how those are stored in your InfluxDB already so the specifics are an exercise left to the student). You’ll at least need to calculate the last day of each month for the next step.

Then to save the value to the inmemory persistence will depend on the rules language you are using. In JS Scripting it would be:

items.ItemName.persistence.persist(endOfMonthDT, sum, 'inmemory');

where endOfMonthDT is a ZonedDateTime at 11:59:59 PM on the last day of that month, sum is the value you want to chart, and "inmemory" tells OH which persistence add-on to save the value to.

BG56 · December 3, 2024, 8:37pm

Thank you Rich @rlkoshak for your quick reply.
I created a rule with the 12 values for 2023.

items.Stromverbrauch_Monatsende.persistence.persist(‘2023-01-31T23:59:59’,484.704, ‘inmemory’);
items.Stromverbrauch_Monatsende.persistence.persist(‘2023-02-28T23:59:59’,437.797, ‘inmemory’);
items.Stromverbrauch_Monatsende.persistence.persist(‘2023-03-31T23:59:59’,494.810, ‘inmemory’);
items.Stromverbrauch_Monatsende.persistence.persist(‘2023-04-30T23:59:59’,492.663, ‘inmemory’);
items.Stromverbrauch_Monatsende.persistence.persist(‘2023-05-31T23:59:59’,442.657, ‘inmemory’);
items.Stromverbrauch_Monatsende.persistence.persist(‘2023-06-30T23:59:59’,442.288, ‘inmemory’);
items.Stromverbrauch_Monatsende.persistence.persist(‘2023-07-31T23:59:59’,446.869, ‘inmemory’);
items.Stromverbrauch_Monatsende.persistence.persist(‘2023-08-31T23:59:59’,477.908, ‘inmemory’);
items.Stromverbrauch_Monatsende.persistence.persist(‘2023-09-30T23:59:59’,415.493, ‘inmemory’);
items.Stromverbrauch_Monatsende.persistence.persist(‘2023-10-31T23:59:59’,476.385, ‘inmemory’);
items.Stromverbrauch_Monatsende.persistence.persist(‘2023-11-30T23:59:59’,497.806, ‘inmemory’);
items.Stromverbrauch_Monatsende.persistence.persist(‘2023-12-31T23:59:59’,555.073, ‘inmemory’);

… but I only see the first value with date of today

Did I use a wrong format for the date … or did I do anything else wrong?

rlkoshak · December 3, 2024, 10:18pm

You need to use a ZonedDateTime, (i.e. time.ZonedDateTime) Object. You are just using a String here. You need to convert that to a ZonedDateTime. Those appear to be ISO8601 so it might be as simple as time.toZTD("2023-01-...)

BG56 · December 4, 2024, 3:29pm

Thank you Rich @rlkoshak for clarification. By preparing the needed data I discovered a strange behaviour of Blockly rules when reading data from RRD4j - I copy @stefan.hoehn who as far as I know is managing Blockly.
I get the following result when I read the 3 values for date 2024-11-30, 2024-12-01 and 2024-12-02:
2024-12-04 15:49:17.909 [INFO ] [tion.script.ui.TestPersistenceDate01] - 2024-11-30: 9662.294
2024-12-04 15:49:17.918 [INFO ] [tion.script.ui.TestPersistenceDate01] - 2024-12-01: 9662.294
2024-12-04 15:49:17.929 [INFO ] [tion.script.ui.TestPersistenceDate01] - 2024-12-02: 9690.373

The date of 2024-12-01 is the same as of 2024-11-30 but it has to be 9676.127
… which can also be seen in the following chart:

I used the following Test rule:

Could you please explain the behaviour of Blockly (when the month switches)

rlkoshak · December 4, 2024, 3:47pm

rrd4j stores data every minute. So there will be values stored for that date all throughout the day.

Do you want the value at the start of that date or at the end of that date? I think by default if you do not specify it, the value returned will be on that date at midnight (i.e. the start of the day). So unless you wrote that month’s data to rrd4j before midnight on the previous day, you’ll be picking up the previous month’s data.

BG56 · December 4, 2024, 3:59pm

A Openhab rule has stored the value at midnight (Austria time … is same as German time) on all three days. Why do two of three days show the same value when I get the values with Blockly and show three different values in the chart? The chart values are right!

rlkoshak · December 4, 2024, 4:06pm

Exactly at midnight? Not midnight plus 100-200 msec? Storing a value take some time. Your query is asking for the data at exactly 2024-11-30T00:00:00.000 but the value might not have been saved until 2024-11-30T00:00:00.150. Also, if you want the full month’s worth of data, should the value be written at 2024-12-01T00:00:00.000? If you write at midnight on the 30th you miss a whole day from the month.

The timing is really important here.

Whay does it show correctly in the chart? Because these tiny bits of difference in the chart are imperceptible.

BG56 · December 4, 2024, 4:12pm

Shall I use noon time 2024-12-01T12:00:00.000 instead of midnight 2024-12-01T00:00:00.000 of each day to have enough tolerance?

rlkoshak · December 4, 2024, 4:30pm

But what you need to do is figure out exactly when the rule writes these values to persistence and then choose a time in your query that is reliably after that. 12 hours is way overkill and the persistence functions will only look back so far. A second should be sufficient. But there’s not enough info here to give a definitive answer.

For example, If your rule runs at 11:59:59.999 to save the value (for example) you might want your query to be at least 00:00:00.100 on the next day to reliably get the saved value. It takes some milliseconds to actually complete the save to persistence. 100 msecs is usually enough. Once second is almost always going to be enough.

BG56 · December 4, 2024, 4:59pm

I thougt it might be better to have a specific value for every day … but now I see the difficulties when doing that. Beside the smartmeter line in day steps, I also have a continously growing smartmeter line (where you don’t see steps).

When I use my rule with these values I get the right results:
2024-12-04 17:31:36.307 [INFO ] [tion.script.ui.TestPersistenceDate01] - 2024-11-30: 9662.294
2024-12-04 17:31:36.313 [INFO ] [tion.script.ui.TestPersistenceDate01] - 2024-12-01: 9676.127
2024-12-04 17:31:36.320 [INFO ] [tion.script.ui.TestPersistenceDate01] - 2024-12-02: 9690.373

In this case a continuos line is better than a line with steps!
Thank you very much Rich for this exciting discussion - training for myself.

BG56 · December 5, 2024, 7:32pm

Sorry Rich I still have problems by storing the values in InMemory.

I have created a number item and tried the following rule:

items.kWh2024.persistence.persist(time.toZTD(‘2024-01-31T23:59’),558.205, ‘inmemory’);
items.kWh2024.persistence.persist(time.toZTD(‘2024-02-28T23:59’),455.587, ‘inmemory’);
…
I couldn’t see items in the chart

Then I tried TimeSeries. I found the documentation on JavaScript Scripting

My adjusted rule:

var timeSeries = new items.TimeSeries('ADD'); // Create a new TimeSeries with policy ADD
timeSeries.add(time.toZDT('2024-01-31T23:59'), Quantity('558.205 kWh')).add(time.toZDT('2024-02-28T23:59'), Quantity('455.587 kWh')).add(time.toZDT('2024-03-31T23:59'), Quantity('481.220 kWh')).add(time.toZDT('2024-04-30T23:59'), Quantity('483.733 kWh')).add(time.toZDT('2024-05-31T23:59'), Quantity('451.596 kWh'));
console.log(timeSeries); // Let's have a look at the TimeSeries
items.getItem('kWh2024').persistence.persist(timeSeries, 'inmemory'); // Persist the TimeSeries for the Item 'MyDistanceItem' using the InMemory persistence service

The log info

2024-12-05 19:47:13.874 [INFO ] [tion.script.ui.WriteDataIntoInMemory] - TimeSeries (Begin=2024-01-31T22:59:00Z, End=2024-05-31T21:59:00Z, Size=5, States=[2024-01-31T22:59:00Z → 558.205 kWh], [2024-02-28T22:59:00Z → 455.587 kWh], [2024-03-31T21:59:00Z → 481.22 kWh], [2024-04-30T21:59:00Z → 483.733 kWh], [2024-05-31T21:59:00Z → 451.596 kWh])

The TimeSeries seems to be created, but when I click on item kWh2024, I dont see any values

It’s a bit difficult …
… any hint how to step forward?

rlkoshak · December 6, 2024, 3:09am

I don’t really use the inmemory persistence nor have I ever used Timeseries. So beyond what’s in the docs I can’t really help. But I know that charts will use the default persistence engine unless specifically configured to use one of the other ones. I believe you set the persistene engine to use on a per Item basis in the chart settings.

BG56 · December 6, 2024, 8:45am

Yes you are right - charts use the default persisctence service … but you are able to activate “show advanced” in the Item / Time-Series settings in the charts definition and select the persistence service

Thanks! One step forward!

BG56 · December 8, 2024, 3:40pm

I have now moved the data into the InMemory persistence and “converted” the Grafana dashboard to a Openhab Chart:

I first tried to display the total of the month with time “last day of month”. This displayed the February data above “Mar” and April data above “May” … which was a bit irritating. That’s the reason why I changed the time to display the total for every month on the “10th” of the month “yyyy-mm-10” which looks better.

I used the following rules to get the data into InMemory

JavaScript rule to get the 12 month totals for every year into a TimeSeries

//
// Year 2023
// Create a new TimeSeries with policy ADD 
var timeSeries2023 = new items.TimeSeries('ADD'); 
timeSeries2023.add(time.toZDT('2024-01-10T23:59'), 
                       Quantity('484.704 kWh')).add(time.toZDT('2024-02-10T23:59'), Quantity('437.797 kWh')).add(time.toZDT('2024-03-10T23:59'), 
                       Quantity('494.810 kWh')).add(time.toZDT('2024-04-10T23:59'), Quantity('492.663 kWh')).add(time.toZDT('2024-05-10T23:59'), 
                       Quantity('442.657 kWh')).add(time.toZDT('2024-06-10T23:59'), Quantity('442.288 kWh')).add(time.toZDT('2024-07-10T23:59'), 
                       Quantity('446.869 kWh')).add(time.toZDT('2024-08-10T23:59'), Quantity('477.908 kWh')).add(time.toZDT('2024-09-10T23:59'),
                       Quantity('415.493 kWh')).add(time.toZDT('2024-10-10T23:59'), Quantity('476.385 kWh')).add(time.toZDT('2024-11-10T23:59'),
                       Quantity('497.806 kWh')).add(time.toZDT('2024-12-10T23:59'), Quantity('555.073 kWh')) ;
// Let's have a look at the TimeSeries 
console.log(timeSeries2023); 
// Persist the TimeSeries for the Item 'kWh2024' using the InMemory persistence service
items.getItem('kWh2023').persistence.persist(timeSeries2023, 'inmemory'); 
//
// Year 2024
// Create a new TimeSeries with policy ADD
var timeSeries2024 = new items.TimeSeries('ADD'); 
timeSeries2024.add(time.toZDT('2024-01-10T23:59'), 
                          Quantity('558.205 kWh')).add(time.toZDT('2024-02-10T23:59'), Quantity('455.587 kWh')).add(time.toZDT('2024-03-10T23:59'), 
                          Quantity('481.220 kWh')).add(time.toZDT('2024-04-10T23:59'), Quantity('483.733 kWh')).add(time.toZDT('2024-05-10T23:59'), 
                          Quantity('451.596 kWh')).add(time.toZDT('2024-06-10T23:59'), Quantity('486.790 kWh')).add(time.toZDT('2024-07-10T23:59'),
                          Quantity('452.671 kWh')).add(time.toZDT('2024-08-10T23:59'), Quantity('446.660 kWh')).add(time.toZDT('2024-09-10T23:59'),
                          Quantity('445.123 kWh')).add(time.toZDT('2024-10-10T23:59'), Quantity('467.268 kWh')).add(time.toZDT('2024-11-10T23:59'),
                          Quantity('417.602 kWh')).add(time.toZDT('2024-12-10T23:59'), Quantity('80.970 kWh')) ;
// Let's have a look at the TimeSeries
console.log(timeSeries2024); 
// Persist the TimeSeries for the Item 'kWh2024' using the InMemory persistence service
items.getItem('kWh2024').persistence.persist(timeSeries2024, 'inmemory');

at the begin of the year all 12 values can be 0.0 and you can step by step update every month with the next script which updates only the current month

JavaScript rule which does not update all 12 value but only the value of current month

//
// Year 2024
// Replace one month of the TimeSeries with policy REPLACE
var timeSeries2024 = new items.TimeSeries('REPLACE'); 
timeSeries2024.add(time.toZDT('2024-12-10T23:59'), Quantity('95.448 kWh')) ;
// Let's have a look at the TimeSeries
console.log(timeSeries2024); 
// Persist the TimeSeries for the Item 'kWh2024' using the InMemory persistence service
items.getItem('kWh2024').persistence.persist(timeSeries2024, 'inmemory');

you can run this rule every day so that you can see how the month increase

The rules still need to be improved (update the values in the rule) so that they run without my help (fully automated)

Chart Code - see where to define the persistence - service: inmemory

config:
  chartType: year
  label: kWh2024
  period: Y
slots:
  grid:
    - component: oh-chart-grid
      config:
        includeLabels: true
        containLabel: true
        show: true
  legend:
    - component: oh-chart-legend
      config:
        bottom: 3
        type: scroll
  series:
    - component: oh-time-series
      config:
        gridIndex: 0
        item: kWh2023
        name: kWh 2023
        service: inmemory
        type: scatter
        xAxisIndex: 0
        yAxisIndex: 0
    - component: oh-time-series
      config:
        gridIndex: 0
        item: kWh2024
        name: kWh 2024
        service: inmemory
        type: bar
        xAxisIndex: 0
        yAxisIndex: 0
  tooltip:

Beside the work to improve the rules I’m still interested to know how to display Grafana dashboards within the Openhab screen (Grafana has much more possibilities to adjust a chart). Many thanks again to Rich who helps to overcome some gaps in my knowledge with some hints to get that done!