Answer the question
In order to leave comments, you need to log in
How to display effective machine hours by hours in 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 the question
In order to leave comments, you need to log in
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`
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
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question