计算2日期时间之间经过的工作时间 [英] Calculating the elapsed working hours between 2 datetime
问题描述
给定两个日期时间。什么是计算它们之间的工作小时数的最佳方式。考虑到工作时间是周一8 - 5.30，和周二至周五8.30 - 5.30，而潜在的任何一天能为公众假期
这是我的心血，似乎令人发指的低效率，但在迭代次数和该IsWorkingDay方法打DB，看看是否能日期时间为公众假期条款。
任何人都可以提出任何优化或替代品
公共小数ElapsedWorkingHours（DateTime的开始，日期时间结束）
{
十进制计数器= 0;
而（start.CompareTo（光洁度）下; = 0），如果
{
（IsWorkingDay（开始）及&放大器; IsOfficeHours（开始））
{
开始= start.AddMinutes（1）;
计数器++;
}
，否则
{
开始= start.AddMinutes（1）;
}
}
十进制小时;
如果（计数器！= 0）
{
小时数=计/ 60;
}
返回小时;
}
在你开始优化它，问自己两个问题。
A）有用吗？
二）是不是太慢了？
只有在回答这两个问题是是，你准备开始优化。
除此之外
- 您只需要担心的起始日和结束日分钟和小时。中间的日子显然是一个完整的9 / 9.5小时，除非是节假日或周末
- 不需要检查是周末，看它是否是一个节日
下面就是我会做它
//开始规格化和结束
，而start.day是周末或节假日，start.day ++，start.time =上午12点
如果start.day是星期一，
start.time = MAX（start.time，上午8时）
，否则
start.time = MAX（start.time，上午8点半）
，而end.day是周末或节假日，end.day--，end.time =下午11时59
end.time = MIN（end.time，下午5点30）
//现在我们已经常态化，没有任何时间留给？
如果开始>结束
返回0
//计算在第一天
timeDiff的时间为下午5:30 - start.time
天= start.day + 1
/ /添加时间对所有的时光
，而（天< end.day）
//返回9或9.30hrs或0适当，可以优化抓住所有记录
//从数据库中的1或2安打，通过计算所有介入星期一，所有
//介入TUE-朋友（非假日）
timeDiff测量+ =持续时间（天）
//添加时间最后一天
timeDiff测量+ = end.time - 上午08时30
如果end.day是星期一那么
timeDiff测量+ = end.time - 上午08时00分
$其他b $ b timeDiff测量+ = end.time - 上午08点半
返回timeDiff的
你可以这样做
SELECT COUNT（DAY）FROM节日，假日BETWEEN @启动和@End GROUP BY DAY
计数假期的人数下降周一，周二，周三，等等。也许让SQL来算只是周一和非星期一，虽然不能认为目前的任何一种方式。
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.
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.
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;
}
Before you start optimizing it, ask yourself two questions.
a) Does it work?
b) Is it too slow?
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
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
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.
这篇关于计算2日期时间之间经过的工作时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
