sql - 计算两个日期之间以分钟为单位的时差(仅工作时间)

Question

我需要计算数据库中事件的“事件分钟数”。开始时间是众所周知的。

复杂的是，这些活跃分钟数只应在工作日计算 - 周一至周五上午 9 点至下午 6 点 30 分，不包括周末和(已知)假期列表

开始时间或“当前”时间可能在工作时间之外，但仍然只计算工作时间。

这是 SQL Server 2005，因此可以使用 T-SQL 或托管程序集。

Answer 1

如果您想使用纯 SQL，这是一种方法

CREATE TABLE working_hours (start DATETIME, end DATETIME);

现在用可计数的时间段填充工时表，每年约 250 行。

如果您有一个事件(@event_start，@event_end)，它将在营业时间开始并在营业时间结束，那么简单查询

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

就足够了。

另一方面，如果事件在工作时间开始和/或结束，则查询更复杂

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

笔记:

以上是未经测试的查询，根据您的 RDBMS，您可能需要日期/时间函数来聚合和减去日期时间(并且根据使用的函数，上述查询可以使用任何时间精度)。

可以重写查询以不使用 UNION ALL。

working_hours 表可用于系统中的其他事情，并提供最大的灵活性

编辑:
在 MSSQL 中，您可以使用 DATEDIFF (mi, start, end) 以获取上述每个减法的分钟数。