Validating 168-Hour Weekly Rest with SQL Window Functions

The exact task this guide solves is confirming, for every duty a crew member is assigned, that a qualifying long rest — at least 30 consecutive hours free from all duty — exists somewhere in the preceding 168 hours, exactly as §117.25(b) requires. This is the check a single-gap rest validator cannot see: a roster can satisfy every minimum-rest-before-duty comparison and still be illegal because no gap in the trailing week was long enough to count as the weekly rest. The rolling nature of the rule makes it far cleaner to express as a SQL window function than to hand-roll in application code. This page builds that query, handles the boundary cases, and reports the offending duties. It extends the rest period compliance checks and shares the rolling-window discipline of the wider duty time validation rule engines.

Prerequisites

• PostgreSQL 14+ for interval-bounded window frames.
• A rest_period view carrying each rest’s crew id, end instant, and duration in hours, derived from consecutive duties.
• A duty_period table with UTC report instants.
• The §117.25(b) parameters seeded — a 30-hour qualifying rest over a 168-hour window.

Why the weekly-rest rule needs a rolling window

The rule is defined over any 168 consecutive hours, not a calendar week, so the check must ask, at each duty, whether the trailing week contained a rest of at least 30 hours. A calendar-week query aligned to Monday would pass a roster whose only long rest fell just outside the Monday boundary of the week that matters. The correct formulation anchors a 168-hour window at each duty’s report instant and takes the longest qualifying rest that ends within it.

Step 1 — Find the longest trailing rest per duty

A window function partitioned by crew, ordered by report instant, with a RANGE frame of 168 hours, computes the longest rest ending in each duty’s trailing week in a single pass.

SELECT
    d.crew_id,
    d.duty_id,
    d.report_utc,
    MAX(r.rest_hours) OVER (
        PARTITION BY d.crew_id
        ORDER BY d.report_utc
        RANGE BETWEEN INTERVAL '168 hours' PRECEDING AND CURRENT ROW
    ) AS longest_rest_in_window
FROM duty_period AS d
JOIN rest_period AS r
    ON r.crew_id = d.crew_id
   AND r.ends_utc <= d.report_utc
   AND r.ends_utc > d.report_utc - INTERVAL '168 hours';

Verify: a crew member whose longest trailing rest is 32 hours shows longest_rest_in_window = 32; one whose longest is 24 hours shows 24, which is the value the next step tests against the threshold.

Step 2 — Flag the weekly-rest violations

Wrapping the window query and comparing against the 30-hour threshold turns the longest-rest figure into a verdict. Duties whose trailing week held no 30-hour rest are the violations.

WITH windowed AS (
    SELECT
        d.crew_id, d.duty_id, d.report_utc,
        MAX(r.rest_hours) OVER (
            PARTITION BY d.crew_id ORDER BY d.report_utc
            RANGE BETWEEN INTERVAL '168 hours' PRECEDING AND CURRENT ROW
        ) AS longest_rest_in_window
    FROM duty_period AS d
    JOIN rest_period AS r
      ON r.crew_id = d.crew_id
     AND r.ends_utc <= d.report_utc
     AND r.ends_utc > d.report_utc - INTERVAL '168 hours'
)
SELECT crew_id, duty_id, report_utc, longest_rest_in_window
FROM windowed
WHERE longest_rest_in_window IS NULL         -- no rest at all in the window
   OR longest_rest_in_window < 30;           -- no qualifying long rest

Verify: the query returns exactly the duties whose preceding 168 hours lacked a 30-hour rest; a duty with a 30.0-hour rest in its window does not appear, and one with a 29.9-hour rest does.

Step 3 — Handle the empty-window boundary

A duty at the very start of a crew member’s history has no prior rest in its window, so the MAX is NULL. Treating NULL as a pass would silently exempt the first duties of every roster; treating it as a violation would flag legitimate history gaps. The correct behaviour is to distinguish “no data yet” from “data shows no qualifying rest”, which the IS NULL branch above surfaces explicitly for review rather than silently deciding.

-- Classify the boundary so it is reviewed, not silently passed or failed.
SELECT crew_id, duty_id,
       CASE
           WHEN longest_rest_in_window IS NULL THEN 'insufficient_history'
           WHEN longest_rest_in_window < 30    THEN 'weekly_rest_violation'
           ELSE 'compliant'
       END AS weekly_rest_status
FROM windowed;

Failure modes and troubleshooting

• Calendar week instead of rolling window. A Monday-aligned week can pass while a rolling 168-hour window fails. Remediation: use a RANGE frame anchored on each duty, not a date_trunc('week', …).
• NULL treated as pass. An empty window returns NULL, and coalescing it to a large number silently exempts the first duties. Remediation: classify NULL as insufficient_history for review.
• Rest ending on the boundary dropped. A rest ending exactly 168 hours before the report sits on the frame edge; an exclusive join bound drops it. Remediation: make the trailing bound inclusive and test the boundary rest.
• Summing instead of maxing. The weekly rule needs the longest single rest, not the total; SUM would pass a week of many short rests. Remediation: use MAX(rest_hours).
• Local-time ordering. Ordering the window on local time lets a date-line crossing reorder duties. Remediation: order on the UTC report instant.

Frequently Asked Questions

Why a window function instead of application code?

Because the rule evaluates every duty against its own trailing week, which a single ordered pass with a RANGE frame does in the database far more efficiently than fetching each crew’s history and looping in Python. The window function also keeps the boundary semantics explicit and testable.

Does the weekly rest have to be a single block?

Yes. §117.25(b) requires 30 consecutive hours free from duty, so the check takes the longest single rest in the window, not the sum of several. Two 20-hour rests do not satisfy it.

How is a crew member’s first week handled?

Their earliest duties have no prior rest in the window, producing a NULL longest rest. That is classified as insufficient history and surfaced for review rather than being treated as either a pass or a violation.

Can this run inside a pairing optimiser?

The same rolling logic runs in Polars for in-process evaluation, but the SQL form is ideal for the nightly compliance sweep where the whole roster is already in the database. Both read the same 30-hour, 168-hour parameters.

Related

• Rest Period Compliance Checks — the parent section and the single-gap rest checks this complements.
• Handling UTC and DST Edge Cases in Flight Time Math — why the window must run on UTC.
• Cumulative Duty and Flight-Time Caps Reference — the sibling rolling-window caps.
• FAA Part 117 Rule Schema Design — the §117.25 rest provisions.

Back to Rest Period Compliance Checks.