Thank you very much for this addon, I really love it!
I have a graph in grafana that displays my fitbit steps:
SELECT
"time" AS "time",
state as metric
FROM ltss
WHERE
$__timeFilter("time") AND entity_id = 'variable.fb_steps'
GROUP BY 1,2
ORDER BY 1,2
But I don’t know how to SUM them, so that all steps are calculated together. I tried SUM(state) as metric, but I get only this error:
pq: function sum(character varying) does not exist
Can somebody help?
Hi @drimpart!
Thank you very much! Nice to hear!
I think I know what the problem is:
The ‘state’ field of ltss is a text-field. This is because a state in HA can be anything. The name of your cellphone, the position of the sun, or in your case: the amount of steps.
The error indicates that SQL does not know how to sum text…(varying characters).
To overcome this, please take a look at: Additional Documentation for Timescaledb Integration · Issue #1 · Expaso/hassos-addons (github.com)
fantastic! Thank you @Expaso that was the trick, I did run both scripts (the second one took me about 7 minutes) and now I can calculate with the values!
One last question: I have some sensor that update more than once a day, and now I want to sum all maximum values (so the last ones before they reset at 0 o’clock) - is this possible?
Sure it is!
It looks like your step counter increments during the day, looking at your data, and I presume you want to show a graph where you can see the total steps per day, am I right?
If so, try something like this:
SELECT
$__timeGroup(time,1d,0) as "time",
max(state_numeric) AS "state_numeric"
FROM ltss
WHERE
$__timeFilter(time) AND
entity_id = 'sensor.steps'
GROUP BY 1
ORDER BY 1
The magic is in the $__timeGroup(time, 1d) which groups your measurements per (1) day, and then take the max(state_numeric) to get the max per day.
The $__ functions are Grafana macro’s. See also: https://grafana.com/docs/grafana/latest/datasources/postgres/#time-series-queries
So i installed TimescaleDB
databases:
- homeassistant
timescale_enabled:
- homeassistant
timescaledb:
telemetry: basic
maxmemory: 512MB
maxcpus: '4'
max_connections: 20
system_packages: []
init_commands: []
Installed pgAdmin4
ssl: false
certfile: fullchain.pem
keyfile: privkey.pem
system_packages: []
init_commands: []
leave_front_door_open: false
Try to add the TimescaleDB to pgAdmin4 but no luck
77b2833f-timescaledb port 5432 >> not working
could not translate host name "77b2833f-timescaledb " to address: Name does not resolve
? So what todo to add it to pgAdmin4 ?
Iám on Hasso 5.10 core-2021.2.3 running on a VM
Update 21-2-2021
Thanks to SanderdW from the DSMR Reader Add-on for Home Assistant it is working.
So for other people struggling with the same
After install TimesclaeDB look in the main screen look at Hostname and this situation 7cd0051d-timescaledb
@PeetsHome Nice to see you have it working!
Strange think is, the name should be stable. it’s based upon the github repository of the addon. It is a forked repo perhaps? I will look into this, and update the docs accordingly.
I used this one Home Assistant Add-on: PostgreSQL + TimescaleDB . This link is given by the DSMR addon https://github.com/sanderdw/hassio-addons/blob/cb200114535ebbb27ca15f3500ef1a0b96a35c6b/dsmr_reader/README.md So i think it is the right one.
Yes, that’s the right one!
So no need to worry, I will look into this.
Hello,
I installed the TimescaleDB and LTSS add-ons . The PostgreSQL database works fine by itself, but when I try to restart HASSOS with LTSS enabled I get the following error:
Logger: custom_components.ltss
Source: custom_components/ltss/__init__.py:167
Integration: ltss (documentation)
First occurred: 22:03:04 (10 occurrences)
Last logged: 22:03:52
Error during connection setup: (psycopg2.errors.UndefinedFile) could not load library "/usr/lib/postgresql/postgis-3.so": Error relocating /usr/lib/postgresql/postgis-3.so: __time64: symbol not found [SQL: CREATE EXTENSION IF NOT EXISTS postgis CASCADE] (Background on this error at: http://sqlalche.me/e/13/e3q8) (retrying in 3 seconds)
I first asked in the LTSS thread, and it turns out that postgis does not seem to be installed in PostgreSQL. When trying to install the extension manually in PostgreSQL I get the same error message:
ERROR: could not load library "/usr/lib/postgresql/postgis-3.so": Error relocating /usr/lib/postgresql/postgis-3.so: __time64: symbol not found
What did I do wrong? My understanding is that postgis is intended to be automatically installed and work out of the box.
Thanks in advance!
It seems that your Postgres installation is missing the postgis extension: PostGIS — Spatial and Geographic Objects for PostgreSQL
At the moment this is still a basic requirement for LTSS
You can either download and install it manually, or if you are using a Docker container for Timescale, use one that has Postgis pre-installed right away.
Oops, I didn’t realize this thread was about the Timescale Home Assistant addon 
Is the Postgis extension already included in the AddOn @Expaso ?
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
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
