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?
historical data from db
Moderator: leecollings
- waaren
- Posts: 6028
- Joined: Tuesday 03 January 2017 14:18
- Target OS: Linux
- Domoticz version: Beta
- Location: Netherlands
- Contact:
Re: historical data from db
[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
}
Debian buster, bullseye on RPI-4, Intel NUC.
dz Beta, Z-Wave, RFLink, RFXtrx433e, P1, Youless, Hue, Yeelight, Xiaomi, MQTT
==>> dzVents wiki
dz Beta, Z-Wave, RFLink, RFXtrx433e, P1, Youless, Hue, Yeelight, Xiaomi, MQTT
==>> dzVents wiki
-
- Posts: 7
- Joined: Monday 21 October 2024 14:33
- Target OS: NAS (Synology & others)
- Domoticz version:
- Contact:
Re: historical data from db
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
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
Who is online
Users browsing this forum: No registered users and 0 guests