SQL Machine Downtime Analysis: Finding Where Production Stops

Learn how to analyze machine downtime, calculate downtime duration, identify major production losses and investigate failure causes with PostgreSQL

What happens when a production line stops?

In a Smart Factory, production data tells us what was produced. But when output falls behind plan, the next question is more important:

Where did we lose production time?

In this article, we will use PostgreSQL and a realistic manufacturing dataset to investigate machine downtime.

We will move from a simple downtime event to a real industrial analysis:

downtime events → duration → planned vs unplanned → causes → machines → production lines → investigation

---

1. The industrial question

A downtime record is more than a timestamp. It tells us that a machine stopped, for how long, why it stopped, and whether the interruption was planned.

Our database stores this information in smart_factory.downtime_events.

The table contains:

• machine_id — the affected machine
• order_id — the related production order, when available
• start_time and end_time — the interruption period
• reason_category — the main cause
• reason_code — a more precise code
• planned — whether the downtime was planned

The schema confirms that an end time cannot be earlier than the start time, which makes duration calculations safe for this training dataset.

---

2. First question: how much downtime do we have?

We first calculate the duration of every downtime event.

SELECT
    downtime_id,
    machine_id,
    reason_category,
    planned,
    ROUND(
        EXTRACT(EPOCH FROM (end_time - start_time)) / 60,
        2
    ) AS downtime_minutes
FROM smart_factory.downtime_events
ORDER BY downtime_minutes DESC;

PostgreSQL's EXTRACT(EPOCH FROM ...) gives us the duration in seconds. Dividing by 60 converts it to minutes.

This is a very common pattern in industrial SQL because machine events are usually stored with timestamps rather than a pre-calculated duration.

---

3. Planned vs unplanned downtime

Not every stop represents the same operational problem.

A scheduled setup, cleaning operation or preventive maintenance is different from an unexpected mechanical failure or material shortage.

SELECT
    CASE
        WHEN planned THEN 'Planned'
        ELSE 'Unplanned'
    END AS downtime_type,
    COUNT(*) AS downtime_events,
    ROUND(
        SUM(EXTRACT(EPOCH FROM (end_time - start_time))) / 60,
        2
    ) AS downtime_minutes
FROM smart_factory.downtime_events
GROUP BY planned
ORDER BY downtime_minutes DESC;

For the current dataset, the result is:

Downtime type	Events	Total minutes
Planned	52	1,059
Unplanned	30	1,010

This is an interesting result: although planned downtime has more events, unplanned downtime represents almost the same amount of lost time.

That immediately gives us an industrial investigation target.

---

4. What causes the downtime?

Now we group downtime by its industrial cause.

SELECT
    reason_category,
    COUNT(*) AS downtime_events,
    ROUND(
        SUM(EXTRACT(EPOCH FROM (end_time - start_time))) / 60,
        2
    ) AS total_downtime_minutes,
    ROUND(
        AVG(EXTRACT(EPOCH FROM (end_time - start_time))) / 60,
        2
    ) AS avg_downtime_minutes
FROM smart_factory.downtime_events
GROUP BY reason_category
ORDER BY total_downtime_minutes DESC;

The dataset produces:

Cause	Events	Total minutes	Average minutes
Setup	30	636	21.20
Mechanical Failure	5	372	74.40
Material Shortage	13	363	27.92
Electrical Failure	12	275	22.92
Maintenance	11	230	20.91
Cleaning	11	193	17.55

There is an important distinction here.

Setup creates the largest total downtime, but mechanical failures are much more severe per event.

Only five mechanical-failure events account for 372 minutes of downtime, with an average duration of 74.4 minutes.

---

5. Which production line is losing the most time?

Industrial analysis becomes much more useful when we connect downtime to the physical factory.

We therefore join downtime_events with machines and production_lines.

SELECT
    pl.line_code,
    pl.line_name,
    COUNT(d.downtime_id) AS downtime_events,
    ROUND(
        SUM(EXTRACT(EPOCH FROM (d.end_time - d.start_time))) / 60,
        2
    ) AS downtime_minutes
FROM smart_factory.downtime_events d
JOIN smart_factory.machines m
    ON m.machine_id = d.machine_id
JOIN smart_factory.production_lines pl
    ON pl.line_id = m.line_id
GROUP BY
    pl.line_code,
    pl.line_name
ORDER BY downtime_minutes DESC;

The result is:

Line	Line name	Events	Downtime minutes
L02	Heavy Machining Line	38	1,124
L03	Final Assembly & Packaging	29	625
L01	Precision Assembly Line	15	320

L02 is clearly the first line to investigate.

---

6. Which machine is the biggest contributor?

Let's go one level deeper.

SELECT
    m.machine_code,
    m.machine_name,
    pl.line_code,
    COUNT(d.downtime_id) AS downtime_events,
    ROUND(
        SUM(EXTRACT(EPOCH FROM (d.end_time - d.start_time))) / 60,
        2
    ) AS downtime_minutes,
    ROUND(
        AVG(EXTRACT(EPOCH FROM (d.end_time - d.start_time))) / 60,
        2
    ) AS avg_downtime_minutes
FROM smart_factory.downtime_events d
JOIN smart_factory.machines m
    ON m.machine_id = d.machine_id
JOIN smart_factory.production_lines pl
    ON pl.line_id = m.line_id
GROUP BY
    m.machine_code,
    m.machine_name,
    pl.line_code
ORDER BY downtime_minutes DESC;

The top machines are:

Machine	Line	Events	Total minutes	Average minutes
M005 — CNC Machine B2	L02	10	511	51.10
M007 — Grinding Machine B4	L02	18	363	20.17
M011 — Packaging Machine C4	L03	7	146	20.86
M012 — Labeling Machine C5	L03	7	143	20.43

M005 is the first machine we should investigate.

It accounts for 511 minutes of downtime by itself — more than the total downtime of the entire L01 line.

---

7. Investigating M005

Now SQL becomes an investigation tool rather than just a reporting language.

SELECT
    reason_category,
    reason_code,
    COUNT(*) AS events,
    ROUND(
        SUM(EXTRACT(EPOCH FROM (end_time - start_time))) / 60,
        2
    ) AS downtime_minutes,
    ROUND(
        AVG(EXTRACT(EPOCH FROM (end_time - start_time))) / 60,
        2
    ) AS avg_minutes
FROM smart_factory.downtime_events
WHERE machine_id = 5
GROUP BY reason_category, reason_code
ORDER BY downtime_minutes DESC;

For M005, the data shows:

Cause	Code	Events	Total minutes	Average
Mechanical Failure	SPINDLE-VIB	5	372	74.40
Material Shortage	MAT-01	1	41	41.00
Maintenance	PM-01	1	38	38.00
Electrical Failure	ELEC-01	2	52	26.00
Cleaning	CLN-01	1	8	8.00

The signal is now very clear:

M005 has a concentrated mechanical-downtime problem associated with spindle vibration.

The five mechanical failures alone represent 372 minutes of downtime.

And the longest event lasted 95 minutes.

---

8. Find the longest downtime events

When investigating production losses, the largest individual events are also useful.

SELECT
    d.downtime_id,
    m.machine_code,
    pl.line_code,
    d.reason_category,
    d.reason_code,
    ROUND(
        EXTRACT(EPOCH FROM (d.end_time - d.start_time)) / 60,
        2
    ) AS downtime_minutes,
    d.start_time,
    d.end_time
FROM smart_factory.downtime_events d
JOIN smart_factory.machines m
    ON m.machine_id = d.machine_id
JOIN smart_factory.production_lines pl
    ON pl.line_id = m.line_id
WHERE NOT d.planned
ORDER BY downtime_minutes DESC
LIMIT 10;

The longest unplanned event in the dataset is a 95-minute mechanical failure on M005, caused by spindle vibration.

Other major events on the same machine reach 91 and 83 minutes.

That is much more actionable than simply saying:

"The factory had downtime."

We now know where, how long, and why.

---

9. From SQL query to industrial decision

Our investigation produced a simple chain of evidence:

1. L02 has the highest total downtime: 1,124 minutes.
2. M005 is the largest contributor: 511 minutes.
3. Mechanical failures account for 372 minutes on M005.
4. The recurring reason code is SPINDLE-VIB.
5. The longest event lasted 95 minutes.

This is the real value of Industrial SQL.

We did not simply retrieve rows from a database. We used relational data to narrow a production problem from the factory level to a specific machine and failure mode.

---

10. A reusable machine downtime query

The database already includes a view called smart_factory.v_machine_downtime that summarizes downtime by machine and line.

SELECT
    machine_code,
    machine_name,
    line_code,
    downtime_events,
    downtime_minutes,
    avg_downtime_minutes
FROM smart_factory.v_machine_downtime
ORDER BY downtime_minutes DESC;

This is a useful next step in a real PostgreSQL environment: once an analytical query is stable and frequently reused, it can become a view that supports dashboards and reporting.

---

11. What we learned

• How to calculate event duration with PostgreSQL timestamps.
• How to distinguish planned and unplanned downtime.
• How to aggregate downtime by cause.
• How to join events with machines and production lines.
• How to identify the machines responsible for the largest losses.
• How to drill down from a production line to a specific failure mode.
• How SQL can support an actual manufacturing investigation.

---

12. Your challenge

Try to answer these questions with SQL:

1. Which machine has the highest unplanned downtime?
2. Which reason category has the highest average unplanned downtime?
3. Which production line has the highest number of unplanned events?
4. Which machines have at least 100 minutes of unplanned downtime?
5. How many unplanned downtime events lasted more than 30 minutes?

These questions will prepare us for the next level: connecting downtime with production performance and maintenance history.

---

Continue the Smart Factory SQL series

New to the series? Start with the previous article:

SQL Production Analytics: Planned vs Actual Production

Next, we will investigate the relationship between machine downtime and maintenance events.

Because in a Smart Factory, a downtime event is rarely the end of the story. It is usually the beginning of an investigation.

Dataset used in this tutorial: Smart Factory SQL — a synthetic but coherent manufacturing environment designed for PostgreSQL and Industrial SQL training.