James Berry James Berry - 7 months ago 144
SQL Question

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

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

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.