SQL中按班次规则计算工单起止时间差的实现需求

Alright, let's break down how to compute the total time each work order falls within the specified shift hours from your second table. First, let's clarify the table structures with properly formatted data:

Table 1: Work Orders (work_orders) #

start_date           | end_date
---------------------|---------------------
2017-02-21 07:52:32  | 2017-02-22 09:56:32
2017-02-21 09:52:32  | 2017-02-23 17:52:32

(Note: I converted the dd-mm-yyyy format to standard ISO date format for easier datetime calculations—adjust if your database uses a different default format.)

Table 2: Shift Details (shifts) #

shift_day | start_time | end_time
----------|------------|----------
MON       | 09:00:00   | 18:00:00
TUE       | 10:00:00   | 19:00:00
WED       | 09:00:00   | 18:00:00
THU       | 10:00:00   | 19:00:00
FRI       | 09:00:00   | 18:00:00

Core Approach #

The key idea is to split each work order's time range into individual days, match each day to its corresponding shift, calculate the overlapping time between the work order's hours that day and the shift hours, then sum all those overlaps for the final total.

SQL Implementation (PostgreSQL Example) #

Here's a step-by-step query using common table expressions (CTEs) to handle this:

WITH date_ranges AS (
    -- Generate every date between the work order's start and end date
    SELECT
        -- Add an order ID (use ROW_NUMBER() if your table doesn't have a unique ID)
        ROW_NUMBER() OVER () AS order_id,
        wo.start_date,
        wo.end_date,
        generate_series(
            DATE(wo.start_date),
            DATE(wo.end_date),
            INTERVAL '1 day'
        )::DATE AS current_date
    FROM work_orders wo
),
daily_shift_matches AS (
    -- Join each date to its shift and create full datetime ranges for the shift
    SELECT
        dr.order_id,
        dr.current_date,
        dr.start_date AS order_start,
        dr.end_date AS order_end,
        -- Combine current date with shift start/end times to get full timestamps
        (dr.current_date || ' ' || s.start_time)::TIMESTAMP AS shift_start,
        (dr.current_date || ' ' || s.end_time)::TIMESTAMP AS shift_end
    FROM date_ranges dr
    -- Match the day of the week to shift_day (adjust 'DY' if your DB uses different abbreviations)
    JOIN shifts s ON TO_CHAR(dr.current_date, 'DY') = s.shift_day
),
overlap_calculations AS (
    -- Calculate the overlapping interval for each day
    SELECT
        order_id,
        -- Overlap starts at the later of the work order's start that day or shift start
        GREATEST(
            CASE WHEN dr.current_date = DATE(dr.order_start) THEN dr.order_start ELSE dr.current_date::TIMESTAMP END,
            dr.shift_start
        ) AS overlap_start,
        -- Overlap ends at the earlier of the work order's end that day or shift end
        LEAST(
            CASE WHEN dr.current_date = DATE(dr.order_end) THEN dr.order_end ELSE (dr.current_date + INTERVAL '1 day')::TIMESTAMP END,
            dr.shift_end
        ) AS overlap_end
    FROM daily_shift_matches dr
)
-- Sum all valid overlaps to get total eligible time per work order
SELECT
    order_id,
    SUM(
        CASE 
            WHEN overlap_end > overlap_start THEN overlap_end - overlap_start 
            ELSE INTERVAL '0 seconds' 
        END
    ) AS total_eligible_time
FROM overlap_calculations
GROUP BY order_id;

Key Notes #

• Day Matching: The TO_CHAR(dr.current_date, 'DY') ensures we match each date to the correct shift day. For other databases (like MySQL), use DAYNAME() instead, and adjust the output to match your shifts table's 3-letter weekday abbreviations.
• Partial Days: The CASE statements handle edge cases where a work order starts before the shift begins or ends after the shift ends, only counting time that falls within the shift window for that day.
• Weekends: If a work order spans Saturday/Sunday, those days won't have a matching shift, so their overlap time will be 0 and won't contribute to the total.

内容的提问来源于stack exchange，提问作者Srinivas Pusapati