fix error when data is a mix of number and strings, convert all strings to numbers

changes in V 2022.16.26 @ 2023-02-08

1. optimized code, reduced cpu by ~ 90% most of the cpu was used by the sql query (80%) now only once an hour, in between subscription to dev / variable changes

2. some grammar fixes

1. removed debug logging
2. some grammar fixes
3. added std deviation simple besides std deviation time weighted

1. removed debug logging
2. some grammar fixes
3. added std deviation simple besides std deviation time weighted

stddev fix

1. added measurement "consumption" = end value of period - start value of period UpTime = % of time the value in the time window was not 0 Simple Average

2. added other time bins day time, night time , last 12 hours, previuos 12 hours

3. fixed calculation of std deviation, the simplified formula does not work for LARGE numbers, back to general formula : sqrt(sum((xi-av)**2))/N

4. added simple average to measures - before it was ONLY time weighted average as some of the data is not in regular time intervals and some values are longer valid that others. ie when a state is not upated on a fixed time slot basis eg a lamp is off for 11, on for 1, off for 12 hours simple average would give (0+1+0)/3 = 1/3 time weighted average would be (011 + 11 * 0*12 )/24 = 1/24 instead of 1/3 now you have both.

5. some print out improvements, shows definitions of time windows and measures

1. moved preSelectDevices() out of interactive menu part to avoid timeouts on slower MACs with larger number of devices of older indigo on new mac Mx
2. added short name to printout of tracked devices

fixed compiler error

1. requires indigo 2022 +, runs under py3
2. streamlined loggging
3. variable names are now deviceName__state__TimeWindow_Measurement eg: Hue_light_0_48_Küche_ceiling_1__redLevel__thisHour_Count1 added an extra _ after device name and stateName

1. made py3 compatible

added debug statements and clarified menues

1. fixed issue that variables could not be deselected
2. prept for python3
3. added color and other improvements to menus

fixed global name 'liteOrPsqlString' is not defined made password entry *

added option for postgres userid and password

fixed import of mylog

1. added: changed to std logging

2. fixed bug fillVariables: error in Line '835' ; error='need more than 0 values to unpack'

changes in V 7.10.10 @ 2018-12-11

1. added measures: First / last Entry Value and Date. These are the data from the first/last entry in the sql db in the time bin

changes in V 7.9.9 @ 2018-12-07

1. added measures: standard deviation "StdDev" Start Value "Start" = first value in time bin and "End" = Last Value all functions: "Min", "Max", "DateMin", "DateMax", "Ave", "Count", "Count1", "First"", "Last time bins: this & last: hour, day, week, month, and last7Days

2. improved menu error checking - in case not all fields are set

3. improved formatting of menus and printouts

4. automatic subscription to changes in devices and variables. Will switch on/off if not needed optimized checking if data updates are needed to reduce cpu load

5. added option to select / de-select all 90 possible measures in memu "define tracking ..."

6. force refresh all data action & menu items

1. added standard deviation measure ( devStd )

2. improved menu error checking

3. improved formatting of menus and printouts

4. added option to select / deselect all 72 possible measures in menu define tracking ...

1. major rewrite of internal data structure.

2. added format option for ave/min/max variables eg %.1f or %.3f ... For count it is integer and dates it is string

3. added free format option for date strings in eg ...DateMax %Y-%m-%d %H:%M:%S ==> 2018-02-24 15:13:40 %y-%b-%d %I%p %M:%S ==> 2018-Feb-24 3pm 13:40 set in config

4. added count if value > 0 measure

5. average is now a proper time weighted average, before it was just sum(values)/count. if eg temperature is 0 for 1 hour and 10 for 9 hours simple average would give 5, time weighted average = 9 ==> sqllogger used to get the data only stores the changes, hence time weighted average is more proper in most cases the difference is much smaller

6. now has the following measures: BINS: "thisHour","lastHour","thisDay","lastDay","thisWeek","lastWeek","thisMonth","lastMonth","last7Days" and for each of the above the following functions: "Min","Max","DateMin = Date of Min","DateMax = Date of Max","Ave","Count","Count1= Count when value>0 - use for eg events = on/off types"

7. option to choose short name for variables - set in define tarcking of devices menu instead of s-2-TH-Leo-Outside_Temperature_thisWeek_Max eg set short name to L-O-Temp results in variable name: L-O-Temp_thisWeek_Max

8. in menu add device/state now shows the previously selected devices first

9. added time CPU tracking option (for debugging and experts), set in config

1. added warning to selection menu, if no measure (ave min/max) was selected that no variable will be created. also same info is printed to logfile "--- no measure selected, no variable will be created---"
2. previously defined / tracked devices are marked in the selection box for new devices to be tracked
3. bugfix in logfile for error messages

fixed error: File "plugin.py", line 154, in validatePrefsConfigUi

added: 1. last 7 days measurement 2. separate remove menu from add menu 3. add expanded logging options 4. improved menus to indicate you need to select measurements and click on confirm

base release, docs see indigo site http://forums.indigodomo.com/viewtopic.php?f=188&t=15241

Indigo 6