Calculate time difference (only working hours) in minutes between two dates

Question

I need to calculate the number of "active minutes" for an event within a database. The start-time is well known.

The complication is that these active minutes should only be counted during a working day - Monday-Friday 9am-6.30pm, excluding weekends and (known) list of holiday days

The start or "current" time may be outside working hours, but still only the working hours are counted.

This is SQL Server 2005, so T-SQL or a managed assembly could be used.

Answer 1

Globally, you'd need:

1. A way to capture the end-time of the event (possibly through notification, or whatever started the event in the first place), and a table to record this beginning and end time.
2. A helper table containing all the periods (start and end) to be counted. (And then you'd need some supporting code to keep this table up to date in the future)
3. A stored procedure that will:
   • iterate over this helper table and find the 'active' periods
   • calculate the minutes within each active period.

(Note that this assumes the event can last multiple days: is that really likely?)

A different method would be to have a ticking clock inside the event, which checks every time whether the event should be counted at that time, and increments (in seconds or minutes) every time it discovers itself to be active during the relevant period. This would still require the helper table and would be less auditable (presumably).

Answer 2

If you want to do it pure SQL here's one approach

CREATE TABLE working_hours (start DATETIME, end DATETIME);

Now populate the working hours table with countable periods, ~250 rows per year.

If you have an event(@event_start, @event_end) that will start off hours and end off hours then simple query

SELECT SUM(end-start) as duration
FROM working_hours
WHERE start >= @event_start AND end <= @event_end

will suffice.

If on the other hand the event starts and/or ends during working hours the query is more complicated

SELECT SUM(duration) 
FROM 
(
   SELECT SUM(end-start) as duration
   FROM working_hours
   WHERE start >= @event_start AND end <= @event_end
UNION ALL
   SELECT end-@event_start
   FROM working_hours
   WHERE @event_start between start AND end
UNION ALL
   SELECT @event_end - start
   FROM working_hours
   WHERE @event_end between start AND end
) AS u

Notes:

• the above is untested query, depending on your RDBMS you might need date/time functions for aggregating and subtracting datetime (and depending on the functions used the above query can work with any time precision).
• the query can be rewritten to not use the UNION ALL.
• the working_hours table can be used for other things in the system and allows maximum flexibility

EDIT: In MSSQL you can use DATEDIFF(mi, start, end) to get the number of minutes for each subtraction above.