add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Dataset
(Datum Datetime, Pot int);
INSERT INTO Dataset
(Datum, Pot )
VALUES
('2023-10-06 21:43:40', 474),
('2023-10-06 21:43:50', 0),
('2023-10-06 21:45:40', 0),
('2023-10-06 21:45:50', 40),
('2023-10-06 21:53:50', 245),
('2023-10-06 21:54:00', 0),
('2023-10-07 00:00:00', 0),
('2023-10-07 09:15:40', 0),
('2023-10-07 09:15:50', 2),
('2023-10-07 09:28:50', 322),
('2023-10-07 09:29:00', 0),
('2023-10-07 09:30:40', 0),
('2023-10-07 09:30:50', 9),
('2023-10-07 12:59:10', 471),
('2023-10-07 12:59:20', 0),
('2023-10-07 13:42:20', 0),
('2023-10-07 13:42:30', 82);
Records: 17  Duplicates: 0  Warnings: 0
select * from Dataset
Datum Pot
2023-10-06 21:43:40 474
2023-10-06 21:43:50 0
2023-10-06 21:45:40 0
2023-10-06 21:45:50 40
2023-10-06 21:53:50 245
2023-10-06 21:54:00 0
2023-10-07 00:00:00 0
2023-10-07 09:15:40 0
2023-10-07 09:15:50 2
2023-10-07 09:28:50 322
2023-10-07 09:29:00 0
2023-10-07 09:30:40 0
2023-10-07 09:30:50 9
2023-10-07 12:59:10 471
2023-10-07 12:59:20 0
2023-10-07 13:42:20 0
2023-10-07 13:42:30 82
SELECT
DATE_FORMAT(Datum, "%Y-%m-%d") as "Date",
round((TIME_TO_SEC(TIMEDIFF(Max(Datum), Min(Datum)))/86400)*100,0) as "Daily downtime [%]",
round(100-((TIME_TO_SEC(TIMEDIFF(Max(Datum), Min(Datum)))/86400)*100),0) as "Daily % uptime [%]"
FROM
Dataset
where
Pot = 0 and Datum between "2023-10-06 00:00:00.000" and "2023-10-07 23:59:50.000"
group by
Datum,
month(Datum)
order by
Datum ASC
Date Daily downtime [%] Daily % uptime [%]
2023-10-06 0 100
2023-10-06 0 100
2023-10-06 0 100
2023-10-07 0 100
2023-10-07 0 100
2023-10-07 0 100
2023-10-07 0 100
2023-10-07 0 100
2023-10-07 0 100