Hi Motik68!
What architecture are you running (i.e. on what device / make /model)?
RPi 3 B, using HASSOS (so no underlying Rasbpian).
See a little bit higher-up in this topic.
Running a full-blown Postgres server on a Pi 3B with timescale is a little bit too much I’m afraid.
If you decide to stick with the PI 3 for HA, it’s best to run timescale/postgres on a separate server/cloud/container.
From PI4 and up it becomes feasible.
Thanks, I hadn’t thought of that, and it makes sense. That also explains why my swap is growing, I guess 
I will try installing the PostgreSQL database on another machine on the network or finally migrate my HA installation on a Pi4
1 Like
I’m running the TimescaleDB add-on together with the LTSS integrations for some weeks now and I’m impressed about the possibilities.
I’m not an expert in this area and therefore I’m struggling with the Query language in Grafana. @CM000n already helped me a lot in designing the first graphs. It would be an idea that some examples of Grafana graphs designed in combination with Home Assistant are published in this community item in order that people not so acquinted with this stuff can step in easier.
I have now the following problem: I have measured rain precipitation data and made a graph of precipation increase in time in Grafana. See next picture (the applied Query is also shown):
I would like to make a graph now of precipitation data per hour (or per day). In InFlux/Grafana this can be done quit easily with the function “derivative”. But how that can be carried out here??
Just use 1d (day) as your minimum time interval for grouping under “Query Options” 
The question is rather what you want to have here as the respective hours/daily value?
Here it makes a difference if you want to use MIN, MAX, AVG or the last hour/day value.
I would like to make the following kind of graph (the example shown below is based on inFlux-data);
When I follow your recommendations I do not obtain the mm precipitation for each hour.
Does your sensor reset? Of does it simply climb just as electritymeters do? If the last is true, you could simply do max(value) - min(value) as difference in your query . This will give you the increase in mm
per grouping period.
Othe ways are to use Postgres WIndowing functions. See this for example:
This Screenshot says nothing about the aggregation level
But I think you want the SUM per hour or day if you sensor resets
Or use the MAX-MIN difference per hour / day
My precipitation sensor is just climbing up. Reset is possible but is not so easy because the device is “sleeping” almost all the time. So the simplest way to solve is to make a query.
You gave me already some suggestions. I will look at it but when you could help me somewhat further it would be very nice.
Yes indeed the amount of rain in one hour. So the MAX-MIN difference per hour should do the trick I think. But how to put that in query language is not so easy for me.
This should do the Trick:
SELECT
$__timeGroupAlias("time",$__interval)
, 'Hoeveelheid neerslag'
, MAX(state::FLOAT) - MIN(state::FLOAT) AS value
FROM ltss
WHERE
$__timeFilter("time")
AND entity_id = 'sensor.regensensor_hoeveelheid_regen'
AND state NOT IN ('', 'unavailable')
GROUP BY 1,2
ORDER BY 1,2
And change your time intervall like described here:
So… I was searching and searching on the Internet and your query script was deviating not so much from the original one!!
However there is still a complication. See the Grafana graph below:
All bars show a value of 0.5 mm whereas in some hourly periods more rain has fallen (20:00 h yesterday 1.0 mm; 04:00 h and 05:00 last night 1.0 mm; last hour 1.0 mm). What can be the reason that only 0.5 mm is shown in the graph??
Did you change the time interval in the query options to 1h as well?
What does the data look like when you make a query directly on your table?
I also don’t know if this is getting so offtopic and still has something to do with the actual HA addon?
Maybe we should open a separate thread for Grafana with Timsecale or something?
1 Like
I made two Grafana graphs:
Graph 1: time interval in query options:
Graph 2: time interval directly in the query:
Both are leading to the same result.
Your question (What does the data look like when you make a query directly on your table?) is answered by Graph 2??.
I agree with you that it might be better to open a separate thread. This afternoon, when I started this question, I choose for this thread because I knew that you and also @Expaso are active on this thread.
What I meant by “make a query directly on your table” is that I would be interested to see what the data looks like when you look at it directly in the table without Grafana, i.e. with any database tool.
It’s hard to guess what’s going on in the background if we don’t know the actual data.
Would you start a new thread?
I opened a new thread (see: Making graphs in Grafana from PostgreSQL/ TimescaleDB databases).
Make a query directly on the table! How do I do that! On my Home Assistant system I have PgAdmin running (as an add-on). But I have the impression that PgAdmin is not suitable for carrying out the task that you suggest. Are there other possibilities?
1 Like
Hi Expaso,
Just wanted to let you know that, after transferring my HA installation on a Pi 4B 4 GB I still get the same error message Error relocating /usr/lib/postgresql/postgis-3.so: __time64: symbol not found.
So unless I did something really stupid it looks like there is a bug somewhere at install time with postgis-3.so. Weird thing is that I seem to be the only one getting this error, so maybe I did do something really stupid after all…
Hi @Motik68,
You need the x64 bit image for your PI4.
Only 64-Bit Operation systems can address > 2GB mem.
Raspberry Pi - Home Assistant (home-assistant.io)
Then it should work fine 
1 Like
Hi,
i really appreciate your work. Is it possible to enhance the timescaledb within hass.io with say plpythonu. I would need this extension to bring intelligent features into the database using functions.
Toni