Dear Community,
I have a SQL Database and in the Database I lock the POE Voltage, Power and Current Amp’s.
To create a graf for for each induvidual Port in no Problem I can create dirct from The Database.
My Proble is as follow.
I would like to create SUM() or AVG() every 15 Minutes and put this into the Dashboard.
Database ir like this and the Port is selected by port_01 to port_**.
TIMESTAMP DEVICE TYPE EVENT READING VALUE UNIT
2022-05-08 20:37:48 Switch_24_Port_PoE_250W UNIFISWITCH port_02_poe_voltage: 52.82 port_02_poe_voltage 52.82
2022-05-08 20:36:45 Switch_24_Port_PoE_250W UNIFISWITCH port_02_poe_voltage: 52.76 port_02_poe_voltage 52.76
2022-05-08 20:34:33 Switch_24_Port_PoE_250W" UNIFISWITCH port_02_poe_voltage: 52.88 port_02_poe_voltage 52.88
I wrote a Query to create this Result.
created_dt_new Sum
2022-05-01 22:50:00 158,28
2022-05-01 23:00:00 475,18999999999994
2022-05-01 23:10:00 369,75
2022-05-01 23:20:00 316,72999999999996
When I try to input this to Grafana I receive no Time Series I receice for each 10 Minutes a different Query.
What I want is like this for one Port.
My Query is this
SELECT
UNIX_TIMESTAMP(`TIMESTAMP`) as time_sec,
concat( date(`TIMESTAMP`) , ' ', sec_to_time(time_to_sec(`TIMESTAMP`)- time_to_sec(`TIMESTAMP`)%(15*60) + (15*60)))as created_dt_new,
SUM(VALUE) AS Sum ,
'Port 02 POE Spannung in V' as metric
FROM `history`
WHERE `DEVICE` = 'Switch_24_Port_PoE_250W' AND `READING` like 'port_%_poe_power'
GROUP BY created_dt_new
I hape someone can help me wit this beginner problem
Thanks
Thomas