Creating a Single Value from the sum of timestamp value across a difference

Grafana MySQL
,
1

Evening,
I’m after some help, I’m a newbie big time sor sorry if this is a simple issue.

I’m using the latest grafana build, self-hosted build.

I have data stored in a mysql database, to keep the explanation simple, the data is monitoring a system when the system is good a 0 (zero) is dropped into the database and bad 1(one) is dropped into the database. Each time the data is written to the database it is written with a timestamp also. In this instance the data is captured ever 5 secs, but i dont want to rely on that 5 secs, as this can be changed at any time. The data points 0 & 1 can change at anytime. I have created a timeline of the data, no problem and set the thresholds so red if 1and green if 0. I have selected to merge if consecutive data points are the same, so when hovering over the are you can see the total duration of the reds & green as you hover along the bar.

What i was to output is a hours value based on the duration set on the view ie 7 days etc. For example 7hr 24mins in red (1) over that selected period.

How do I do this as i need to create some sort of calculation / query that considers each time the data point changes…

Cheers,
Ant

2

maybe

SELECT 
  $__timeGroup(atimestamp,'60m') as time_hourly,
  avg(afloat) as value, 
  avarchar as metric 
FROM mytable 
GROUP BY 1, avarchar 
ORDER BY 1
3

Capture_2
Capture_21660×869 103 KB

I took a screenshot of my current query; in table view. You can see the timestamp on the left and then the 1 & 0 on the right…so I want to sum the total time 1 has been active throughout the period of time selected at the top; and output as a number on my dashboard…ie 4 hrs 30 mins.

I couldn’t get the query above to work.

Cheers
Ant