Power Outages Lasting Four Hours or More

Easy
505.

Power Outages Lasting Four Hours or More

EasyDatesBasics

MySQL

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

ColumnType
outage_idint
feeder_codevarchar
started_atdatetime
restored_atdatetime

Primary key: outage_id.

One row per supply interruption on an 11 kV feeder; restored_at is NULL while the outage continues.

Example

The rows must come back in the order shown.

Outage

outage_idfeeder_codestarted_atrestored_at
1BLR-F112024-07-02 09:15:002024-07-02 13:15:00
2BLR-F122024-07-02 22:40:002024-07-03 04:05:00
3BLR-F272024-07-03 11:00:002024-07-03 14:59:30
4MYS-F042024-07-04 06:00:00NULL
5HBL-F092024-07-05 15:20:002024-07-05 16:05:00
6BLR-F112024-07-06 01:00:002024-07-06 06:25:00

Output

outage_idfeeder_codeduration_minutes
2BLR-F12325
6BLR-F11325
1BLR-F11240
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
outage_idint
feeder_codevarchar
started_atdatetime
restored_atdatetime

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_idfeeder_codestarted_atrestored_at
1BLR-F112024-07-02 09:15:002024-07-02 13:15:00
2BLR-F122024-07-02 22:40:002024-07-03 04:05:00
3BLR-F272024-07-03 11:00:002024-07-03 14:59:30
4MYS-F042024-07-04 06:00:00NULL
5HBL-F092024-07-05 15:20:002024-07-05 16:05:00
6BLR-F112024-07-06 01:00:002024-07-06 06:25:00

Output

outage_idfeeder_codeduration_minutes
2BLR-F12325
6BLR-F11325
1BLR-F11240

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_id

Another 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 →