How to calculate Equipment Downtime Rate

Formula

Downtime rate = unplanned downtime hours ÷ scheduled production hours

SQL query

This example assumes: machine_hours(machine_id bigint, day date, scheduled_hours numeric, downtime_hours numeric). It is written in PostgreSQL syntax; date functions differ slightly in MySQL, BigQuery and SQLite, and your column names will differ from these.

SELECT machine_id,
       SUM(downtime_hours) AS downtime_hours,
       SUM(scheduled_hours) AS scheduled_hours,
       SUM(downtime_hours) / NULLIF(SUM(scheduled_hours), 0) AS downtime_rate
FROM machine_hours
WHERE day >= CURRENT_DATE - 30
GROUP BY machine_id
ORDER BY downtime_rate DESC;

Common mistakes that make Equipment Downtime Rate wrong

• Planned maintenance is usually excluded from unplanned downtime; define which one you report.
• Machines scheduled for different hours should be compared as rates, not total hours.
• Short stoppages that nobody logs make the number look better than it is.

Ask for Equipment Downtime Rate in plain English

Instead of writing the query yourself, you can type the question. SchemaChat turns it into SQL against your connected database or uploaded file, runs it, and shows the SQL and the figures it used so you can check the answer before you share it. It does not replace your judgement about the definition: check that the query matches the formula your business uses.

• What was the downtime rate for each machine over the last 30 days?
• Which machines lost the most hours to unplanned downtime?