Display S0-Impulse Values

Grafana
1

Hi at all!

I’ve got a MySQL Database with the following rows:

id | channel_id | timestamp | value
1188122 | 16 | 1509134552863 | 1
1188117 | 16 | 1509134355607 | 1
1188116 | 16 | 1509134306097 | 1
1188115 | 16 | 1509134258030 | 1
1188113 | 16 | 1509134217868 | 1
1188106 | 16 | 1509134177735 | 1
1188104 | 16 | 1509134137744 | 1
1188101 | 16 | 1509134098104 | 1
1188097 | 16 | 1509134058869 | 1
1188095 | 16 | 1509134020423 | 1
1188091 | 16 | 1509133982958 | 1
1188088 | 16 | 1509133945493 | 1
1188085 | 16 | 1509133908074 | 1
1188079 | 16 | 1509133870714 | 1
1188078 | 16 | 1509133815953 | 1

the value is always 1 and equals 1/75 kW

with the following querry I got this graph:

graph
graph1639×358 41 KB

SELECT
  cast(timestamp/1000 as signed integer) as time_sec,
  value as value,
  "Stromverbrauch" as metric
FROM data
WHERE cast(timestamp/1000 as signed integer) > $__unixEpochFrom() and cast(timestamp/1000 as signed integer) < $__unixEpochTo() and
channel_id = 16
ORDER BY timestamp ASC

is there any chance to get a graph like this:

graph2
graph21252×626 18.2 KB

Cheers!
Karl

2

Impossible to answer, what do you need help with? The query or graph display options??

3

Looks like you wanna calculate consumption per time unit. To do that you need to aggregate by time. In master there is a $__timeGroup macro for mysql but it didnt make it into 4.6.0.

You probably want something similar to the following query:

SELECT
  cast((timestamp/1000)/300 as signed integer)*300 as time_sec,
  sum(value) as value,
  "Stromverbrauch" as metric
FROM data
WHERE cast(timestamp/1000 as signed integer) > $__unixEpochFrom() and cast(timestamp/1000 as signed integer) < $__unixEpochTo() and
channel_id = 16
GROUP BY 1
ORDER BY 1

300 is the time interval for the time interval you want to calculate the rate for in seconds. 300s = 5m

4

Hi Sven!
Again perfect answer. This is exactly what I was looking for. Thank you!!!

Heizung
Heizung1658×234 18.3 KB

Kind Regards!
Karl