Grafana plotting openweathermap forecast from influxdb/telegraf -

InfluxDB 2 Dashboards
, ,
1

Hello All,

i would highly appreciate your help, as (again) i have come to a stop end for my project:

I am using telegraf input plugin openweathermap which writes the data into my influxdb database “telegraf_db”. Now i want to visualize the data using grafana. Unfortunately i am not able to make the correct select statement to get seperate values. The issue is, that for one timestamp multiple entries (from former telegraf entries) exist. What i need, is a query which takes only the one and last value for each tag value (120h, 117h, 114h, 111h, …)

SELECT * FROM weather WHERE city = 'Waiblingen' ORDER BY time DESC LIMIT 20
name: weather
time                 city       city_id cloudiness country forecast host      humidity pressure rain sunrise sunset temperature visibility wind_degrees wind_speed
----                 ----       ------- ---------- ------- -------- ----      -------- -------- ---- ------- ------ ----------- ---------- ------------ ----------
2019-11-26T18:00:00Z Waiblingen 2815330 100        DE      120h     openhabpi 89       1008     0.31                9.01                   172          2.61
2019-11-26T15:00:00Z Waiblingen 2815330 100        DE      117h     openhabpi 89       1010     0                   8.78                   171          1.14
2019-11-26T15:00:00Z Waiblingen 2815330 93         DE      120h     openhabpi 78       1015     0                   7.75                   264          1.58
2019-11-26T12:00:00Z Waiblingen 2815330 98         DE      114h     openhabpi 80       1011     0                   10.63                  234          2.15
2019-11-26T12:00:00Z Waiblingen 2815330 100        DE      117h     openhabpi 67       1015     0                   10.32                  255          4.12
2019-11-26T12:00:00Z Waiblingen 2815330 100        DE      120h     openhabpi 67       1015     0                   10.32                  255          4.12
2019-11-26T09:00:00Z Waiblingen 2815330 100        DE      117h     openhabpi 80       1015     0.13                8.39                   231          3.89
2019-11-26T09:00:00Z Waiblingen 2815330 95         DE      120h     openhabpi 82       1015     0                   8.78                   227          3.93
2019-11-26T09:00:00Z Waiblingen 2815330 100        DE      114h     openhabpi 80       1015     0.13                8.39                   231          3.89
2019-11-26T09:00:00Z Waiblingen 2815330 97         DE      111h     openhabpi 85       1012     0                   8.86                   220          2.8
2019-11-26T06:00:00Z Waiblingen 2815330 100        DE      114h     openhabpi 90       1014     0.13                6.91                   211          2.77
2019-11-26T06:00:00Z Waiblingen 2815330 100        DE      111h     openhabpi 90       1014     0.13                6.91                   211          2.77
2019-11-26T06:00:00Z Waiblingen 2815330 97         DE      120h     openhabpi 90       1014     0                   6.65                   218          3.2
2019-11-26T06:00:00Z Waiblingen 2815330 100        DE      108h     openhabpi 94       1012     0                   6.49                   208          2.26
2019-11-26T06:00:00Z Waiblingen 2815330 97         DE      117h     openhabpi 90       1014     0                   6.65                   218          3.2
2019-11-26T03:00:00Z Waiblingen 2815330 100        DE      108h     openhabpi 90       1014     0                   6.7                    210          2.29
2019-11-26T03:00:00Z Waiblingen 2815330 96         DE      117h     openhabpi 85       1015     0                   6.8                    203          3.11
2019-11-26T03:00:00Z Waiblingen 2815330 100        DE      105h     openhabpi 91       1012     0.13                6.8                    203          2.27
2019-11-26T03:00:00Z Waiblingen 2815330 83         DE      120h     openhabpi 88       1016     0                   5.05                   159          1.75
2019-11-26T03:00:00Z Waiblingen 2815330 96         DE      114h     openhabpi 85       1015     0                   6.8                    203          3.11

My expected answer is something like this

time                 city       city_id cloudiness country forecast host      humidity pressure rain sunrise sunset temperature visibility wind_degrees wind_speed
----                 ----       ------- ---------- ------- -------- ----      -------- -------- ---- ------- ------ ----------- ---------- ------------ ----------
2019-11-26T18:00:00Z Waiblingen 2815330 100        DE      120h     openhabpi 89       1008     0.31                9.01                   172          2.61
2019-11-26T15:00:00Z Waiblingen 2815330 100        DE      117h     openhabpi 89       1010     0                   8.78                   171          1.14
2019-11-26T12:00:00Z Waiblingen 2815330 98         DE      114h     openhabpi 80       1011     0                   10.63                  234          2.15
2019-11-26T09:00:00Z Waiblingen 2815330 97         DE      111h     openhabpi 85       1012     0                   8.86                   220          2.8
2019-11-26T06:00:00Z Waiblingen 2815330 100        DE      108h     openhabpi 94       1012     0                   6.49                   208          2.26
2019-11-26T03:00:00Z Waiblingen 2815330 100        DE      105h     openhabpi 91       1012     0.13                6.8                    203          2.27

So how can i achieve this? I tried in multiple ways (for the last 9hrs now :frowning: ) with DISTINCT(), GROUP BY, LIMIT, SLIMIT, LAST in many different variants but none of them was successfull. Can you pls pls help me out in this.

A similiar, but unanswered question can be found here:
https://community.influxdata.com/t/plotting-weather-forecast-data/9135

Reference to telegraf openweathermap input plugin can be found here:
https://github.com/influxdata/telegraf/tree/master/plugins/inputs/openweathermap

Documentation to openweathermap forecast can be found here:
https://openweathermap.org/forecast5#5days

Thanks very much in advance,
Patrick

2

Im just pushing this question, as i still do not have a clue to fix this. Any hint is highly appreciated

Thanks in advance

3

Hello, I came across the same problem. You can solve it doing something like this :

SELECT * FROM (SELECT last(*) FROM weather WHERE city = ‘Waiblingen’ GROUP BY forecast ORDER BY time DESC)

Here the result I have got from influx command line :

SELECT * FROM (SELECT last(temperature) AS temperature FROM weather GROUP BY “forecast”)
name: weather
time forecast temperature

2020-03-11T09:09:30Z * 13.57
2020-03-11T12:00:00Z 3h 14.05
2020-03-11T15:00:00Z 6h 16.86
2020-03-11T18:00:00Z 9h 14.2
2020-03-11T21:00:00Z 12h 12.4
…etc