Calculate difference timestamp based on status (on/off)

Keywords´╝Ü mysql sql timestamp

Question: 

Im trying to calculate the totale (daily) uptime and the total downtime in MySQL. I got the follow database:

CREATE TABLE `IOT_DATA` (
  `DATA_ID` int(11) NOT NULL,
  `DATA_BRON` text NOT NULL,
  `DATA_SOORT` text NOT NULL,
  `DATA_WAARDE` int(11) NOT NULL,
  `DATA_TIJD` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `IOT_DATA` (`DATA_ID`, `DATA_BRON`, `DATA_SOORT`, `DATA_WAARDE`, `DATA_TIJD`) VALUES
(931, '77', 'temperature', 29, '2019-06-11 14:05:00'),
(932, '77', 'humidity', 32, '2019-06-11 14:05:00'),
(933, '77', 'temperature', 30, '2019-06-11 14:10:01'),
(934, '77', 'humidity', 32, '2019-06-11 14:10:01'),
(935, '77', 'temperature', 30, '2019-06-11 14:15:00'),
(936, '77', 'humidity', 31, '2019-06-11 14:15:00'),
(963, '77', 'status', 0, '2019-06-11 14:17:19'),
(978, '77', 'status', 1, '2019-06-11 14:18:40'),
(982, '77', 'temperature', 29, '2019-06-11 14:20:00'),
(983, '77', 'humidity', 32, '2019-06-11 14:20:00'),
(993, '77', 'status', 0, '2019-06-11 14:22:40'),
(1008, '77', 'status', 1, '2019-06-11 14:23:21'),
(1019, '77', 'status', 0, '2019-06-11 14:24:50'),
(1044, '77', 'temperature', 30, '2019-06-11 14:25:00'),
(1045, '77', 'humidity', 32, '2019-06-11 14:25:00'),

ALTER TABLE `IOT_DATA`
  ADD PRIMARY KEY (`DATA_ID`);

ALTER TABLE `IOT_DATA`
  MODIFY `DATA_ID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1070;

It saves all sensor data from a machine. The 'DATA_SOORT' "status" indicates if the machine is started (1) or stopped (0).

I do not have a clue where to begin, I searched for comparable topics and got this:

select 
    date, 
    DATA_ID, 
    sum(datediff(minute, starttime, endtime )) downtime 
from (
    select 
       cast(t1.DATA_TIJD as date) as date, 
       t1.serviceid, 
       min(t1.DATA_TIJD) starttime, 
       ca.DATA_TIJD endtime
    from IOT_DATA t1
    cross apply (
       select top 1 DATA_TIJD
       from IOT_DATA
       where serviceid = t1.serviceid 
       and DATA_TIJD > t1.DATA_TIJD 
       and DATA_SOORT = "status"
       and DATA_WAARDE in (1) 
       order by DATA_TIJD
    ) ca
    where t1.DATA_ID in (0)
    group by cast(t1.DATA_TIJD as date), t1.DATA_ID, ca.DATA_TIJD
) a
group by date, DATA_ID;

It's not working, and honestly have no idea what it is. It gives the follow error:

- Incorrect parameter count in the call to native function 'datediff'

So i searched for that error and got something like it only can handle two expressions? But im not even sure if this is the correct way.

The expected result should be like

+------------+--------+----------+------------+--------------+
|    Date    | Source | TotaleUp | TotaleDown | PercentageUp |
+------------+--------+----------+------------+--------------+
| 11-06-2019 |     77 | 04:30:20 | 3:30:40    | 56%          |
| 12-06-2019 |     77 | 06:00:00 | 1:00:00    | 85%          |
+------------+--------+----------+------------+--------------+

The source = DATA_BRON PercentageUP = Totaleup/(Totaleup+TotaleDown)*100

It is my first topic, if anything is missing please let me know

Answers: