SQL 计算两个日期之间以分钟为单位的时差（仅工作时间）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3296923/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Calculate time difference (only working hours) in minutes between two dates
提问by James Berry
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
复杂的是，这些活跃分钟数只应在工作日计算 - 周一至周五上午 9 点至下午 6 点 30 分，不包括周末和（已知）假期列表
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.
这是 SQL Server 2005，因此可以使用 T-SQL 或托管程序集。
回答by Unreason
If you want to do it pure SQL here's one approach
如果您想使用纯 SQL，这是一种方法
CREATE TABLE working_hours (start DATETIME, end DATETIME);
Now populate the working hours table with countable periods, ~250 rows per year.
现在用可计数的时间段填充工时表，每年约 250 行。
If you have an event(@event_start, @event_end) that will start off hours and end off hours then simple query
如果您有一个事件（@event_start，@event_end），它将在营业时间开始并在营业时间结束，那么简单查询
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
- 以上是未经测试的查询，根据您的 RDBMS，您可能需要日期/时间函数来聚合和减去日期时间（并且根据使用的函数，上述查询可以使用任何时间精度）。
- 可以重写查询以不使用 UNION ALL。
- working_hours 表可用于系统中的其他事情，并提供最大的灵活性
EDIT: In MSSQL you can use DATEDIFF(mi, start, end) to get the number of minutes for each subtraction above.
编辑：在 MSSQL 中，您可以使用DATEDIFF(mi, start, end) 来获取上述每个减法的分钟数。
回答by Danny S
Using unreason's excellent starting point, here is a TSQL implementation for SQL Server 2012.
使用 unreason 的优秀起点，这里是 SQL Server 2012 的 TSQL 实现。
This first SQL populates a table with our work days and times excluding weekends and holidays:
第一个 SQL 将我们的工作日和时间（周末和节假日除外）填充到一个表中：
declare @dteStart date
declare @dteEnd date
declare @dtStart smalldatetime
declare @dtEnd smalldatetime
Select @dteStart = '2016-01-01'
Select @dteEnd = '2016-12-31'
CREATE TABLE working_hours (starttime SMALLDATETIME, endtime SMALLDATETIME);
while @dteStart <= @dteEnd
BEGIN
IF datename(WEEKDAY, @dteStart) <> 'Saturday'
AND DATENAME(WEEKDAY, @dteStart) <> 'Sunday'
AND @dteStart not in ('2016-01-01' --New Years
,'2016-01-18' --MLK Jr
,'2016-02-15' --President's Day
,'2016-05-30' --Memorial Day
,'2016-07-04' --Fourth of July
,'2016-09-05' --Labor Day
,'2016-11-11' --Veteran's Day
,'2016-11-24' --Thanksgiving
,'2016-11-25' --Day after Thanksgiving
,'2016-12-26' --Christmas
)
BEGIN
select @dtStart = SMALLDATETIMEFROMPARTS(year(@dteStart),month(@dteStart),day(@dteStart),8,0) --8:00am
select @dtEnd = SMALLDATETIMEFROMPARTS(year(@dteStart),month(@dteStart),day(@dteStart),17,0) --5:00pm
insert into working_hours values (@dtStart,@dtEnd)
END
Select @dteStart = DATEADD(day,1,@dteStart)
END
Now here is the logic that worked to return the minutes as an INT:
现在这里是将分钟作为 INT 返回的逻辑：
declare @event_start datetime2
declare @event_end datetime2
select @event_start = '2016-01-04 8:00'
select @event_end = '2016-01-06 16:59'
SELECT SUM(duration) as minutes
FROM
(
SELECT DATEDIFF(mi,@event_start,@event_end) as duration
FROM working_hours
WHERE @event_start >= starttime
AND @event_start <= endtime
AND @event_end <= endtime
UNION ALL
SELECT DATEDIFF(mi,@event_start,endtime)
FROM working_hours
WHERE @event_start >= starttime
AND @event_start <= endtime
AND @event_end > endtime
UNION ALL
SELECT DATEDIFF(mi,starttime,@event_end)
FROM working_hours
WHERE @event_end >= starttime
AND @event_end <= endtime
AND @event_start < starttime
UNION ALL
SELECT SUM(DATEDIFF(mi,starttime,endtime))
FROM working_hours
WHERE starttime > @event_start
AND endtime < @event_end
) AS u
This correctly returns 1 minute shy of three 9 hour work days
这正确地返回了比三个 9 小时工作日少 1 分钟的结果
回答by LazyDave76
I came here looking for an answer to a very similar question - I needed to get the minutes between 2 dates excluding weekends and excluding hours outside of 08:30 and 18:00. After a bit of hacking around, I think i have it sorted. Below is how I did it. thoughts are welcome - who knows, maybe one day I'll sign up to this site :)
我来这里是为了寻找一个非常相似问题的答案 - 我需要获得两个日期之间的分钟数，不包括周末，不包括 08:30 和 18:00 以外的时间。经过一些黑客攻击，我想我已经整理好了。下面是我是如何做到的。欢迎提出想法 - 谁知道呢，也许有一天我会注册这个网站:)
create function WorkingMinutesBetweenDates(@dteStart datetime, @dteEnd datetime)
returns int
as
begin
declare @minutes int
set @minutes = 0
while @dteEnd>=@dteStart
begin
if datename(weekday,@dteStart) <>'Saturday' and datename(weekday,@dteStart)<>'Sunday'
and (datepart(hour,@dteStart) >=8 and datepart(minute,@dteStart)>=30 )
and (datepart(hour,@dteStart) <=17)
begin
set @minutes = @minutes + 1
end
set @dteStart = dateadd(minute,1,@dteStart)
end
return @minutes
end
go
回答by VoodooChilliChild
I started working with what Unreason posted and was a great start. I tested this is SQL Server and found not all time was being captured. I think the problem was primarily when the event started and ended the same day. This solution seems to be working well enough for me
我开始使用 Unreason 发布的内容，这是一个很好的开始。我测试了这是 SQL Server，发现并非所有时间都被捕获。我认为问题主要在于活动在同一天开始和结束的时间。这个解决方案似乎对我来说足够好
CREATE TABLE [dbo].[working_hours](
[wh_id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[wh_starttime] [datetime] NULL,
[wh_endtime] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
[wh_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
走
CREATE FUNCTION [dbo].[udFWorkingMinutes]
(
@startdate DATETIME
,@enddate DATETIME
)
RETURNS INT
AS
BEGIN
DECLARE @WorkingHours INT
SET @WorkingHours =
(SELECT
CASE WHEN COALESCE(SUM(duration),0) < 0 THEN 0 ELSE SUM(Duration)
END AS Minutes
FROM
(
--All whole days
SELECT ISNULL(DATEDIFF(mi,wh_starttime,wh_endtime),0) AS Duration
FROM working_hours
WHERE wh_starttime >= @startdate AND wh_endtime <= @enddate
UNION ALL
--All partial days where event start after office hours and finish after office hours
SELECT ISNULL(DATEDIFF(mi,@startdate,wh_endtime),0) AS Duration
FROM working_hours
WHERE @startdate > wh_starttime AND @enddate >= wh_endtime
AND (CAST(wh_starttime AS DATE) = CAST(@startdate AS DATE))
AND @startdate < wh_endtime
UNION ALL
--All partial days where event starts before office hours and ends before day end
SELECT ISNULL(DATEDIFF(mi,wh_starttime,@enddate),0) AS Duration
FROM working_hours
WHERE @enddate < wh_endtime
AND @enddate >= wh_starttime
AND @startdate <= wh_starttime
AND (CAST(wh_endtime AS DATE) = CAST(@enddate AS DATE))
UNION ALL
--Get partial day where intraday event
SELECT ISNULL(DATEDIFF(mi,@startdate,@enddate),0) AS Duration
FROM working_hours
WHERE @startdate > wh_starttime AND @enddate < wh_endtime
AND (CAST(@startdate AS DATE)= CAST(wh_starttime AS DATE))
AND (CAST(@enddate AS DATE)= CAST(wh_endtime AS DATE))
) AS u)
RETURN @WorkingHours
END
GO
Alls that is left to do is populate the working hours table with something like
剩下要做的就是用类似的东西填充工作时间表
;WITH cte AS (
SELECT CASE WHEN DATEPART(Day,'2014-01-01 9:00:00 AM') = 1 THEN '2014-01-01 9:00:00 AM'
ELSE DATEADD(Day,DATEDIFF(Day,0,'2014-01-01 9:00:00 AM')+1,0) END AS myStartDate,
CASE WHEN DATEPART(Day,'2014-01-01 5:00:00 PM') = 1 THEN '2014-01-01 5:00:00 PM'
ELSE DATEADD(Day,DATEDIFF(Day,0,'2014-01-01 5:00:00 PM')+1,0) END AS myEndDate
UNION ALL
SELECT DATEADD(Day,1,myStartDate), DATEADD(Day,1,myEndDate)
FROM cte
WHERE DATEADD(Day,1,myStartDate) <= '2015-01-01'
)
INSERT INTO working_hours
SELECT myStartDate, myEndDate
FROM cte
OPTION (MAXRECURSION 0)
delete from working_hours where datename(dw,wh_starttime) IN ('Saturday', 'Sunday')
--delete public holidays
delete from working_hours where CAST(wh_starttime AS DATE) = '2014-01-01'
My first post! Be merciful.
我的第一篇文章！仁慈点。
回答by Tobiasopdenbrouw
Globally, you'd need:
在全球范围内，您需要：
- 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.
- 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)
- 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).
一种不同的方法是在事件内部设置一个滴答时钟，它每次检查该事件是否应该在那个时间计数，并在每次发现自己在相关时间段内处于活动状态时递增（以秒或分钟为单位）。这仍然需要帮助表，并且可审计性较低（大概）。
