Hi,
As @CM000n says, it is possible to cast to other types directly in the query
select state::float from ltss;
In the same manner it is possible to cast the attributes to json:
select attributes::json from ltss;
1 Like
Hi @freol
what I would like to understand is if queries like this can be run?
SELECT time_bucket('5 minutes', time) AS five_min, avg(cpu)
FROM metrics
GROUP BY five_min
ORDER BY five_min DESC LIMIT 10;
I think it would be as simple a change as avg(cpu::float)
thanks
Yes, correct, in this example, just replace cpu by cpu::float
2 Likes
BTW: Another nice side effect for me is that I can now use the Timescale Docker container for both, the HA recorder and the long time state storage (Each with its own database of course).
This saves me the separate Postgress and Influxdb containers and I only have to maintain one container now. 
2 Likes
Would it be possible to take the data that is being logged and back feed it into a sql sensor in order to graph it with the mini graph card for example?
select time_bucket('1 day', time)::date as ts,
round( cast( (cast(last(state, time) as float) - cast(first(state, time) as float)) as numeric), 4) as kwh
from ltss
where entity_id = 'sensor.main_totalenergy'
and time::date >= cast(date_trunc('month', current_date) as date)
group by ts;
This returns date’s and the daily consumption and would love to be able load it back into a graph.
@Tjeerd , this question is not really directly related to the LTSS component but here is my take on it:
- The SQL sensor is 100% compatible with LTSS but is implemented as a “typical” HA sensor integration (i.e expects a single value on each update and then stores it with the regular recorder instance etc. Not really suitable for this kind of task where you want to show a history of another entity (regardless of db)
- The mini-graph lovelace card depends on the Home Assistant API. More specifically the history sub-api which is integrated with the regular history/recorder component of HA only. Hence, no luck here unless HA makes the history component a bit more modular (i.e. accepts other storage solutions and not only the regular recorder component).
- My preferred choice is to use grafana, which makes it really easy to graph things like your example but is a little more complicated to integrate with HA frontend, a nice guide can be found here (just ignore all the talk about influxdb and replace those step with LTSS instead.
Hope this gives some ideas about your possibilities!
@Tjeerd another option is to write a script that is run daily and generates a custom output/plot. See this thread. You might also want to checkout the SMA filter.
Cheers
Thanks for the advice @freol and @robmarkcole !
I went a different route for now. Although Grafana is very flexible, it’s a bit of a pain to get some main line stats back into HA. I’ve setup a SQL Sensor which logs the min / max for the current day. No history, but as it runs it will build it. The value of the sensor is going back into the recorder component and I can use in the mini graph card for now
1 Like
I’d really appreciate your advice on the following:
I’ve been trying to figure out how to do that as well ( forum link ), so how to best set that up?
(I’m new to both HA and Docker)
Specifically, I’m getting caught up as to where it would be best to either mount a Docker volume or where to bind the Postgres container to:
sudo docker pull timescale/timescaledb-postgis
sudo docker run \
–name chooseContainerName -e \
POSTGRES_PASSWORD=postgresRootUsersPassword \
-v /your/data/dir:/var/lib/postgresql/data \
-p 5432:5432 \
-d timescale/timescaledb-postgis
So for the choice of /your/data/dir :
e.g. would it be better to:
1.) bind it to the same dir as Home Assistant
(in my case generic linux install of Hassio):
-v /usr/share/hassio/homeassistant:/var/lib/postgresql/data
OR
2.) use default Docker volume location:
-v chooseVolumeName:/var/lib/postgresql/data
OR
3.) somewhere else
?
I would be glad to receive any help on this…
Thanks!
I’m using docker compose and storing the timescale data on my btrfs raid.
But I think you can store them wherever you want and have enough space 
timescale:
container_name: timescale
restart: unless-stopped
image: timescale/timescaledb-postgis:latest
volumes:
- /srv/dev-disk-by-id-ata-ST32000542AS_5XW2HFF9/data/docker_data/timescale:/var/lib/postgresql/data
- /etc/localtime:/etc/localtime:ro
environment:
- POSTGRES_USER=myuser
- POSTGRES_PASSWORD=mypassword
ports:
- "5442:5432"
1 Like
Is possible to use this for store states for e.g. 1 year? Will it work without any problem on raspberry pi 3?
Even if it is basically feasible, it is probably not a wise decision to run a relational database to back up long term data on a Raspberry Pi.
You might encounter performance issues.
Here is a very interesting blog article about the Postgress Performance on a Rasberry Pi 3: https://blog.rustprooflabs.com/2019/04/postgresql-pgbench-raspberry-pi
It is also questionable whether the space on a Raspberyy Pi is sufficient for this.
Just to give you a rough idea: I currently have about 350 entities and sensors in Home Assistant being tracked. My LTSS Postgres database has been running since the beginning of the year and already occupies about 400MB:
Thank you for answer. So i need to find different solution for that what i want - store at least min, max, average for each hour for last 3 months, and then for data older than 3 months values for each day for each sensor recorded by recorder. Any idea?
Mhmm, you could perhaps write an SQL script that will take the values to the required level of aggregation and purge the values that are no longer needed.
You could run this automatically, for example with the help of CRON or pg_cron: https://www.citusdata.com/blog/2016/09/09/pgcron-run-periodic-jobs-in-postgres/.
If you want to use another database, in MySQL there is also an event scheduler: https://dev.mysql.com/doc/refman/5.7/en/event-scheduler.html
This way you could keep your database as small as possible.
Just a quick idea…
A suggestion is to have a look in the docs for timescaledb, specifically these should be of interest:
https://docs.timescale.com/latest/using-timescaledb/continuous-aggregates
https://docs.timescale.com/latest/using-timescaledb/data-retention
Note: I havent tried these features myself so cant really say much else.
2 Likes
The timescaledb-postgis image you linked in your github doesn’t support raspberry pis/arm7v32, I created my own container here, in case anybody wants to use it.
Sorry, where is your docker container?
Seems like I forgot the link, sorry.
2 Likes
Hi @freol, I just wanted to let you know that since HA version 0.109 I’m getting an error message which is probably caused by ltss:
Traceback (most recent call last):
File "/usr/src/homeassistant/homeassistant/components/websocket_api/decorators.py", line 20, in _handle_async_response
await func(hass, connection, msg)
File "/usr/src/homeassistant/homeassistant/components/frontend/__init__.py", line 550, in websocket_get_translations
msg.get("config_flow"),
File "/usr/src/homeassistant/homeassistant/helpers/translation.py", line 329, in async_get_translations
resources = flatten(resource_func(results[0], components, category))
File "/usr/src/homeassistant/homeassistant/helpers/translation.py", line 127, in merge_resources
new_value = translation_strings[component].get(category)
KeyError: 'ltss'
And as a Feature Request: Could you maybe also add HACS support to ltss?
Many thanks in advance and again for your great component
Greetings
Thanks for the heads-up @CM000n . Can you please open an issue in the github repo? (link in the first post)
Fyi, seems to be related to this issue: https://github.com/home-assistant/core/issues/34857
For your feature request, please also put this in the issue tracker on the github repo. Unfortunately I have no previous experience with HACS and dont use it myself. I you feel up for the challenge I am happy to accept a pull request for this.