rain0total incorect on NanoSD **solved**

[bardek]

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

[admin]

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]

ok, I'll change approach then. Thank you

[bardek]

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.

[admin]

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]

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.