Time series data access advice

Hi all,
I need some advice on how to use external sensor data for a calculation.

A bit of an architectural question.

I am retrieving hourly sensor data from a weather api (buienradar.nl) which tells me the rainfall in the previous 24 hours. I want to decide whether to water my lawn based on the total rainfall in the last three days, and the irrigation logic is running in appdaemon.

My question is for advice on storing the data, retrieving it, and making the calculation. Currently I am storing the hourly data in the HA SQLite database, which is fine. What I would need to do daily is to sum the inputs from three of the last sixty-two (or so) records, and use those to decide whether to water the lawn.

Before I start trying to figure out how to access the database from appdaemon, perform some logic on the time stamps, and make a decision, I’d love to get some inputs. Do you think putting them into the db is the best approach; would you use appdaemon to calculate (I also have MQTT running); and do you have any practical advice on how to most easily do this?

I saw an example using “shelve” in python, which makes sense to me. But, to be honest, in the last month I have taught myself C++ for the arduino boards, HA, yaml, python, linux and appdaemon (all completely new to me). The more I can limit the complexity until I have caught my breath, the better.

Cheers,
Sean

i guess you are on a point where learning new things is a bit much. and i can understand that. i had some moments like that also.

off course you could use shelve, or use a db, or read out the HA db.
all of them need you to learn new tricks.

i still use the easy way for stuff like that.
just save it in a file and calculate from there.

to give you the basics for that just here:

import datetime

def init():
  listen_state(self.cb,"sensor.that_has_the_rain")
def cb(...):
    sensorlog = open("/path/to/and_name.txt", 'a')
    runtime = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    sensorlog.write(runtime + ";" + str(new) + "\n")
    sensorlog.close()

thats the easy part.
now your sensorvalue gets logged in a file with the time.
now we make an app to get the values you need

import datetime

def init():
    checktime = datetime.time(0, 1, 0) # the time you need the value in H M S
    self.run_daily(self.cb, checktime)
def cb(...):
      sensorlog = open(self.args["/path/to/and_name.txt", 'r') # we read the whole file
      lines = sensorlog.readlines() # lines becomes a list with all lines
      sensorlog.close()  # we got the needed values so close the file  
      totalvalue = 0 
      maxlines = 62 # the amount we want to read
      linenr = 1 # the startline from the end
      do while linenr < maxlines: # so as long as the line is smaller then the amount we want to read go on
          lineparts = lines[:-linenr].split(";") # we look in the lines list for the linenr from the bottom and split it in 2 parts
          value = float(lineparts[1]) # the second part of the line is the value we need in text so make it a float
          totalvalue = totalvalue + value # we add it to total
          linenr = linenr + 1 # and we move to the next line
    averagevalue = totalvalue/maxlines # we got the values so why not use it
    self.set_state("sensor.total_rain", state=totalvalue) # creates and updates the sensor
    self.set_state("sensor.avarage_rain", state=averagevalue) # creates and updates another sensor

thats in basic what you need to create 2 new sensors and then you can use that how you like.

by the way i dont try to get all the stuff i use in my head.
i try to remember where i did use the code before and then i look at it and adapt.
most of this stuff is copied from my own apps and then changed for this situation :wink:

Usually when you talk about time series data influxdb or graphite comes to mind. But since you don’t have that many samples the HA sqlite will do fine (recorder component). If you want to keep things simple you could use the statistics sensor or the bayesian sensor to do the math for you without having to use appdaemon.

1 Like

Thanks, both of you. I’ll take a look at the suggestions when I get home.

1 Like