One other reason for dropping InfluxDB is it’s very limited DB size on 32bit systems such as the Raspi. I’m collecting HomeAssistant data for about six months now and have hit the limit where InfluxDB cannot compact its data files any more (~2GB database size) and will eventually freeze the whole system every other week. Totally unacceptable, especially when considering that databases are meant to serve data that is far larger than the available memory (otherwise I don’t need a database I can simply store everything in memory). Hence I will also investigate switching to TimescaleDB.
Is there any way to transfer collected data from InfluxDB to TimescaleDB or do I have to start from scratch?
1 Like
Yes there is!
It’s called Outflux: https://www.outfluxdata.com/
I have never tried it, but from the look of it, it should work.
Perfect, that worked like a charm. I was able to successfully migrate from InfluxDB to TimescaleDB by keeping all old data. Also connecting Grafana to TimescaleDB was succesful and provides even better visualizations (e.g. handling time intervals in which there is no measurement). I’ll try to summarize my project in the next few days as a reference for others.
1 Like
Awesome!
A documented upgrade path from Influx to TimescaleDb would help tremendously for those who seek to upgrade!
Nice work!
Gap filling was exactly why I switched over to timescale DB. Your addon was a lifesaver, thank you!
1 Like
1 Like
create unique index concurrently …
what if you don’t specify concurrently? (that would lock out writers, so HA can’t be running)
I am trying to see whether it possible to setup the tables in timescaledb enabled postgresql server, and then restore my data from normal postgresql db.
@Expaso, Thanks a lot for the integration.
I got it installed and it didn’t trigger any errors. I can see that extension has been added to the DB, but being new to the Postgres, can you may be put a bit of light for me on how the data is actually stored there, as I can’t see any new schemas added.
That’s getting me confused in how I should query the data from Grafana.
Thanks.
Disregard this question. I found it under the public schema 
No prob!
Happy you have it working!
I have a sensor that has different string states. Is it possible to display the time range of these states in Grafana for example in a pie chart?
I tried it already but the result makes no sense, it should be something like Alternative: 03:22:10 and so on…
Since no time periods are recorded, but individual points in time (start), you probably have to work with a subquery and, if necessary, determine the next point in time (end) via a lead function. From this subquery you can then determine the sum of the time differences of two points in time (start and end) for each genre.
thanks for your reply @CM000n! Glad to hear it could be possible. Tried it first with influxdb, but as far as I know I can´t get different string states displayed in grafana…
I discovered Timescaledb and PostgreSQL just yesterday and want to learn a bit more about. Can you lead me maybe in the right direction how I can add a subquery for this here:
SELECT
$__timeGroupAlias("time",$__interval),
state AS metric,
max(id) AS "id"
FROM ltss
WHERE
$__timeFilter("time") AND
entity_id = 'sensor.get_the_genre'
GROUP BY 1,2
ORDER BY 1,2
Happy new year!
That’s how I would normally do it.
For example, the unformatted LTSS data for my Chromecast looks like this:
Using a lead function, I would then simply calculate the difference between individual states:
As far as I know, you should then simply be able to calculate the sum for “diff_in_seconds” in Grafana and simply replace the hard-coded values (like “a.time”) with the appropriate variables.
Unfortunately I don’t have the possibility to test it in Grafana myself at the moment, but I hope it gives you an idea.
Greetings
…::edit::…
So it seems our Chromecast has played ~74 hour of media since September 
(At least as far as sensor data was recorded)
1 Like
thank you very much for your help so far @CM000n!! I installed pgAdmin 4 and tested your last code, and it seems to work!
Sorry for the newbie question, but what do you mean with the variable instead of a.time? I tried to paste the code into Grafana but got this error: “Found no column named time”
Do I have to create this variable in Grafana first or is it something like $__time?
It looks like you have to replace the hardcoded “DATE(a.time) >= ‘2021-01-01’ " with the appropriate Grafana Variable like " $__timeFilter(“time”)”
BTW: Nice to see you listening to “Krautrock” 
1 Like
i replaced
WHERE DATE(a.time) >= '2021-01-01'
to
WHERE DATE(a.time) >= $__timeFilter("time")
but now I get this error: “pq: operator does not exist: date >= boolean”
sorry for all the questions, this all completely new to me!
I grab the music genres from last.fm and I think they are user generated, and almost all music that is from Germany and has something to do with rock is categorised there as Krautrock
I have just tried it in Grafana.
I have no problems. The code is
SELECT
$__timeGroupAlias("time",$__interval)
, b.metric
, SUM(b.diff_in_seconds)/3600 AS hours_in_state
FROM (
SELECT
a.state AS metric
, a.time
, EXTRACT(EPOCH FROM (LEAD(a.time) OVER(ORDER BY a.time ASC) - a.time)) AS diff_in_seconds
FROM ltss AS a
WHERE $__timeFilter(a.time)
AND a.entity_id = 'media_player.chromecast_wohnzimmer'
ORDER BY a.time
) AS b
GROUP BY 1,2
ORDER BY 1,2
Don’t forget to set the Calculation to “Total” if you are using a gauge or pie chart. Otherwise it will try to display the individual values of the specified time intervals.
Interesting. I am from Germany. And Krautrock is usually something special and not just rock
1 Like
YES! That code snippet is working for me too, thank you very much!
I tried to change the total values into duration (hh:mm:ss) but almost all values appear as 00:00:00 - do you know why?
one last question, when no music is played the genre is just “Pause” is it possible to exclude this state from the pie chart?
(oh ich och, der gute alte krautrock ist natürlich mehr als nur rock aus Deutschland, keine frage
1 Like
Because the query already returns decimal values for hours.
Grafana does not know how to convert them. If you remove the “/3600” and thus get the seconds, you can also use your time format:
To exclude certain states, you can simply add a NOT IN function. For example like this:
SELECT
$__timeGroupAlias("time",$__interval)
, b.metric
, SUM(b.diff_in_seconds) AS hours_in_state
FROM (
SELECT
a.state AS metric
, a.time
, EXTRACT(EPOCH FROM (LEAD(a.time) OVER(ORDER BY a.time ASC) - a.time)) AS diff_in_seconds
FROM ltss AS a
WHERE $__timeFilter(a.time)
AND a.entity_id = 'media_player.chromecast_wohnzimmer'
ORDER BY a.time
) AS b
WHERE b.metric NOT IN ('off', 'unavailable')
GROUP BY 1,2
ORDER BY 1,2
1 Like