Project

General

Profile

Smartvisu periode issue - possible solution

Added by Raymond about 3 years ago

Smartvisu issue, for who is interested smirk

By running a Smartvisu graphic with data from Domotiga, Smartvisu is limited to 1200 records, one record more in the resultset and the graph will be blank.
With measuring uses off gas, water and energy, I save the data every minute (*1) in DomotiGa with a json script.
That mains that the output, in my case, is limited to 20 hours. That’s not what I want.

One of the findings, is that the parameter ‘Step’ will be used to limit the record count. In mine vision is the definition of a step a timeblock between two records with a minimum (in my case) of 1 minuut. So we have to group data in a timeblock, the function SUM, AVG, MAX, MIN is required.

Example 1 : measure data over a periode of 5 days in steps/groups of 1 hour… ( 60 x 1 minute) is a result set with 120 records/samples.

{{ plot.period('p5', ['UsesWater_plot4'], 'sum', '5d','' ,'' , '', 60,'ltr/water', '#e00', ['column'], '', '')}}

Example 2 : measure data over a periode of 1 day in steps/groups of 10 minute… ( 10 x 1 minute) > resultset with 144 records/samples.

{{ plot.period('p6', ['UsesWater_plot4'], 'sum', '1d','' ,'' , '', 10,'ltr/water', '#e00', ['column'], '', '')}}

The next Query will do the job.

SELECT lastchanged , unixtime, value
FROM ( Select lastchanged
, unix_timestamp(lastchanged) DIV ( 60 * 60 ) As TimeGroup
, unix_timestamp(lastchanged) * 1000 As unixtime
, Sum( value ) as value
FROM device_values_log
WHERE device_id='38' AND valuenum = '4' AND lastchanged between '2016-04-02 00:00:00'
AND '2016-04-03 00:00:00'
Group By Timegroup
ORDER BY lastchanged DESC LIMIT 1000 ) AS tbl
ORDER BY tbl.unixtime ASC

I changed the function GetPlotData in the module CSmartVisuServer.class ( see attachemnt) and that’s working for me.

Now i can determine the Steps ( in minutes) and limit in that way the count of records.
Also the functions Min, Max, Avg, Sum are available.

Maybe i'm missing something, so I’m looking for feedback of this is the right way…

*1. However I have 1 issue left.
I used the data from the 4th value field on a device, and I’m missing data in my report.
The finding is that the changelog function only works when the next offered value is different from the previous value.
Is there a solution to register, duplicate value’s after each other, in the value log table?

Regards Raymond


Replies (20)

RE: Smartvisu periode issue - possible solution - Added by thomas.hicks about 3 years ago

Hi Raymond,

I've come across your solution hoping it would solve my problem but it doesn't seem to solve it.
I currently have two values (actual consumption in W and total kWh counted) that get saved to devicevalueslog more than once a minute. I've tried to lessen the amount of logging but seems like I cannot change it to less than +- 3times per minute.

I currently would like to plot : Total kWh over 7 and 30 days and a reasonable plot for the actual consumption.

When using your changed .class-file I got to show Total kWh for today and yesterday however I'm using

"{{ plot.period('p2', ['kWh.Teller_plot3'], 'sum', '1m','' ,'' , '', 180,'kWh', '#e00', ['column'], '', '')}}" and would expect to show the day before yesterday as well as I have logs from that day too. I've used 30,60,180 - steps but keeps giving me the same results.

I thought your change would group multiple values together but when I try this with the actual consumption I can use Step 1100 but this still shows my all the values.

I'm certainly missing something and was wondering if you could help me out.

Regards,

Tom

RE: Smartvisu periode issue - possible solution - Added by lolouk44 about 3 years ago

Hi Raymond,

This looks very promising as it would fix the very same problem that I have with my power consumption charts.
I'll be trying this soon and will feedback on my findings.

Thanks
Laurent

RE: Smartvisu periode issue - possible solution - Added by lolouk44 about 3 years ago

Hi Raymond,

I can't see where to make the changes. Are you able to detail further where the file is located?

Thanks
Laurent

PS. I will have the same issue as you with the log only being written if the data is different than previous.

RE: Smartvisu periode issue - possible solution - Added by Raymond about 3 years ago

Hi Tom,

Do you have a screen capture of your graphics and your datatable. In the attachement, you see a result of your wishes with my data.

Because you said that there is no difference between the images, is my question: In which folder do you have replaced the code, and do you have compiled the code?

My change wil group multiple values i a time block with a minimum limit of 1 minute, so the number 180, in your example, means a time block of 3 hours. All data in that block will be grouped by sum, avg, min of ,max. So i limit the total records in the dataset.

Regards
Raymond

RE: Smartvisu periode issue - possible solution - Added by thomas.hicks about 3 years ago

Hi Raymond,

I've placed the CSmartVISUServer.class - file in the directories ~/domotiga/DomotiGa3/.src and ~/domotiga/DomotiGaServer3/.src/

I'll retry your files but I would probably need to recreate my dataset. As it was not working I tried another approach to limit my loggings by using a virtual device that saves by using events (and cron). So my current graphs are not based on the same data that I was using while testing out your code.

I've deleted that data now as logging every 10s seemed overkill to me.

RE: Smartvisu periode issue - possible solution - Added by Raymond about 3 years ago

Hi Tom, replacing of the files is not enough, have you compile the code? edit: see message below smiley
I think that it will not be necessary to change your data file..
btw, sample every 10 seconds, why you would that?

RE: Smartvisu periode issue - possible solution - Added by Raymond about 3 years ago

Hi Laurent,

You have to replace the files in de folder ~/domotiga/DomotiGa3/.src and ~/domotiga/DomotiGaServer3/.src/.
After that, you have to compile the code.

1. stop service : pi@raspberrypi ~ $ sudo /etc/init.d/domotigaserver3 stop

2. compile: pi@raspberrypi ~ $ tools/compile.sh

3. start service : pi@raspberrypi ~ $ sudo /etc/init.d/domotigaserver3 start

Raymond

RE: Smartvisu periode issue - possible solution - Added by Raymond about 3 years ago

Hi Laurent,

Here is my solutiuon to register equal numbers to a new datarecord.
The trick is to reset a value field to zero, so i can detected the next value.
But i needed also a field to show my last value or sum off that.

I used 2 values fields and a event for a device.
Value 4 wil be the trigger value, i wil reset this after each change.
Value 3 wil be use to display the last value, or sum of value.

- value 4 is the last set value, in my cases this datafield will be filled by a js-on script.
- value 4 will be logged to the database
- Create a event
- Create a trigger on device 4 : device value > 0
- Create a action - script : add or replace value 4 to value 3. ( not necessary but handy to show the last value)
example: (Devices.ValueUpdate(38, 3, Dev_38_Value3 + Dev_38_Value4 )
- Create a action - set device , value 4 to Value 0.

let me know, of this is working for you.

Regards
Raymond

RE: Smartvisu periode issue - possible solution - Added by thomas.hicks about 3 years ago

Hi Raymond,

Trying to compile gives me the following result :

pi@raspy2-domotiga ~/domotiga $ tools/compile.sh
CSmartVISUServer.class:104:6: error: Unknown operator
-rwxr-xr-x 1 pi pi 1304593 Jun 5 20:42 DomotiGaServer3.gambas
CSmartVISUServer.class:104:6: error: Unknown operator
-rwxr-xr-x 1 pi pi 2047254 Jun 5 20:42 DomotiGa3.gambas

BTW, concerning the sample every 10 seconds. This is not something I want but the device changes(updates) every 10 seconds so that's why I get heavy logs. The workaround with a virtual device solved this though.

RE: Smartvisu periode issue - possible solution - Added by Raymond about 3 years ago

Hi Thomas,

looking to a simulair issue i found this : https://domotiga.nl/issues/474 (search on 104:6:)
What is your version of domotiga and gambas?

back to the sample frequency, 3 or more time a sample in a minut is not a problem for the query.

RE: Smartvisu periode issue - possible solution - Added by thomas.hicks about 3 years ago

I found this as well : "I guess you downloaded the non-raw version, so it has html tags included." in https://domotiga.nl/boards/1/topics/6466

I'm running DomotiGa3 V1.0.023 and Gambas 3.8.4.

Is it possible there is another version (now-raw?) of the class file you'd be able to upload ?

RE: Smartvisu periode issue - possible solution - Added by lolouk44 about 3 years ago

Hi Raymond,

Thanks, I can now chart my consumption for more than the past 6 hours :)

Quick question though, I have an average set in my SmartVISU page:

{{ plot.period('plot5', ['currentcost_Plot1'], 'avg', '1w', '', 0, '', 10, ['TTL Consumption'], ['#ff0'], 'line', ['', 'Wh'], '1h') }}

Do you know how I can set it up so I don't have as many decimals when I check a value on the chart, see screenshot.

I've not yet tried the workaround to register equal numbers to a new datarecord, but it makes sense so I'll try this later today/this week

Thanks again

RE: Smartvisu periode issue - possible solution - Added by Raymond about 3 years ago

@Thomas, i'm not familiair with your issue, so what i would do, is open a Domotiga Server project in Gambas and paste the code and see if the code will run. The uploaded code is a plain text file with a extension "class"

In this case, i hope that Ron or Alexie can help?

myVersion: Domotiga 1.0.022 and Gamba 3.8.2; but i think that could not be the problem. Next weekend i will update my version.

@Laurent, I actually think that the rounding issue in Smartvisu should be regulated. But the easiest way is to arrange the rounding issue in mysql.

In the attached file is added the Round() function. The data rouding is to 1 decimal. Let me know of this is working.

regards
Raymond

RE: Smartvisu periode issue - possible solution - Added by lolouk44 about 3 years ago

Hi Raymond,

Thanks for this. Guess there's something else at play as the newer version you provided did not fix the issue with the decimals.

Thanks however for looking into this :)

RE: Smartvisu periode issue - possible solution - Added by Raymond about 3 years ago

Hi Laurent,

maybe a silly question, but have you compiled the code flushed
I ask this because your previous adjustment works.

RE: Smartvisu periode issue - possible solution - Added by lolouk44 about 3 years ago

Doh! Not a silly question, only a silly user :P

Thanks again it's perfect

RE: Smartvisu periode issue - possible solution - Added by lolouk44 over 2 years ago

Hi Raymond,

Sorry to revive an old thread, I've upgraded to Ubuntu 16.04 / PHP7.1 and since then the customized code doesn't seem to work and I get no plots anymore.
Have you upgraded too? Does it work for you?

Thanks
Laurent

RE: Smartvisu periode issue - possible solution - Added by Raymond over 2 years ago

Hi Laurent,

I haven't updated Domitiga since version 1.0.0.22. Maybe I should do that in a few weeks.

I asked to the administrator for 2 times, if they could validate my code and want to consider to including it in domotiga package. I didn't receive any response.

@Ron Klinken, did you received my request, to validate and implementate my code.

Regards Raymond

RE: Smartvisu periode issue - possible solution - Added by rdnzl over 2 years ago

@Raymond please update me, what excatly do I need to validate?
I was out of DomotiGa land for some time, need to pick up where i left...

RE: Smartvisu periode issue - possible solution - Added by Raymond about 2 years ago

Hi Ron,

To improve the graphics in Smart visu, i have changed the code in the module CSmartVISUServer.class.
One of the changes is grouping data in a timeframe (step). A another change is the use of SUM, AVG, MAX, MIN. This was also necessary to group the data.

I have added my last version, for Domotiga 1.0.022 and Gamba 3.8.2.
Please read this total story, validate my code and hopefully you will adapt the code in the source.
Let me know if you have questions or advice for improvements.

Regards Raymond

    (1-20/20)