C# 计算 2 个日期时间之间经过的工作时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/141368/
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
Calculating the elapsed working hours between 2 datetime
提问by Dan
Given two datetimes. What is the best way to calculate the number of working hours between them. Considering the working hours are Mon 8 - 5.30, and Tue-Fri 8.30 - 5.30, and that potentially any day could be a public holiday.
给定两个日期时间。计算它们之间的工作小时数的最佳方法是什么。考虑到工作时间是周一 8 - 5.30 和周二至周五 8.30 - 5.30，而且可能任何一天都可能是公共假期。
This is my effort, seem hideously inefficient but in terms of the number of iterations and that the IsWorkingDay method hits the DB to see if that datetime is a public holiday.
这是我的努力，似乎效率低得可怕，但就迭代次数而言，并且 IsWorkingDay 方法会访问数据库以查看该日期时间是否为公共假期。
Can anyone suggest any optimizations or alternatives.
任何人都可以提出任何优化或替代方案。
public decimal ElapsedWorkingHours(DateTime start, DateTime finish)
{
decimal counter = 0;
while (start.CompareTo(finish) <= 0)
{
if (IsWorkingDay(start) && IsOfficeHours(start))
{
start = start.AddMinutes(1);
counter++;
}
else
{
start = start.AddMinutes(1);
}
}
decimal hours;
if (counter != 0)
{
hours = counter/60;
}
return hours;
}
回答by OregonGhost
especially considering the IsWorkingDay method hits the DB to see if that day is a public holiday
特别是考虑到 IsWorkingDay 方法会访问数据库以查看那天是否是公共假期
If the problem is the number of queries rather than the amount of data, query the working day data from the data base for the entire day range you need at the beginning instead of querying in each loop iteration.
如果问题是查询次数而不是数据量，那么一开始就从数据库中查询你需要的全天范围的工作日数据，而不是在每次循环迭代中查询。
回答by Airsource Ltd
Before you start optimizing it, ask yourself two questions.
在开始优化之前，先问自己两个问题。
a) Does it work?
a) 它有效吗？
b) Is it too slow?
b) 是不是太慢了？
Only if the answer to both question is "yes" are you ready to start optimizing.
只有当这两个问题的答案都是“是”时，您才准备好开始优化。
Apart from that
除此之外
- you only need to worry about minutes and hours on the start day and end day. Intervening days will obviously be a full 9/9.5 hours, unless they are holidays or weekends
- No need to check a weekend day to see if it's a holiday
- 您只需要担心开始日和结束日的分钟数和小时数。中间的日子显然是整整 9/9.5 小时，除非是假期或周末
- 无需检查周末是否为假期
Here's how I'd do it
这是我的方法
// Normalise start and end
while start.day is weekend or holiday, start.day++, start.time = 0.00am
if start.day is monday,
start.time = max(start.time, 8am)
else
start.time = max(start.time, 8.30am)
while end.day is weekend or holiday, end.day--, end.time = 11.59pm
end.time = min(end.time, 5.30pm)
// Now we've normalised, is there any time left?
if start > end
return 0
// Calculate time in first day
timediff = 5.30pm - start.time
day = start.day + 1
// Add time on all intervening days
while(day < end.day)
// returns 9 or 9.30hrs or 0 as appropriate, could be optimised to grab all records
// from the database in 1 or 2 hits, by counting all intervening mondays, and all
// intervening tue-fris (non-holidays)
timediff += duration(day)
// Add time on last day
timediff += end.time - 08.30am
if end.day is Monday then
timediff += end.time - 08.00am
else
timediff += end.time - 08.30am
return timediff
You could do something like SELECT COUNT(DAY) FROM HOLIDAY WHERE HOLIDAY BETWEEN @Start AND @End GROUP BY DAY
您可以执行类似 SELECT COUNT(DAY) FROM HOLIDAY WHERE HOLIDAY BETWEEN @Start AND @End GROUP BY DAY 之类的操作
to count the number of holidays falling on Monday, Tuesday, Wednesday, and so forth. Probably a way of getting SQL to count just Mondays and non-Mondays, though can't think of anything at the moment.
计算星期一、星期二、星期三等的假期数。可能是让 SQL 只计算星期一和非星期一的一种方法，尽管目前想不出任何事情。
回答by Ian Jacobs
Take a look at the TimeSpan Class. That will give you the hours between any 2 times.
看看 TimeSpan 类。这将为您提供任何 2 次之间的时间。
A single DB call can also get the holidays between your two times; something along the lines of:
一次 DB 调用也可以得到你两次之间的假期；类似的东西：
SELECT COUNT(*) FROM HOLIDAY WHERE HOLIDAY BETWEEN @Start AND @End
Multiply that count by 8 and subtract it from your total hours.
将该计数乘以 8，然后从您的总小时数中减去它。
-Ian
-伊恩
EDIT: In response to below, If you're holiday's are not a constant number of hours. you can keep a HolidayStartand a HolidayEndTime in your DB and and just return them from the call to the db as well. Do an hour count similar to whatever method you settle on for the main routine.
编辑：响应以下，如果您是假期，则不是固定的小时数。您可以在您的数据库中保留一个HolidayStart和一个HolidayEnd时间，并且也可以从对数据库的调用中返回它们。以类似于您为主要例程选择的任何方法计算一个小时。
回答by Joel Coehoorn
Building on what @OregonGhost said, rather than using an IsWorkingDay() function at accepts a day and returns a boolean, have a HolidayCount() function that accepts a range and returns an integer giving the number of Holidays in the range. The trick here is if you're dealing with a partial date for your boundry beginning and end days you may still need to determine if those dates are themselves holidays. But even then, you could use the new method to make sure you needed at mostthree calls the to DB.
建立在@OregonGhost 所说的基础上，而不是使用 IsWorkingDay() 函数接受一天并返回一个布尔值，而是使用一个 HolidayCount() 函数来接受一个范围并返回一个整数，给出该范围内的假期数。这里的技巧是，如果您正在处理边界开始日和结束日的部分日期，您可能仍然需要确定这些日期本身是否是假期。但即便如此，您也可以使用新方法来确保最多需要对 DB 进行三次调用。
回答by Stephen Wrighton
Try something along these lines:
尝试以下方法：
TimeSpan = TimeSpan Between Date1 And Date2
cntDays = TimeSpan.Days
cntNumberMondays = Iterate Between Date1 And Date2 Counting Mondays
cntdays = cntdays - cntnumbermondays
NumHolidays = DBCall To Get # Holidays BETWEEN Date1 AND Date2
Cntdays = cntdays - numholidays
numberhours = ((decimal)cntdays * NumberHoursInWorkingDay )+((decimal)cntNumberMondays * NumberHoursInMondayWorkDay )
回答by Jeffrey L Whitledge
There's also the recursive solution. Not necessarily efficient, but a lot of fun:
还有递归解决方案。不一定高效，但很有趣：
public decimal ElapseddWorkingHours(DateTime start, DateTime finish)
{
if (start.Date == finish.Date)
return (finish - start).TotalHours;
if (IsWorkingDay(start.Date))
return ElapsedWorkingHours(start, new DateTime(start.Year, start.Month, start.Day, 17, 30, 0))
+ ElapsedWorkingHours(start.Date.AddDays(1).AddHours(DateStartTime(start.Date.AddDays(1)), finish);
else
return ElapsedWorkingHours(start.Date.AddDays(1), finish);
}
回答by Mark Ransom
The most efficient way to do this is to calculate the total time difference, then subtract the time that is a weekend or holiday. There are quite a few edge cases to consider, but you can simplify that by taking the first and last days of the range and calculating them seperately.
最有效的方法是计算总时差，然后减去周末或假期的时间。有很多边缘情况需要考虑，但您可以通过获取范围的第一天和最后一天并分别计算它们来简化它。
The COUNT(*) method suggested by Ian Jacobsseems like a good way to count the holidays. Whatever you use, it will just handle the whole days, you need to cover the start and end dates separately.
Ian Jacobs建议的 COUNT(*) 方法似乎是计算假期的好方法。无论您使用什么，它都会处理一整天，您需要分别涵盖开始日期和结束日期。
Counting the weekend days is easy; if you have a function Weekday(date) that returns 0 for Monday through 6 for Sunday, it looks like this:
计算周末天数很容易；如果您有一个函数 Weekday(date) 在星期一返回 0 到星期日返回 6，它看起来像这样：
saturdays = ((finish - start) + Weekday(start) + 2) / 7;
sundays = ((finish - start) + Weekday(start) + 1) / 7;
Note: (finish - start) isn't to be taken literally, replace it with something that calculates the time span in days.
注意： (finish - start) 不能按字面意思理解，而是用以天为单位计算时间跨度的东西替换它。
回答by jgreep
Use @Ian's query to check between dates to find out which days are not working days. Then do some math to find out if your start time or end time falls on a non-working day and subtract the difference.
使用@Ian 的查询来检查日期之间的日期以找出哪些天不是工作日。然后做一些数学计算，找出您的开始时间或结束时间是否在非工作日并减去差异。
So if start is Saturday noon, and end is Monday noon, the query should give you back 2 days, from which you calculate 48 hours (2 x 24). If your query on IsWorkingDay(start) returns false, subtract from 24 the time from start to midnight, which would give you 12 hours, or 36 hours total non-working hours.
因此，如果开始是星期六中午，结束是星期一中午，则查询应该返回 2 天，从中计算 48 小时 (2 x 24)。如果您对 IsWorkingDay(start) 的查询返回 false，则从 24 减去从开始到午夜的时间，这将为您提供 12 小时或 36 小时的总非工作时间。
Now, if your office hours are the same for every day, you do a similar thing. If your office hours are a bit scattered, you'll have more trouble.
现在，如果你每天的办公时间都一样，你也会做类似的事情。如果你的办公时间有点分散，你会遇到更多的麻烦。
Ideally, make a single query on the database that gives you all of the office hours between the two times (or even dates). Then do the math locally from that set.
理想情况下，对数据库进行一次查询，以获取两个时间（甚至日期）之间的所有办公时间。然后从该集合本地进行数学计算。
回答by Wes
Dim totalMinutes As Integer = 0
For minute As Integer = 0 To DateDiff(DateInterval.Minute, contextInParameter1, contextInParameter2)
Dim d As Date = contextInParameter1.AddMinutes(minute)
If d.DayOfWeek <= DayOfWeek.Friday AndAlso _
d.DayOfWeek >= DayOfWeek.Monday AndAlso _
d.Hour >= 8 AndAlso _
d.Hour <= 17 Then
totalMinutes += 1
Else
Dim test = ""
End If
Next minute
Dim totalHours = totalMinutes / 60
Piece of Cake!
小菜一碟！
Cheers!
干杯!
