如何基于Start_Time和End_Time将单行数据拆分为多行（SQL实现）

To split time intervals at daily 7:00 AM and calculate corresponding downtime minutes, we use a recursive CTE to generate split points, then compute the duration for each segment. Below is a practical implementation:

SQL Query #

WITH split_points AS (
    SELECT 
        ID,
        Start_Time,
        End_Time,
        -- Get first 7:00 AM after Start_Time
        CASE 
            WHEN Start_Time > DATE_TRUNC('day', Start_Time) + INTERVAL '7 hours'
            THEN DATE_TRUNC('day', Start_Time) + INTERVAL '1 day 7 hours'
            ELSE DATE_TRUNC('day', Start_Time) + INTERVAL '7 hours'
        END AS split_time,
        Start_Time AS prev_split
    FROM your_table
    WHERE End_Time > Start_Time -- Skip invalid rows where end <= start

    UNION ALL

    SELECT 
        ID,
        Start_Time,
        End_Time,
        split_time + INTERVAL '1 day' AS split_time,
        split_time AS prev_split
    FROM split_points
    WHERE split_time + INTERVAL '1 day' < End_Time
),
intervals AS (
    -- First segment: original start to first split
    SELECT ID, prev_split AS segment_start, split_time AS segment_end
    FROM split_points
    WHERE prev_split = Start_Time

    UNION ALL

    -- Middle segments: consecutive split points
    SELECT ID, prev_split AS segment_start, split_time AS segment_end
    FROM split_points
    WHERE prev_split != Start_Time

    UNION ALL

    -- Last segment: final split to original end
    SELECT ID, split_time AS segment_start, End_Time AS segment_end
    FROM split_points
    WHERE split_time + INTERVAL '1 day' >= End_Time
)
SELECT 
    ID,
    segment_start AS Start_Time,
    segment_end AS End_Time,
    ROUND(EXTRACT(EPOCH FROM (segment_end - segment_start)) / 60, 2) AS Down_Mins
FROM intervals
ORDER BY ID, segment_start;

How It Works #

1. Recursive Split Points:

   • Base case: Calculates the first 7:00 AM after the original start time (next day if start is past 7 AM).
   • Recursive step: Adds 24 hours to each split point until the next split exceeds the original end time.

2. Interval Construction:

   • Combines the original start, all split points, and original end to form full segments:
     • Initial segment from start time to first split.
     • Middle segments between consecutive splits (exactly 24 hours = 1440 minutes).
     • Final segment from last split to end time.

3. Duration Calculation:

   • Converts time differences to seconds, divides by 60 to get minutes, and rounds to two decimals for precision matching the example.

Dialect Adjustments #

• SQL Server:

  • Replace DATE_TRUNC('day', ...) with CAST(Start_Time AS DATE)
  • Replace INTERVAL 'X hours' with DATEADD(HOUR, X, ...)
  • Replace EXTRACT(EPOCH FROM ...) with DATEDIFF(SECOND, segment_start, segment_end) / 60.0

• MySQL:

  • Replace DATE_TRUNC('day', ...) with DATE(Start_Time)
  • Replace INTERVAL '7 hours' with INTERVAL 7 HOUR
  • Replace EXTRACT(EPOCH FROM ...) with TIMESTAMPDIFF(SECOND, segment_start, segment_end) / 60.0

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