SQLite3

[koensch]

domoticz saves all data in a SQLite3 database.

As someone with a background in SQL development and dataanalytics i could not resist the temptation to do some queries now i have more than a year of available data.
Also i wanted to see if i could use "Common Expression Tables" in SqLite3.

Using a P1 interface my energy consumption is registed. Via a OTGW the behavious of the CentalHeating boiler is monitored.

in our home, we use gas for:
- heating (this is the bulk of my gas usage)
- cooking
- hotwater

i was curious to learn how much gas we use for Non-Heating purpose. I tried this by averaging the gas consumption over the 'Non-Heatingdays' (heater was not used)

Code: Select all

-- Sensor "Control Setpoint" (idx=17) the 'Temp_Max' value indicates that the heater was
-- used during the Day. When heater not used it is 10, if the heater was used, it's >10.
WITH
  CTE_HeatingDays AS (
    SELECT Date FROM Temperature_Calendar WHERE (DeviceRowID=17 AND Temp_Max>10)
), CTE_NonHeatingDays AS (
    SELECT Date FROM Temperature_Calendar WHERE (DeviceRowID=17 AND NOT(Temp_Max>10))
), CTE_GasUsed AS (
-- Gas meter is idx=46, Usage is in m3/1000
    SELECT Date, Value FROM Meter_Calendar WHERE (DeviceRowID=46)
), CTE_GasUsedNonHeatingDays AS (
-- Join the non-heatingdays with the gas usage
    SELECT A.Date, B.Value FROM CTE_NonHeatingDays AS A
      INNER JOIN CTE_GasUsed AS B ON A.Date=B.Date
), CTE_GasUsedHeatingDays AS (
-- Join the heatingdays with the gas usage
    SELECT A.Date, B.Value FROM CTE_HeatingDays AS A
      INNER JOIN CTE_GasUsed AS B ON A.Date=B.Date
)
--SELECT COUNT(*) AS NonHeatingDays, SUM(Value)/COUNT(*) AS "GasUsed/1000" FROM CTE_GasUsedNonHeatingDays; 
SELECT COUNT(*) AS HeatingDays, SUM(Value)/COUNT(*) AS "GasUsed/1000" FROM CTE_GasUsedHeatingDays; 

my result came back with:

Code: Select all

sqlite> .read cte_example.sql
NonHeatingDays|GasUsed/1000
343|299
sqlite>

so now i know that you can use CTE scripting and that in our household we use 0,299m3/day for non-heating applications on average. I will do some reseach on how dataanalysis can be used within Domoticz.

My conclusion is that we use 109m3/year for non heating application (0,299 *365)

If i change the SQL (uncomment/comment the other SELECT) i get:

Code: Select all

sqlite> .read cte_example.sql
HeatingDays|GasUsed/1000
240|2525
sqlite>

i get 240 heating days with an average usage of 2.525m3 per day (including the 0,299 for non-heating application).

realise my data set is 583 days (343+240) long and this is not something that predicts my total year consumption. But that was not the purpose of this experiment.

[PierreT]

Nice work, but couldn't you just subtract the extrapolated value from the non heating days and divide the remainder through the number of heating days to find the gas usage for heating only?

I actually aimed to do something similar as well, but I'm on city heating and the meter runs on a battery that is supposed to last ten years. The meter does have some kind of optical port but I've been informed that accessing it will take at least a day off the battery life and it seemed like a bad idea to try to collect it in Domoticz because it shows sensor error if you only put data in it once a day or even less and well, the people running the system are not the friendliest bunch so I let it go.

I did do some manual readings though and found that I use around 0.5GJ on average per month during non heating periods. Assuming a 90% efficiency for domestic hot water this is the equivalent of 0,53m3 per day which is pretty shocking viewing your numbers (that include cooking as well). Luckily I do better on heating since this usage totals to a third of the overall city heating consumption.