19 lines
869 B
SQL
19 lines
869 B
SQL
SELECT
|
|
CONCAT(DATE(dc.t_stamp), ' ', HOUR(dc.t_stamp), ':00') AS StartTimestamp,
|
|
CONCAT('H', TIMESTAMPDIFF(HOUR,
|
|
DATE_FORMAT(dc.t_stamp, "%Y-%m-%d %H:00:00"),
|
|
DATE_FORMAT(NOW(), "%Y-%m-%d %H:00:00"))) AS Hour,
|
|
|
|
CONCAT(ROUND(SUM(dc.ulc1 = 1)/COUNT(*)*100*2, 2),'%') AS ULC1,
|
|
CONCAT(ROUND(SUM(dc.ulc2 = 1)/COUNT(*)*100*2, 2),'%') AS ULC2,
|
|
CONCAT(ROUND(SUM(dc.ulc3 = 1)/COUNT(*)*100*2, 2),'%') AS ULC3,
|
|
CONCAT(ROUND(SUM(dc.ulc4 = 1)/COUNT(*)*100*2, 2),'%') AS ULC4,
|
|
CONCAT(ROUND(SUM(dc.ulc5 = 1)/COUNT(*)*100*2, 2),'%') AS ULC5,
|
|
CONCAT(ROUND(SUM(dc.ulc6 = 1)/COUNT(*)*100*2, 2),'%') AS ULC6,
|
|
CONCAT(ROUND(SUM(dc.ulc7 = 1)/COUNT(*)*100*2, 2),'%') AS ULC7,
|
|
CONCAT(ROUND(SUM(dc.ulc8 = 1)/COUNT(*)*100*2, 2),'%') AS ULC8
|
|
|
|
FROM dumper_cycles dc
|
|
WHERE dc.t_stamp BETWEEN :starttime AND :endtime
|
|
GROUP BY HOUR(dc.t_stamp);
|