historical data from db

[chello]

Hi.
I need historical Counter entry (@ specific date - month ago from today) from Meter_Calendar table to calculate monthly energy usage from several counters and specify the percentage share of individual counters in total. How do this in dzVents?

[waaren]

Hi.
I need historical Counter entry (@ specific date - month ago from today) from Meter_Calendar table to calculate monthly energy usage from several counters and specify the percentage share of individual counters in total. How do this in dzVents?

[EDITED] to use from and to dates in json

There are no native commands in dzVents yet to access this data but you can use domoticz api/json to get this data in a Lua table
Just as an example on how to get historical data from domoticz

Code: Select all

-- getHistory 

return {
    on      =   {   timer           =   { "never" },                        -- Triggers the getJsonPart
                    devices         =   { "historyTrigger"},               -- virtual device to trigger the script during test / development
                    httpResponses   =   { "history"}                   -- Trigger the handle Json part
                },
                
    logging =   {   level     =   domoticz.LOG_DEBUG,
                    marker    =   "getHistory"    },

    data    =   {   lastTotal            = { initial = 0  }, 
                },
                    
        execute = function(dz, trigger)

        local function triggerJSON(idx,from,to)
            local  URLString   = dz.settings['Domoticz url'] .. 
                                 "/json.htm?type=graph&sensor=counter&idx=" .. idx ..
                                 "&range=" .. from .. "T" .. to
            dz.openURL({    url = URLString,
                            method = "GET",
                            callback = "history" })                      
        end
        
        local function getResponse()
            -- dz.log(trigger.json,dz.LOG_DEBUG)
            rt = trigger.json.result
            -- here you deal with the values in the rt (ReturnTable)
            -- do your calculations and store intermediate results dzVents persistent data for later use
            local total = 0 
            for record in pairs(rt) do
               dz.log("date: " .. rt[record].d .. " ==>> Value: " .. rt[record].v,dz.LOG_DEBUG)
                total = total +  rt[record].v
            end
            dz.log("********* Total: " ..  total, dz.LOG_DEBUG)
            dz.log("Previous total was : " .. dz.data.lastTotal,dz.LOG_DEBUG)
            dz.data.lastTotal = total    
        end    
        
        
        local function getDateRange(days)
            local dateFormat = "%Y-%m-%d"
            local t = os.date("*t", os.time())
            t.day = t.day - days 
            
            return os.date(dateFormat,os.time(t)), os.date(dateFormat)
        end
        
        if not (trigger.isHTTPResponse) then
            local myDevice = 133                                          -- device IDX where you want history from
            triggerJSON(myDevice,getDateRange(10))
          
        elseif trigger.ok then                                      -- statusCode == 2xx
            getResponse()
        else
            dz.log("getRepsonse() : Could not get data from domoticz" ,dz.LOG_ERROR)
        end
    end
}

[infoed]

Be aware that the content of the results of the json API calls may have changed!

I updated from DOM 2024.1 to 2024.7.

In some of the JSON API results with energy meters with more then one counter , in version 2024.1 there's the "v" and in 2024.7 changed in "v1".
E.g result for P1 smart meter with json.htm?type=graph&sensor=counter&range=year&idx=..
"c1": "26054.124",
"c2": "3975.648",
"c3": "26164.968",
"c4": "9030.318",
"d": "2023-10-23",
"p": "0.0000",
"r1": "0.000",
"r2": "13.237",
"v1": "3.454", <= CHANGED!
"v2": "9.088"

json.htm?type=graph&sensor=counter&range=2023-01-01T2023-01-02&idx..
"d": "2023-01-01",
"v1": "6.870", <= CHANGED!
"v2": "2.853"

E.g result of gasmeter, with one counter
c": "21358.800",
"d": "2024-02-24",
"p": "0.0000",
"v": "0.200" <=NO CHANGE