Knowledge is power. We love to share it.

News related to Mono products, services and latest developments in our community.

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 4.00, 5 vote(s).