Power Outages Lasting Four Hours or More — SQL Dates Problem
Under the state regulator's supply code, an outage of four hours or more on a feeder has to be explained in the monthly reliability report.
- Difficulty: Easy
- Topics: Dates, Basics
- Dialect: MySQL
- Problem: #505
Problem statement
Under the state regulator's supply code, an outage of four hours or more on a feeder has to be explained in the monthly reliability report. Outages still in progress have restored_at NULL.
Return every restored outage whose duration is at least 240 minutes, with the columns outage_id, feeder_code and duration_minutes — the whole minutes from started_at to restored_at, a partial minute dropped. Order by duration_minutes from longest to shortest, then by outage_id.
Tables
Table: Outage
| Column | Type |
|---|---|
| outage_id | int |
| feeder_code | varchar |
| started_at | datetime |
| restored_at | datetime |
Primary key: outage_id.
One row per supply interruption on an 11 kV feeder; restored_at is NULL while the outage continues.
Examples
Example 1
Outage
| outage_id | feeder_code | started_at | restored_at |
|---|---|---|---|
| 1 | BLR-F11 | 2024-07-02 09:15:00 | 2024-07-02 13:15:00 |
| 2 | BLR-F12 | 2024-07-02 22:40:00 | 2024-07-03 04:05:00 |
| 3 | BLR-F27 | 2024-07-03 11:00:00 | 2024-07-03 14:59:30 |
| 4 | MYS-F04 | 2024-07-04 06:00:00 | NULL |
| 5 | HBL-F09 | 2024-07-05 15:20:00 | 2024-07-05 16:05:00 |
| 6 | BLR-F11 | 2024-07-06 01:00:00 | 2024-07-06 06:25:00 |
Output
| outage_id | feeder_code | duration_minutes |
|---|---|---|
| 2 | BLR-F12 | 325 |
| 6 | BLR-F11 | 325 |
| 1 | BLR-F11 | 240 |
How to solve Power Outages Lasting Four Hours or More
TIMESTAMPDIFF(MINUTE, started_at, restored_at) returns the number of complete minutes between the two timestamps, so an outage of 3 hours 59 minutes 30 seconds is 239 minutes and stays out, while one of exactly four hours is 240 and is reported — the >= matters.
Ongoing outages have restored_at NULL. Any function of NULL is NULL and a NULL comparison is never true, so they would drop out even without the explicit IS NOT NULL; writing it anyway documents the intent.
The second query reaches the same rows without computing a duration in the filter: restored_at >= DATE_ADD(started_at, INTERVAL 240 MINUTE) is exactly "at least four hours later", and whole seconds divided by 60 and rounded down with FLOOR gives the same truncated minutes. Equal durations are ordered by outage_id. One pass over the table.
Reference solution (MySQL)
SELECT outage_id, feeder_code, TIMESTAMPDIFF(MINUTE, started_at, restored_at) AS duration_minutes
FROM Outage
WHERE restored_at IS NOT NULL
AND TIMESTAMPDIFF(MINUTE, started_at, restored_at) >= 240
ORDER BY duration_minutes DESC, outage_idAnother way
SELECT outage_id, feeder_code, FLOOR(TIMESTAMPDIFF(SECOND, started_at, restored_at) / 60) AS duration_minutes
FROM Outage
WHERE restored_at >= DATE_ADD(started_at, INTERVAL 240 MINUTE)
ORDER BY duration_minutes DESC, outage_id← Electricity Bills Paid After the Due Date · Slab Tariff Energy Charges →