Can I use Timescale DB as an alternative to Influx DB in Homeassistant for Grafana?

CM000n · January 1, 2021, 11:12am

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)

drimpart · January 1, 2021, 12:32pm

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?

CM000n · January 1, 2021, 1:20pm

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”

drimpart · January 1, 2021, 1:29pm

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

CM000n · January 1, 2021, 1:42pm

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

drimpart · January 1, 2021, 2:03pm

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

CM000n · January 1, 2021, 2:11pm

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

drimpart · January 1, 2021, 2:30pm

perfect! thanks for your help!