rain0total incorect on NanoSD **solved**

All about the standard Meteobridge devices based on mobile routers from TP-Link, D-Link, ASUS

Moderator: Mattk

Post Reply
bardek
Senior Boarder
Senior Boarder
Posts: 40
Joined: Fri Jan 16, 2015 7:49 pm
Contact:

rain0total incorect on NanoSD **solved**

Post by bardek » Sun Jan 13, 2019 12:08 pm

rain0total-sum5.2:NULL does not return correctly. On NanoSD this selector does not return correct value.
Here is the use case:
1. MySql insert every 5 min on full minute
2. randomly value is inccorect (returns "0")

ie. dbase.ram shows:

Code: Select all

sqlite> select ts, datetime(ts, 'unixepoch'), sum(val) from rain0total_min where ts > strftime('%s','2019-01-13 03:30:00') and ts < strftime('%s','2019-01-13 03:35:00');
1547350440|2019-01-13 03:34:00|200

Code: Select all

select ts, datetime(ts, 'unixepoch'), val,ts, minval,datetime(mints, 'unixepoch'),maxval,datetime(mints, 'unixepoch') from rain0total_min where ts > strftime('%s','2019-01-13 03:30:00') and ts < strftime('%s','2019-01-13 03:35:00');
1547350260|2019-01-13 03:31:00|0|1547350260|0|2019-01-13 03:31:50|0|2019-01-13 03:31:50
1547350320|2019-01-13 03:32:00|0|1547350320|0|2019-01-13 03:32:46|0|2019-01-13 03:32:46
1547350380|2019-01-13 03:33:00|0|1547350380|0|2019-01-13 03:33:42|0|2019-01-13 03:33:42
1547350440|2019-01-13 03:34:00|200|1547350440|200|2019-01-13 03:34:10|200|2019-01-13 03:34:10

but MySql insert run at 2019-01-13 03:35:00 rain0total-sum5.2:NULL returned "0"
For me it looks like this selector does not sum up last minute.
Same setup on Meteobridge TL-MR3020 returns correctly.
How to fix this issue

User avatar
admin
Platinum Boarder
Platinum Boarder
Posts: 5788
Joined: Mon Oct 01, 2007 10:51 pm

Re: rain0total incorect on NanoSD

Post by admin » Sun Jan 13, 2019 3:13 pm

Sorry this is bad algorithmic design on your end.

Trying to get every 5 mins the rainfall of the last 5 mins will never work correctly. You will always have quirks caused by timing etc.

Proper design is to read the rainfall of all time and to subtract rainfall you have read in the previous request.

bardek
Senior Boarder
Senior Boarder
Posts: 40
Joined: Fri Jan 16, 2015 7:49 pm
Contact:

Re: rain0total incorect on NanoSD **solved**

Post by bardek » Sun Jan 13, 2019 3:33 pm

ok, I'll change approach then. Thank you

bardek
Senior Boarder
Senior Boarder
Posts: 40
Joined: Fri Jan 16, 2015 7:49 pm
Contact:

Re: rain0total incorect on NanoSD

Post by bardek » Sun Jan 13, 2019 6:18 pm

admin wrote:
Sun Jan 13, 2019 3:13 pm
Sorry this is bad algorithmic design on your end.

Trying to get every 5 mins the rainfall of the last 5 mins will never work correctly. You will always have quirks caused by timing etc.

Proper design is to read the rainfall of all time and to subtract rainfall you have read in the previous request.
would you point me how to use template to get this sum of rain of last 5 min in MySql insert.

other thing is why services scripts, mysql etc are run before local sqlite update? I'm guessing that you do some inserts on full minute to local db and there is some run racing.

User avatar
admin
Platinum Boarder
Platinum Boarder
Posts: 5788
Joined: Mon Oct 01, 2007 10:51 pm

Re: rain0total incorect on NanoSD **solved**

Post by admin » Sun Jan 13, 2019 10:56 pm

1) just log the total rain in your database and act accordingly when retrieving data from your database would be one option.

2) Or you do some more advanced SQL inserts. A bit of google well tell you how.

bardek
Senior Boarder
Senior Boarder
Posts: 40
Joined: Fri Jan 16, 2015 7:49 pm
Contact:

Re: rain0total incorect on NanoSD **solved**

Post by bardek » Sun Jan 13, 2019 11:50 pm

first option is probably best solution. I'll go for it or I'll will export minutes reading data from Nano every hour and correct it by running some script.

"Advanced SQL inserts"? my SQL is already too complex, its hardly fits in 4000 chars length limit of form input, don't want to make it more complicated.

There are any more template sensors/selectors affected or its only rain sum?

If only Nano store readings by minute interval tables I would use Nano db as archive.

Post Reply