How to read database

---

Hello,

i want to read older entries from the database. For example the value of the rain gauge from a year ago.
Is that even possible in Pimatic?

Thanks
Jens

Good morning Jens,

yes that is possible, click on the desired sensor and open up the ‘Graph’ .
In the next screen you’ll be able to select an end date for your graph.

Here you can also change the time-range of the graph from 24 Hours to a week or even a month…

edit) Typo

Thanks for the quick answer. At this point i know that it works. But i need this in the expression to calculate with this.

Not quite sure what you mean by that…

To my understanding variables are stored in the DB as well, so you should be able to graph them.
As an alternative you could ‘view’ the database stand-alone with any sqlite viewer… If you know some basic SQL you could manipulate the values yourselve.

I want to show the amount of rain for the last 24 hours or the last quarter of a year. So the current value minus the value 24 hours ago.

Hi, what settings are you using in config.json for “database.deviceAttributeLogging”?
If you didn’t change anything the default expired time for device attributes is 7 days.
Only temperature and humidity attributes are default stored for a 1 year.

"database": {
  "deviceAttributeLogging": [
    {
      "deviceId": "*",
      "attributeName": "*",
      "type": "*",
      "interval": "0",
      "expire": "7d"
    },
    {
      "deviceId": "*",
      "attributeName": "*",
      "type": "continuous",
      "interval": "5min",
      "expire": "7d"
    },
    {
      "deviceId": "*",
      "attributeName": "temperature",
      "type": "number",
      "expire": "1y"
    },
    {
      "deviceId": "*",
      "attributeName": "humidity",
      "type": "number",
      "expire": "1y"
    }
  ],
  "messageLogging": [
    {
      "level": "*",
      "tags": [],
      "expire": "7d"
    },
    {
      "level": "debug",
      "tags": [],
      "expire": "0"
    }
  ]
},

This are the original settings. 7 days are currently set for rain. Of course I have to increase that.

Yes, that will help in the future.
You could also create a variable for the value of the ‘last 24 hours’ and keep that with a rule updated.
The calculation in the rule is depending on

1. if you want to calculated the last 24 hour as ‘yesterday 00:00-24:00’ or the real last 24 hours (a sliding window).
2. the meaning of the value of the rain gauge (cumulative rain (mm), actual rain (mm/min), etc)

Thanks for your answer.

Yes, i want to display exactly the last 24 hours. With the help of variables, which save a respective start value, I am currently working with the help of rules. But I can’t save a value in a variable every 5 minutes or something like that.
The rain gauge provides a value in mm. This counts up endlessly.

Ok, for the last 24 hour variable you could use a plugin i made.
It’s called pimatic-tools and if you create a VariableDelayDevice, you could create variable of which the variable value is sampled and delayed by a certain time. The delayed value is put in a variable (configurable).
If you configure a delay of 24 hours. The last-24-hour-value = current_value - delayed_value. And this last-24-hour-value is update at the rate of the sample time.

Thanks for your help. This should work fine for me . I don´t have found your plugin for me.
But for the last year i must save the data two times. Reading the original value from the database would actually be better. But i don´t see any way for this.

The plugin is visible on the plugins page (pimatic gui).
You can always add a plugin by adding it to the plugins section in config.json.

{
   "plugin": "tools"
}

Reading the value from the database is more complex because you need te create a query that used a timestamp or time period, to get 1 value that is exactly a year ago. That seems not possible because attributes are only stored when they change. So you need to query a period and get probably more values back. Those values you need to interpolate or get an average.

I think its easier to create day, month, quarter, year variables with the rain gauge value and update it every day, month, quarter and year at 00:00.
And from those values you can create lastDay, lastMonth, lastQuarter, lastYear variables.

And in parallel you can set the deviceAttributeLogging for rain gauge to a year.

Hello friends,

first thanks for your help and ideas.
I have now found a very good solution to my problem.
With the help of the Http API i can read out the appropriate database entries and filter them using a ShellSensorDevice.

Here is my used code for 7 days as an example:

{
  "attributeName": "7d",
  "attributeType": "number",
  "command": "NOW=$((`date +%s%3N --date="7 days ago"`)); curl  -XGET --silent --header \"Content-Type:application/json\" --user \"****:****\" --data '{\"criteria\": { \"order\": \"time\", \"orderDirection\": \"asc\" , \"attributeName\": \"rain\" }}' http://localhost/api/database/device-attributes/ | jq '.events |= map(select(.time > '$NOW')) | .events[0].value'",
  "interval": 5000,
  "id": "7d",
  "name": "7d",
  "class": "ShellSensor"
}

Maybe it will help someone with a similar project.

Best regards
Jens

Nice solution.
A suggestion. You could go from interval every 5 seconds to an interval like 1 day.
When you also add the 1 year ago this will help querying the api and the load on the pi.

I changed it.