Statistics on persistent data [Solved]

[kubrik]

Hi,
i need help with statistical functions on persistent variables.

I've persistent data like this where i store historical temp and humidity for room/zone.

Code: Select all

local obj1 = {
	["Temp"] = {
		[1] = {
			["time"] = "2020-11-18 22:50:0.462";
			["data"] = {
				["hum"] = 73.1;
				["zona"] = "Room1";
				["temp"] = 20.8;
			};
		};
		[2] = {
			["time"] = "2020-11-18 22:50:0.462";
			["data"] = {
				["hum"] = 70.0;
				["zona"] = "Room2";
				["temp"] = 21.3;
			};
		};
		[3] = {
			["time"] = "2020-11-18 22:40:0.462";
			["data"] = {
				["hum"] = 75.0;
				["zona"] = "Room1";
				["temp"] = 19.7;
			};
		};
		[4] = {
			["time"] = "2020-11-18 22:40:0.462";
			["data"] = {
				["hum"] = 60.0;
				["zona"] = "Room2";
				["temp"] = 22.5;
			};
		};
		[5] = {
			["time"] = "2020-11-18 22:30:0.462";
			["data"] = {
				["hum"] = 60.0;
				["zona"] = "Room1";
				["temp"] = 22.5;
			};
		};
		[6] = {
			["time"] = "2020-11-18 22:30:0.462";
			["data"] = {
				["hum"] = 75.0;
				["zona"] = "Room2";
				["temp"] = 19.7;
			};
		};
....
....

How can i get, for example, the AVG "temp" for the "zona" Room1 using the statistical functions avg, avgSince..etc?

Thanks in advance!

[waaren]

i need help with statistical functions on persistent variables.
How can i get, for example, the AVG "temp" for the "zona" Room1 using the statistical functions avg, avgSince..etc?

You stored the historical data of multiple sensors in one table. The standard statistical dzVents functions cannot deal with that. You need to define the historical data per sensor or build your own statistical functions.

example script

Code: Select all

return
{
    on =
    {
        devices =
        {
            3, 19, 471, -- temperature sensors 
        },
    },

    logging =
    {
        level = domoticz.LOG_DEBUG,
        marker = 'historical data',
    },

    data =
    {
        t3 = { history = true, maxMinutes = 60 },
        t19 = { history = true, maxMinutes = 60 },
        t471 = { history = true, maxMinutes = 60 },
    },

    execute = function(dz, item)

        local idx = item.idx

        -- add new data
        dz.data['t' .. idx].add(item.temperature)
       
        ----------------------------------------------------------------------------------------------------------
        -- Check historical storage entries
        ----------------------------------------------------------------------------------------------------------
        if dz.data['t' .. idx].size < 3 then
            dz.log('Not enough data points yet to produce something useful', dz.LOG_DEBUG)
            dz.log(dz.data['t' .. idx].size .. ' entries for ' .. item.name .. ' in History', dz.LOG_DEBUG)
            return
        end

        ----------------------------------------------------------------------------------------------------------
        -- Calculate
        ----------------------------------------------------------------------------------------------------------
        -- average temperature in last 3 minutes
        dz.log('Average temperature of ' .. item.name .. ' in last 3 minutes ' .. dz.utils.round(dz.data['t' .. idx].avgSince('00:03:00'),2), dz.LOG_DEBUG)

        minutesStored = dz.data['t' .. idx].getOldest().time.minutesAgo
        dz.log('Average temperature of ' .. item.name .. ' of all entries in history (' .. minutesStored ..  ' minutes) ' ..
                dz.utils.round(dz.data['t' .. idx].avg(),2), dz.LOG_DEBUG)

    end
}

loglines from the example script

Code: Select all

2020-11-19 01:17:40.393  Status: dzVents: Info: historical data: ------ Start external script: history.lua: Device: "Internal Temperature (Motherboard)", Index: 3
2020-11-19 01:17:40.408  Status: dzVents: Debug: historical data: Average temperature of Internal Temperature in last 3 minutes 49.33
2020-11-19 01:17:40.408  Status: dzVents: Debug: historical data: Average temperature of Internal Temperature of all entries in history (17 minutes) 49.08
2020-11-19 01:17:40.413  Status: dzVents: Info: historical data: ------ Finished history.lua

content of datafile of example script

Spoiler: show

Code: Select all

-- Persistent Data
local multiRefObjects = {

} -- multiRefObjects
local obj1 = {
        ["t3"] = {
                [1] = {
                        ["data"] = 50;
                        ["time"] = "2020-11-19 0:17:40.245";
                };
                [2] = {
                        ["data"] = 49;
                        ["time"] = "2020-11-19 0:16:30.302";
                };
                [3] = {
                        ["data"] = 49;
                        ["time"] = "2020-11-19 0:15:20.267";
                };
                [4] = {
                        ["data"] = 49;
                        ["time"] = "2020-11-19 0:14:10.143";
                };
                [5] = {
                        ["data"] = 49;
                        ["time"] = "2020-11-19 0:13:0.233";
                };
                [6] = {
                        ["data"] = 49;
                        ["time"] = "2020-11-19 0:11:50.92";
                };
                [7] = {
                        ["data"] = 49;
                        ["time"] = "2020-11-19 0:10:40.128";
                };
                [8] = {
                        ["data"] = 49;
                        ["time"] = "2020-11-19 0:9:30.99";
                };
                [9] = {
                        ["data"] = 49;
                        ["time"] = "2020-11-19 0:8:20.46";
                };
                [10] = {
                        ["data"] = 49;
                        ["time"] = "2020-11-19 0:7:10.14";
                };
                [11] = {
                        ["data"] = 49;
                        ["time"] = "2020-11-19 0:6:0.982";
                };
                [12] = {
                        ["data"] = 49;
                        ["time"] = "2020-11-19 0:1:19.837";
                };
                [13] = {
                        ["data"] = 49;
                        ["time"] = "2020-11-19 0:0:9.803";
                };
        };
        ["t19"] = {
                [1] = {
                        ["data"] = 9.1000003814697;
                        ["time"] = "2020-11-19 0:13:52.817";
                };
                [2] = {
                        ["data"] = 9.3000001907349;
                        ["time"] = "2020-11-19 0:3:52.300";
                };
        };
        ["t471"] = {
                [1] = {
                        ["data"] = 6.6999998092651;
                        ["time"] = "2020-11-19 0:18:7.185";
                };
                [2] = {
                        ["data"] = 6.6999998092651;
                        ["time"] = "2020-11-19 0:13:6.263";
                };
                [3] = {
                        ["data"] = 6.6999998092651;
                        ["time"] = "2020-11-19 0:8:5.526";
                };
                [4] = {
                        ["data"] = 6.6999998092651;
                        ["time"] = "2020-11-19 0:3:4.623";
                };
        };
}
return obj1

[kubrik]

You stored the historical data of multiple sensors in one table. The standard statistical dzVents functions cannot deal with that. You need to define the historical data per sensor.

Ok, thanks waaren.

example script

Code: Select all

    data =
    {
        t3 = { history = true, maxMinutes = 60 },
        t19 = { history = true, maxMinutes = 60 },
        t471 = { history = true, maxMinutes = 60 },
    },

And can i assign dynamic "t3", "t19", "t471" instead of static ones like in the example?
I have an init json filled with, for example, room name with its temp/hum sensor and other parameters.
I can add or remove elements from it.

So the data would be:

Code: Select all

nameOfRoom1_Temp = { history = true, maxMinutes = 60 },
nameOfRoom1_Hum = { history = true, maxMinutes = 60 },
nameOfRoom2_Temp = { history = true, maxMinutes = 60 },
nameOfRoom2_Hum = { history = true, maxMinutes = 60 },
nameOfRoom3_Temp= { history = true, maxMinutes = 60 },
nameOfRoom3_Hum = { history = true, maxMinutes = 60 },
and so on.

where nameOfRoom is read from this json/array/variable...dynamic.

[waaren]

And can i assign dynamic "t3", "t19", "t471" instead of static ones like in the example?
I have an init json filled with, for example, room name with its temp/hum sensor and other parameters.

No you cannot assign dynamic sensor names in historic persistent data like that. You will have to use a table and because of that you will loose the build-in statistical functions. Luckily you can define them yourself. See how in below example.

Code: Select all

return
{
    on =
    {
        devices =
        {
            3, 19, 471,10,456 -- temperature sensors
        },
    },

    logging =
    {
        level = domoticz.LOG_DEBUG,
        marker = 'historical data',
    },

    data =
    {
        temperatures = { history = true, maxMinutes = 60 },
    },

    execute = function(dz, item)

        local function buildHistoryRecord()
            local t = {}
            t.name = item.name
            t.temperature = item.temperature
            return t
        end

        -- homemade statistical function
        local function avgSince(sensor, since) 
        
            local since = since or  '9999:00:00' -- 9999 hours if not provided
            local sensor = sensor or item -- defaults to item
            
            local timeSlicedHistory = dz.data.temperatures.subsetSince(since)
            local sumTemperature, recCounter = 0, 0
            timeSlicedHistory.forEach(
                function(record)
                    if type(record) == 'table' and record.data.name == sensor.name then
                        recCounter = recCounter + 1
                        sumTemperature = sumTemperature + record.data.temperature
                    end
                end)

            if recCounter == 0 then return -999 end -- prevent -nan return when 0 matching records
            return dz.utils.round( sumTemperature / recCounter , 2)
        end

        -- Add temperature to persistent historical data
        dz.data.temperatures.add(buildHistoryRecord())

        -- Check number of historical storage entries
        dz.log(dz.data.temperatures.size .. ' entries in History', dz.LOG_DEBUG)

        -- Check age of oldest entry
        local minutesStored = dz.data.temperatures.getOldest().time.minutesAgo
        dz.log(minutesStored ..' minutes worth of data in history.' , dz.LOG_DEBUG)

        -- get avgSince of current item
        dz.log('Average temperature of ' .. item.name .. ' in last 12 minutes is ' .. avgSince(item, '00:12:00') .. ' °C')
        
        -- get overal average of current item
        dz.log('Average temperature of ' .. item.name .. ' is ' .. avgSince() .. ' °C')
    end
}

[kubrik]

Nice and clean example!
Thank you waaren