Calculating days/hours per year below a certain temperature?

[mabe (Matthias)]

Hi,
in a few years (hopefully not too early) I will have to replace my heating system. For that reason I would like to collect real-life data to be able to reasonably dimension the next heating system.

One parameter which appears to be interesting to me is the number of hours/days per year where the outside temperature is below a certain value (e.g. -5°C). Perhaps including the longest period thereof.
Result should be something like:
2025: 300h , longest period: 62h
2026: 321h, longest period: 18h
…
perhaps not starting in January but better in October.
I have so far only little experience with automations. Mostly only rather simple if/then/else… automations.
I have a temperature sensor which provides the outside temperature every 5 min (roughly).

Could this be done in HA? If yes, could anyone please provide me with a hint, how to start? Or should it better be done “outside” HA? The temperature values, for the moment for historic reasons) are collected via MQTT and also sent to InfluxDB (which I only used to present the data to Grafana. I have absolutely no InfluxDB expertise.)
Thanks,
Matthias

P.S. I’m was not sure where to put this question. Since it is loosely energy related, I put it here and not under “configuration”.

[Edwin_D]

While it might be feasible, it is hard. There are other ways though, like the degree days integration, which tries to calculate what you hope to achieve:

GitHub - Ernst79/degree-days: Integration that determines (weighted) degree...

This one might be geared towards the Netherlands, but there are others like it. See also for instance this thread:

Yet Another Heating Degree Days Implementation

This bit: (as_timestamp(trigger.to_state.last_changed) - as_timestamp(trigger.from_state.last_changed) Might require you to use a triggered template sensor, which I previously would not have recommended but as of 2022.5 they are restored after a restart/reload so I have no issues with them now.

[Biscuit (Geoff)]

Easy enough and takes about 10 minutes* to do, and with very little effort, but only if you know how.

Here is ‘how’:

Home Assistant keeps all entity state for 10 days. It also keeps short-term and long-term statistics, short for 10 days but long-term forever.

The long-term stats are built at the end of every hour, as a snapshot of the entity state over the past hour. This only happens for entities with a defined measurement class, but ‘temperature’ is included. My indoor ‘kitchen temperature’ sensor for last year…

For temperature, the hourly figures kept are the mean, as well as min and max, so we can pull the entire one-year data set for the past year. Since there is only one record per hour, 370 days worth is 8,880 records.

Step 1:
Add SQLite Web add-on to your Home Assistant (and set it up)

Step 2:
Open SQLite Web, and go to the statistics_meta table and contents. This is a mapping between the entities stored, and the statistics table id. Look for your sensor.temperature_whatever and find the metadata_id. Make a note of this.

Step 3:
Go to the statistics table. Select ‘Query’ and enter something like the following SQL

SELECT mean, min, max, created_ts, date(created_ts, "unixepoch") AS udate, time(created_ts, "unixepoch") AS utime
FROM "statistics"
WHERE metadata_id = 305 AND unixepoch()-created_ts < 86400 * 370
ORDER BY udate, id, utime

Replace ‘305’ with your metadata_id found from above, and execute.

This will give you the hourly records (mean, min, max) for your temperature sensor for the past year (or for as much as you have in history…)

Step 4:
Process the data.
You could try improving the SQL using select where min < -5 and using group-by and sum etc to return just one line, and then this SQL could be used in the HA SQL integration to create a new sensor of your choice. Note that you will need at least two sensors, one for the total time, and another for the longest period. The total time should be ‘easy’ in SQL, the longest period a bit more tricky (since we need to test for concurrency) and since I have very little experience in SQL, I use JSONata to do the work for me.

Use Export CSV if you want to use a spreadsheet to analyse,
Use Export JSON otherwise.

Step 5 (optional):
Export the SQL query table to JSON, and copy-paste into JSON exerciser at https://try.jsonata.org/. Remove the left-hand window input entirely and paste the JSON as extracted. Paste the following JSONata code into the top right hand window . You will need to change the sample test temperature first to something like -5.

(
    $test:=17; /* test temperature */

    /* sample anything less than test temperature, and add 'one hour earlier/later' time rounded*/ 
    $samp:=$[min<$test]~>|$|{"this_hour": $floor(created_ts/10)*10, "last_hour": $floor(created_ts/10)*10-3600, "next_hour": $floor(created_ts/10)*10+3600}|;
    $dayarray:=$distinct($samp.udate); /* array of specific days on which temp less than test */

    /* reduction functions for adding last and next flags */
    $clast:=function($acc, $val){$append($acc, $merge([$val, {"last": $acc[-1].this_hour = $val.last_hour} ] ) )};
    $cnext:=function($acc, $val){$append($acc, $merge([$val, {"next": $acc[-1].this_hour = $val.next_hour} ] ) )};
    /* go forwards to check last record, then backwards to check next record */
    $temp:=$reduce($samp[[1..$count($samp)]], $clast, $merge([$samp[0],{"last": false}]))^(>this_hour);
    $temp:=$reduce($temp[[1..$count($temp)]], $cnext, $merge([$temp[0],{"next": false}]))^(this_hour);

    /* select only start and ends of contiguous groups */
    $periods:=$temp.(
        ($.last or $.next) and $not($.last and $.next) ?  $.{"place": $.next ? "start":"end", "time": this_hour, "temp": min}
        );

    /* find longest consecutive hour period */
    $longesthours:= $periods#$i.($.place="end" ? (time-$periods[$i-1].time)/3600)~>$max();

    {"days": $floor($count($)/24),
     "start": $[0].udate & " " & $[0].utime,
     "endat": $[-1].udate & " " & $[-1].utime,
     "minimum": $min($.min),
     "maximum": $max($.max),
     "test_min": $test,
     "total-hours-below": $count($samp),   
     "days-where-below": $count($dayarray),
     "longest-hours-below": $longesthours
    };

)

You will get the ‘result’ in the lower right hand window.

In my case, my kitchen temperature (the hourly minimum) was below 17 degrees during 62 individual hour-periods over the past year, at least once over each of 22 different days, with a longest concurrent time period of 5 hours.

JSONata expressions can be processed in Node-RED, and it is possible to have Node-RED flows that perform SQL reads on the HA statsitics table, perform JSONata data processing, and to then return one or more values, including arrays, in HA sensor state & attributes.

No need to create automations, additional sensors, or use external databases since the data is already there in Home Assistant, just waiting to be explored.

* once setup

[chairstacker]

I use the following approach to count the hours my furnace/AC fan is running in order to replace the filters based on running time of the fan rather than just calendar days - might be adaptable to what you’re trying to do:

1. I have a history_stats sensor that counts when the fan is running (for you, it could be for the time the temperature is below -5)
2. It resets every day, so I can see the individual/daily history by using the (old?) terminology of
   start: “{{ now().replace(hour=0, minute=0, second=0, microsecond=0) }}”
   and
   end: ‘{{ now() }}’
3. There’s a second sensor which keeps a running total.
4. Easy to look at and export from the History view.

As long as you classify them correctly, they should keep their stats in the long term history.

P.S.:
I also export the daily values into a csv-file which I use to analyze the data - as well as the output of many other sensors - in MS Power BI.

P.P.S.:
This is, of course, only if you don’t want to follow the excellent suggestion that @Biscuit has provided - which also has the benefit of providing you historic results right away

[mabe (Matthias)]

Wow!
Thanks all three of you.
@Biscuit: “easy enough” - perhaps for you. I guess I have to learn quite a lot. Your solution is much more than I expected. I will take it easy and do it step by step.
And I’m really impressed what can be found deep inside HA.
@chairstacker: sounds also interesting and perhaps easier to do at the beginning.

[Biscuit (Geoff)]

@mabe

“easy enough” - perhaps for you.

I have been working with Home Assistant for three years now. At first the “learning curve” was more of a sheer vertical rock face with an overhang. I continue to learn stuff every week.

The ‘long-term’ statistics table was a closed door until I took a deep breath and dived in, and I still don’t understand SQL (just enough to get by). JSONata, I grant you, has been about two years of advanced and protracted study, and I now write JSONata code almost every day as I do everything in it and still continue to learn.

I don’t recommend you use JSONata, but the idea I would like to put forward is that the data you want is possibly already there. The hardest part in your question I think is to working out the concurrent periods in the records, which is tricky in any language and, I submit, a challenge for anyone. That said, thanks to your question and the large array it generated, I was prompted to re-write my ‘sequence concurrency’ code to improve the efficiency, and although it took me a few hours today, I cracked it eventually.

Good luck with your project.

[Biscuit (Geoff)]

There is always room: at the top, and for improvement.

In case anyone reads this later…

Better SQL query to match a given sensor directly, and to round the unix-seconds, improved JSONata to return details of the days when temperature is below target.

Can be done in one go inside Node-RED, so here is a NR flow to do just that, using the SQLite node configured for the standard Home Assistant database. [Uses a prepared statement and in read-only mode]

With Node-RED it would be easy to trigger the flow as required, use variable parameters, and to take any part of the return and send this back to Home Assistant in a sensor node.

[{"id":"5b8bd7cbe05f7465","type":"inject","z":"bf1c409ead401b04","name":"Get Long Term stats","props":[{"p":"params","v":"{\"$entity\":\"sensor.bedroom_aircon_room_temperature\",\"$date1\":\"2023-10-01\",\"$date2\":\"2024-10-01\"}","vt":"json"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","x":170,"y":440,"wires":[["b2cbfb61ec7195b9"]]},{"id":"b2cbfb61ec7195b9","type":"sqlite","z":"bf1c409ead401b04","mydb":"56ce7a19f8e148e7","sqlquery":"prepared","sql":"SELECT mean, min, max, FLOOR(created_ts/100)*100 AS unixsec, date(created_ts, \"unixepoch\") AS udate, time(created_ts, \"unixepoch\") AS utime\nFROM \"statistics\"\nWHERE metadata_id = (SELECT id FROM \"statistics_meta\" \n    WHERE statistic_id = $entity)\nAND udate>=$date1 AND udate<$date2","name":"Long term stats array","x":180,"y":500,"wires":[["53c9e133662f71f6"]]},{"id":"53c9e133662f71f6","type":"change","z":"bf1c409ead401b04","name":"Calculate when below given temperature","rules":[{"t":"set","p":"temperature","pt":"msg","to":"17","tot":"num"},{"t":"set","p":"topic","pt":"msg","to":"params.$entity","tot":"msg"},{"t":"set","p":"payload","pt":"msg","to":"(\t    $test:=temperature; /* test temperature */\t    $array:=payload;\t\t    /* sample anything less than test temperature, and add 'one hour earlier-later' time rounded*/ \t    $samp:=$array[min<$test]~>|$|{\"this_hour\": unixsec, \"last_hour\": unixsec-3600, \"next_hour\": unixsec+3600}|;\t    $dayarray:=$distinct($samp.udate); /* array of specific days on which temp less than test */\t\t    /* reduction functions for adding last and next flags */\t    $clast:=function($acc, $val){$append($acc, $merge([$val, {\"last\": $acc[-1].this_hour = $val.last_hour} ] ) )};\t    $cnext:=function($acc, $val){$append($acc, $merge([$val, {\"next\": $acc[-1].this_hour = $val.next_hour} ] ) )};\t    /* go forwards to check last record, then backwards to check next record */\t    $temp:=$reduce($samp[[1..$count($samp)]], $clast, $merge([$samp[0],{\"last\": false}]))^(>this_hour);\t    $temp:=$reduce($temp[[1..$count($temp)]], $cnext, $merge([$temp[0],{\"next\": false}]))^(this_hour);\t\t    /* select only start and ends of contiguous groups */\t    $periods:=$temp.(\t        ($.last or $.next) and $not($.last and $.next) ?  $.{\"place\": $.next ? \"start\":\"end\", \"time\": this_hour, \"temp\": min}\t        );\t\t    /* find longest consecutive hour period */\t    $longesthours:= $periods#$i.($.place=\"end\" ? (time-$periods[$i-1].time)/3600)~>$max();\t\t    /* array of cold days - the hour and temperature */\t    $cold_days:= $dayarray.(\t        $day:=$;\t        $data:=$samp[udate=$day];\t        $recs:=($data.{\"hour \" & $substringBefore(utime, \":\") : min})~>$merge();\t        {\"date\": $day, \"hours\": $count($keys($recs)), \"records\": $recs}\t\t    );\t\t    {\"days\": $count($array)/24,\t     \"start\": $array[0].udate & \" \" & $array[0].utime,\t     \"endat\": $array[-1].udate & \" \" & $array[-1].utime,\t     \"minimum\": $min($array.min),\t     \"maximum\": $max($array.max),\t     \"test_min\": $test,\t     \"total-hours-below\": $count($samp),   \t     \"days-where-below\": $count($dayarray),\t     \"most-hours-sequence\": $longesthours +1,  /* period is lasthour - firsthour, so add one to include first-hour period */\t     \"cold-day-array\": $cold_days     \t    };\t\t)","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":480,"y":500,"wires":[["bec78f9ab9cd88fd"]]},{"id":"bec78f9ab9cd88fd","type":"debug","z":"bf1c409ead401b04","name":"debug 395","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":750,"y":500,"wires":[]},{"id":"56ce7a19f8e148e7","type":"sqlitedb","db":"/homeassistant/home-assistant_v2.db","mode":"RO"}]