How to display effective machine hours by hours in MySQL?

Question

MySQL

There is a common table with events of several machines. New entries are added when the machine status is updated. For example, Fanuc-0id-01 entered the ACTIVE state at a certain time. Then he became "STOPPED" at another time.

You want the MYSQL query to display the efficiency of a specific machine as a percentage for hourly periods over the last 24 hours. For example, the Fanuc-0id-01 machine was in the "ACTIVE" status
from 13.00 to 13.59 - 80%
from 14.00 to 14.59 - 76%
and so on.

The most difficult thing is that the machine can go into the ACTIVE state in one hour, and go to another status only after a couple of hours.

Answer 1

SELECT 
 _data.`_begin`,
 _data.`name`,
 ROUND(SUM(UNIX_TIMESTAMP(_data.`_right`) - UNIX_TIMESTAMP(_data.`_left`) + 1) / 3600, 2) * 100 AS _availability
FROM (
  SELECT DISTINCT
   calendar.`_begin`,
   periods.`name`, 	 
   GREATEST(calendar.`_begin`, periods.`_begin`) AS _left,
     LEAST(calendar.`_end`, periods.`_end`) AS _right
  FROM (
    SELECT
     CURDATE() + INTERVAL (HOUR(NOW()) - iter.hours) * 3600 SECOND AS _begin,
     CURDATE() + INTERVAL (HOUR(NOW()) - iter.hours + 1) * 3600 - 1 SECOND AS _end
    FROM (
      SELECT 24 AS hours UNION SELECT 23 UNION SELECT 22 UNION SELECT 21 UNION SELECT 20 UNION SELECT 19 UNION  
      SELECT 18 UNION SELECT 17 UNION SELECT 16 UNION SELECT 15 UNION SELECT 14 UNION SELECT 13 UNION  
      SELECT 12 UNION SELECT 11 UNION SELECT 10 UNION SELECT 9 UNION SELECT 8 UNION SELECT 7 UNION  
      SELECT 6 UNION SELECT 5 UNION SELECT 4 UNION SELECT 3 UNION SELECT 2 UNION SELECT 1 
    ) iter
  ) calendar
  JOIN (
    SELECT 
     s.`name`, 
     s.`time` AS _begin,
     COALESCE((SELECT s1.`time` FROM `status_log` s1 WHERE s1.`value`= 'STOPPED' AND s1.`name` = s.`name` AND s1.`time` > s.`time` ORDER BY s1.`time` LIMIT 1), NOW()) AS _end
    FROM `status_log` s
    WHERE s.value = 'ACTIVE'
  ) periods ON (calendar.`_begin` <= periods.`_begin` AND (calendar.`_end` >= periods.`_end`))
    OR (calendar.`_begin` >= periods.`_begin` AND (calendar.`_begin` <= periods.`_end`))
    OR (calendar.`_end` >= periods.`_begin` AND (calendar.`_end` <= periods.`_end`))
) _data
GROUP BY _data.`_begin`, _data.`name`

online

Answer 2

This is probably about the same as Lentyuy showed above, but I don’t think that a more efficient solution is possible

SELECT 
  timeStart, timeEnd, ROUND(IFNULL(seconds, 0) / 3600 * 100) as percent
FROM
(SELECT 
  timeStart, 
  timeEnd, 
  SUM(3600 - CASE 
    WHEN UNIX_TIMESTAMP(timeStarted) - UNIX_TIMESTAMP(timeStart) < 0 THEN 0 
    ELSE UNIX_TIMESTAMP(timeStarted) - UNIX_TIMESTAMP(timeStart) END - CASE 
    WHEN UNIX_TIMESTAMP(timeEnd) - UNIX_TIMESTAMP(timeStopped) < 0 THEN 0 
    ELSE UNIX_TIMESTAMP(timeEnd) - UNIX_TIMESTAMP(timeStopped) END) as seconds
  FROM (
  SELECT '2020-06-23 00:00:00' as timeStart, '2020-06-23 00:59:59' as timeEnd
  UNION SELECT '2020-06-23 01:00:00', '2020-06-23 01:59:59'  
  UNION SELECT '2020-06-23 02:00:00', '2020-06-23 02:59:59'
    UNION SELECT '2020-06-23 03:00:00', '2020-06-23 03:59:59'
    UNION SELECT '2020-06-23 04:00:00', '2020-06-23 04:59:59'
    UNION SELECT '2020-06-23 05:00:00', '2020-06-23 05:59:59'
    UNION SELECT '2020-06-23 06:00:00', '2020-06-23 06:59:59'
  // и т.д.
) as HoursSet
LEFT JOIN (
   SELECT MAX(IFNULL(StartedPoints.time, '2020-06-23 00:00:00')) as timeStarted, StoppedPoints.time as timeStopped FROM `stanki` as StoppedPoints 
        LEFT JOIN stanki as StartedPoints 
          ON StoppedPoints.name = StartedPoints.name AND
            StartedPoints.value = 'ACTIVE' AND 
            StoppedPoints.time > StartedPoints.time
        WHERE StoppedPoints.name = '01' AND StoppedPoints.value IN ('STOPPED', 'UNAVAILABLE')
        GROUP BY StoppedPoints.time
  UNION ALL
  SELECT time, '2020-06-24 00:00:00'
  FROM (SELECT * FROM stanki WHERE name = '01' ORDER BY time DESC LIMIT 1) as LastRecord
  WHERE value = 'ACTIVE'  
) as ActiveIntervals ON ActiveIntervals.timeStopped > HoursSet.timeStart 
  AND ActiveIntervals.timeStarted <= HoursSet.timeEnd
GROUP BY timeStart, timeEnd) as CalculatedSeconds