Calculating minutes between two points in SQL

04/30/2012 -
I use a lot of SQL functions on daily basis. Some are in-built and come with the SQL Server, some I have to write. The function I'm going to present today is something that I needed for one of my projects and I couldn't find anything similar on the web. It turned out that it does it's job and more.

Let's look at the following example: you have an air traffic controller who worked for 34 hours continuously and you want to calculate how many hours were "overtime", or outside 08:00 - 16:00 period. If he started working at Jan 1st 2012 07:00 and finished his shift on Jan 2nd 2012 17:00. Performing this calculation manually, you would get that he worked 1080 minutes (18 hours) overtime - 9 hours on the first day, and 9 hours on the second day. The function I am going to show you calculates this:
CREATE FUNCTION [dbo].[GetMinuteCount]
(
    -- Add the parameters for the function here
    @StartTime DateTime,
    @StopTime DateTime,
    @StartTimeFilter varchar(10),
    @StopTimeFilter varchar(10)
)
RETURNS int
AS
BEGIN
    DECLARE @MinuteCount int
    SET @MinuteCount = 0
 
 
    -- Start & End time for the first day
    DECLARE @StartTime1 DateTime
    DECLARE @StopTime1 DateTime
 
    -- Start & End time for the last day
    DECLARE @StartTime2 DateTime
    DECLARE @StopTime2 DateTime
 
    DECLARE @DateCounter DateTime
    SET @DateCounter = DATEADD(d, 1, @StartTime)
 
 
    SET @StartTime1 = CONVERT(DateTime, (CONVERT(nvarchar(10), @StartTime , 101) + ' ' + @StartTimeFilter))
    SET @StartTime2 = CONVERT(DateTime, (CONVERT(nvarchar(10), @StopTime , 101) + ' ' + @StartTimeFilter))
 
    IF @StopTimeFilter = '24:00'
        SET @StopTime1 = DATEADD(day, 1, CONVERT(DateTime, (CONVERT(nvarchar(10), @StartTime , 101))))
    ELSE
        SET @StopTime1 = CONVERT(DateTime, (CONVERT(nvarchar(10), @StartTime , 101) + ' ' + @StopTimeFilter))
 
 
    IF @StopTimeFilter = '24:00'
        SET @StopTime2 = DATEADD(day, 1, CONVERT(DateTime, (CONVERT(nvarchar(10), @StopTime , 101))))
    ELSE
        SET @StopTime2 = CONVERT(DateTime, (CONVERT(nvarchar(10), @StopTime , 101) + ' ' + @StopTimeFilter))
 
     
    IF @StartTime1 > @StopTime1
            RETURN
                dbo.GetMinuteCount(@StartTime, @StopTime, '00:00', @StopTimeFilter)
                +
                dbo.GetMinuteCount(@StartTime, @StopTime, @StartTimeFilter, '24:00')
                 
 
    -- Easy part: both start and stop time are are within the same day
    IF (SELECT DATEDIFF(d, @StartTime, @StopTime)) = 0
    BEGIN
        IF @StartTime <= @StartTime1
            SET @StartTime = @StartTime1
             
        IF @StopTime >= @StopTime1
            SET @StopTime = @StopTime1
         
        SET @MinuteCount = DATEDIFF(minute, @StartTime, @StopTime)
        IF @MinuteCount < 0 SET @MinuteCount = 0
    END
 
 
    ELSE IF (SELECT DATEDIFF(d, @StartTime, @StopTime)) > 0
    BEGIN
        -- Add minutes from the first day
        IF @StartTime <= @StartTime1
            SET @StartTime = @StartTime1
             
        IF DATEDIFF(minute, @StartTime, @StopTime1) > 0
            SET @MinuteCount = DATEDIFF(minute, @StartTime, @StopTime1)
 
        -- Add minutes from the rest of days
        WHILE (SELECT DATEDIFF(d, @DateCounter, @StopTime)) >= 0
        BEGIN
            IF (SELECT DATEDIFF(d, @DateCounter, @StopTime)) > 0
                -- Add minutes from @StartTimeFilter to @StopTimeFilter
                SET @MinuteCount = @MinuteCount
                    + DATEDIFF
                      (
                        minute,
                        CONVERT(DateTime, (CONVERT(nvarchar(10), @DateCounter , 101) + ' ' + @StartTimeFilter)),
                        CASE @StopTimeFilter
                            WHEN '24:00'
                                THEN DATEADD(Day, 1, CONVERT(DateTime, (CONVERT(nvarchar(10), @DateCounter , 101))))
                            ELSE
                                CONVERT(DateTime, (CONVERT(nvarchar(10), @DateCounter , 101) + ' ' + @StopTimeFilter))
                        END
                      )
                       
            ELSE
            BEGIN
                IF @StopTime >= @StopTime2
                    SET @StopTime = @StopTime2
                     
                IF @StopTime BETWEEN @StartTime2 AND @StopTime2
                    SET @MinuteCount = @MinuteCount + DATEDIFF(minute, @StartTime2, @StopTime)
            END
             
            SET @DateCounter = DATEADD(d, 1, @DateCounter)
 
        END
    END
     
    RETURN @MinuteCount
END

It accepts 4 parameters:
- @StartTime
- @StopTime
- @StartTimeFilter
- @StopTimeFilter

In our example, @StartTime and @StopTime parameters represent the beggining and the end of our air traffic controller shift. @StartTimeFilter and @StopTimeFilter parameters represent the "time-frame" we want our calculation to be performed for.
Let's first declare the @StartTime & @StopTime and assign the values:
DECLARE @StartTime DateTime
DECLARE @StopTime DateTime
SET @StartTime = '2012-05-01 08:00'
SET @StopTime = '2012-05-02 16:00'

Now, let's create 3 groups of "time-window" parameters (work.time, over-time and total):
DECLARE @StartTimeFilterWorkHours varchar(5)
DECLARE @StopTimeFilterWorkHours varchar(5)
SET @StartTimeFilterWorkHours = '08:00'
SET @StopTimeFilterWorkHours = '16:00'
 
DECLARE @StartTimeFilterOvertime varchar(5)
DECLARE @StopTimeFilterOvertime varchar(5)
SET @StartTimeFilterOvertime = '16:00'
SET @StopTimeFilterOvertime = '08:00'
 
DECLARE @StartTimeFilterTotal varchar(5)
DECLARE @StopTimeFilterTotal varchar(5)
SET @StartTimeFilterTotal = '00:00'
SET @StopTimeFilterTotal = '24:00'

Let's look at the results:
Minutes during work hours (08:00 - 16:00):
SELECT dbo.GetMinuteCountTEST(@StartTime, @StopTime, @StartTimeFilterWorkHours, @StopTimeFilterWorkHours) AS MinutesWorkHours
This returns 960 minutes (16 hours)

Overtime minutes (00:00 to 08:00 and 16:00 to 24:00):
SELECT dbo.GetMinuteCountTEST(@StartTime, @StopTime, @StartTimeFilterOvertime, @StopTimeFilterOvertime) AS MinutesOvertime
This returns 1080 minutes (18 hours)

And finally, the total minutes:
SELECT dbo.GetMinuteCountTEST(@StartTime, @StopTime, @StartTimeFilterTotal, @StopTimeFilterTotal) AS MinutesTotal
This returns the total of 2040 minutes (34 hours), but I know you aren't going to use my function for this final calculation because you could get away with SQL Server in-built DATEDIFF function :).

Thank you for reading, I hope you'll find this function useful.
Rated 5.00, 1 vote(s). 
E-mail