Calculating Time Difference in Minutes with Hourly Segmentation in SQL Server

Fundamental Concepts of Time Difference Calculation

In database applications, calculating the difference between two time points is a common requirement. SQL Server provides the DATEDIFF function for such calculations, but when minute statistics segmented by hours are needed, the problem becomes more complex.

DATEDIFF Function Basics

The DATEDIFF function is the core function in SQL Server for calculating date and time differences. Its basic syntax is:

DATEDIFF(datepart, startdate, enddate)

For minute-level calculations, you can use:

SELECT DATEDIFF(MINUTE, '11:15:00', '13:15:00') AS TotalMinutes

This returns the total minutes of 120, but cannot provide results segmented by hour.

Core Logic of Segmented Calculation

To achieve time difference calculations segmented by hour, the following key components need to be analyzed:

• Remaining minutes of the starting hour: 60 - DATEPART(MINUTE, StartTime)
• Minutes of intermediate complete hours: 60 minutes for each complete hour
• Minutes of the ending hour: DATEPART(MINUTE, EndTime)

Implementing Segmented Calculation Using CASE Expressions

By combining DATEDIFF and CASE expressions, segmented calculation logic can be constructed:

SELECT 
    Diff = CASE DATEDIFF(HOUR, StartTime, EndTime)
            WHEN 0 THEN CAST(DATEDIFF(MINUTE, StartTime, EndTime) AS VARCHAR(10))
            ELSE CAST(60 - DATEPART(MINUTE, StartTime) AS VARCHAR(10)) +
                REPLICATE(',60', DATEDIFF(HOUR, StartTime, EndTime) - 1) + 
                ',' + CAST(DATEPART(MINUTE, EndTime) AS VARCHAR(10))
          END
FROM (VALUES 
    (CAST('11:15' AS TIME), CAST('13:15' AS TIME)),
    (CAST('10:45' AS TIME), CAST('18:59' AS TIME))
) t (StartTime, EndTime)

Multi-Column Output Solution

For scenarios requiring results to be divided into multiple columns, multiple CASE expressions can be used:

SELECT 
    [0] = CASE WHEN DATEDIFF(HOUR, StartTime, EndTime) = 0
                THEN DATEDIFF(MINUTE, StartTime, EndTime)
              ELSE 60 - DATEPART(MINUTE, StartTime)
          END,
    [1] = CASE WHEN DATEDIFF(HOUR, StartTime, EndTime) = 1 
                THEN DATEPART(MINUTE, EndTime)
              WHEN DATEDIFF(HOUR, StartTime, EndTime) > 1 THEN 60
          END
FROM (VALUES 
    (CAST('11:15' AS TIME), CAST('13:15' AS TIME))
) t (StartTime, EndTime)

Optimized Solution Using PIVOT

To avoid repeating CASE expressions, a combination of number table and PIVOT can be employed:

WITH Numbers (Number) AS (
    SELECT ROW_NUMBER() OVER(ORDER BY t1.N) - 1
    FROM (VALUES (1), (1), (1), (1), (1), (1)) AS t1 (N)
    CROSS JOIN (VALUES (1), (1), (1), (1)) AS t2 (N)
),
YourData AS (
    SELECT StartTime, EndTime
    FROM (VALUES 
        (CAST('11:15' AS TIME), CAST('13:15' AS TIME))
    ) AS t (StartTime, EndTime)
),
PivotData AS (
    SELECT t.StartTime, t.EndTime, n.Number,
        MinuteDiff = CASE 
            WHEN n.Number = 0 AND DATEDIFF(HOUR, StartTime, EndTime) = 0 
                THEN DATEDIFF(MINUTE, StartTime, EndTime)
            WHEN n.Number = 0 THEN 60 - DATEPART(MINUTE, StartTime)
            WHEN DATEDIFF(HOUR, t.StartTime, t.EndTime) <= n.Number 
                THEN DATEPART(MINUTE, EndTime)
            ELSE 60
        END
    FROM YourData AS t
    INNER JOIN Numbers AS n
        ON n.Number <= DATEDIFF(HOUR, StartTime, EndTime)
)
SELECT *
FROM PivotData AS d
PIVOT (
    MAX(MinuteDiff)
    FOR Number IN ([0], [1], [2], [3], [4], [5])
) AS pvt

Practical Application Scenarios

This segmented calculation method is particularly useful in the following scenarios:

• Work time statistics: Counting employee working hours by hour
• Resource usage analysis: Analyzing equipment usage across different time periods
• Business period statistics: Counting transaction volumes in different business periods

Performance Optimization Recommendations

When processing large amounts of data, it is recommended to:

• Establish appropriate indexes for time fields
• Avoid function operations on time fields in WHERE clauses
• Consider using computed columns to pre-store hour difference information

Compatibility Considerations

The above methods can be used in SQL Server 2008 and later versions. For earlier versions, time processing methods may need adjustment since the TIME data type was introduced in SQL Server 2008.