Making graphs in Grafana from PostgreSQL/ TimescaleDB databases

CM000n · March 12, 2021, 5:53pm

Of course you get an error. The query was meant for Pgadmin. For Grafana you have to adjust and add the time variables for grouping and filtering

poerle · March 12, 2021, 6:41pm

CM000n:

SELECT
    TO_CHAR(i1.time, 'YYYY-MM-DD HH24') AS time
    , 'Hoeveelheid neerslag' AS metric
    , MAX(i1.value)-MIN(i1.lag_value) AS value
FROM
    (
    SELECT
        i0.time
        , LAG((i0.state::FLOAT)) OVER(ORDER BY i0.time) AS lag_value
        , (i0.state::FLOAT) AS value
    FROM ltss AS i0
    WHERE i0.entity_id = 'sensor.regensensor_hoeveelheid_regen'
        AND i0.state NOT IN ('', 'unavailable')
    ) AS i1
GROUP BY 1,2
ORDER BY 1,2

I think you found the trick!!

Now the transfer to Grafana and we had a very fruitful Friday!

Expaso · March 14, 2021, 9:48am

I think you should also take a look at: https://docs.timescale.com/latest/using-timescaledb/reading-data#locf and https://docs.timescale.com/latest/using-timescaledb/reading-data#gap-filling

Btw, many props to @CM000n for taking so much effort to dig your problem out!

Yagyu · November 17, 2021, 2:29pm

Hello,

I also use the LTS / Postgres, but I’m somehow too stupid to display the whole thing in Grafana …

I can display the table, but as soon as I switch to the visualize I get an error …

Where is my mistake?

Thanks very much

EDIT: Fixed it:

CM000n · November 17, 2021, 6:31pm

Glad it worked for you

You can also convert the values to integers or floats directly within the query. This would look like this.

SELECT
  "time" AS "time"
  , entitiy_id AS metric
  , state::FLOAT AS value
FROM ltss
WHERE
  entity_id = 'sensor.solar_production_daily'
ORDER BY 1

This is, IMHO, sometimes easier than searching for the right setting in the Grafana Gui.

Yagyu · November 17, 2021, 8:43pm

Thanks!

is there an alternative to ALIAS BY that works with postgres? all data are now called “value” in the Legend -_- I only find solutions with ALIAS BY, but this is not recognized by me.

CM000n · November 17, 2021, 9:14pm

I’m not sure I understand you correctly and what you mean by “ALIAS BY”.
In general you should put the time values into a $__timeGroupAlias and $__timeFilter to use the time intervals of the Grafana dropdowns comfortably.
This documentation may also help you:
PostgreSQL | Grafana Labs
For the metric labels you can either take values from other existing columns or specify them directly manually. This does not matter then.

This query results in the following graphic for me, for example:

SELECT
  $__timeGroupAlias("time",$__interval,previous)
  , AVG(state::FLOAT) AS "PM2.5 - Ubstadt"
FROM ltss
WHERE
  $__timeFilter("time")
  AND entity_id = 'sensor.luftdaten_19149_p2'
  AND state != 'unavailable'
GROUP BY 1
ORDER BY 1

Yagyu · November 18, 2021, 12:31pm

Big Thanks,

I’ve tried only

SELECT
  "time" AS "time"
  , entitiy_id AS metric
  , state::FLOAT AS value
FROM ltss
WHERE
  entity_id = 'sensor.solar_production_daily'
ORDER BY 1

I tried to change value to “XYZ”
When I tried this, I got an error. So I thought it had to be “value” … but this made sure that I had 3 graphs, all of which were labeled “value”.

But with your solution it works perfectly

big thank you