计算两个日期之间以分钟为单位的时差(仅工作时间) [英] 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.

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

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.

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

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

推荐答案

如果你想做纯 SQL，这里有一种方法

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

CREATE TABLE working_hours (start DATETIME, end DATETIME);

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

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

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

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

就足够了.

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

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

注意事项:

• 以上是未经测试的查询，根据您的 RDBMS，您可能需要日期/时间函数来聚合和减去日期时间(并且根据所使用的函数，上述查询可以使用任何时间精度).
• 可以重写查询以不使用 UNION ALL.
• working_hours 表可用于系统中的其他事情，并提供最大的灵活性

在 MSSQL 中，您可以使用 DATEDIFF(mi, start, end) 来获得上面每个减法的分钟数.

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

这篇关于计算两个日期之间以分钟为单位的时差(仅工作时间)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！