c# - 仅营业时间内 DateTime 之间的 TimeSpan 8 :30am - 5:00pm M-F

Question

我正在尝试获取在工单系统中记录作业到作业到期之间的总时间量(以小时为单位)。例如，如果作业在星期五下午 4:00 记录，并在星期一上午 10:30 到期，则它将有 3 小时的时间跨度。 (不包括周末和我们的营业时间)

我可以获得总时间的 TimeSpan，但我不知道如何仅在工作时间内获取 TimeSpan？

我正在使用 LINQpad 作为代码暂存器，并且正在查询数据库。

下面是我的查询示例，它获取营业时间内的总 TimeSpan 而不是 TimeSpan。

(from so in TblServiceOrders 
 where so.DateReceived != null
    && so.TimeReceived != null
    && so.DateRequested != null
    && so.TimeRequested != null
 orderby so.SONumber descending
 select new
 {
   so.SONumber,
   received = so.DateReceived.Value.AddTicks(so.TimeReceived.Value.Ticks),
   requested = so.DateRequested.Value.AddTicks(so.TimeRequested.Value.Ticks),
   TimeSpan = (so.DateReceived.Value.AddTicks(so.TimeReceived.Value.Ticks))
              .Subtract(so.DateRequested.Value.AddTicks(so.TimeRequested.Value.Ticks))
 }).Take(20)

Answer 1

我希望有更好的方法来做到这一点，但是因为我的 TimeRange 永远不会超过几天，所以我已经能够在 Linq 查询中实现这一切下面的代码:

这只适用于 LINQ-to-SQL，不幸的是它不能在 Entity Framework 中使用。

from so in TblServiceOrders

where so.DateReceived != null &&
      so.TimeReceived != null &&
      so.DateRequested != null &&
      so.TimeRequested != null

//Get the named day of the week for start of TimeRange
let DayReceived = so.DateReceived.Value.DayOfWeek
let DayRequested = so.DateRequested.Value.DayOfWeek

//Find out how many days pass within the TimeRange
let SLADaysLapesed = ((so.DateRequested.Value.Day) - (so.DateReceived.Value.Day))

//Find out if any of those days past fall onto a Sat and therefor over a weekend.
//This will only work for the first weekend within the TimeRange.
let SLAContainsWE = (DayReceived == DayOfWeek.Monday && SLADaysLapesed <= 4 ||
                     DayReceived == DayOfWeek.Tuesday && SLADaysLapesed <= 3 ||
                     DayReceived == DayOfWeek.Wednesday && SLADaysLapesed <= 2 ||
                     DayReceived == DayOfWeek.Thursday && SLADaysLapesed <= 1 ||
                     DayReceived == DayOfWeek.Friday && SLADaysLapesed <= 0 ? false : true )

//Work out how many hours pass outside of business hours. (930 minutes)                  
let SLATotalDailyAH = (new TimeSpan(0, (930 * SLADaysLapesed), 0))

//Work out if the TimeRange falls over a weekend. Time declared in mins is equal to two standard working days 8.5hr x 2 in my case.                
let TotalWEHours = (SLAContainsWE == true ? new TimeSpan(0,1020,0) : new TimeSpan(0,0,0))

//Work out how many Business hours have passed by removing the total after hours.
let SLAHours =  (
                ((so.DateRequested.Value.AddHours(so.TimeRequested.Value.Hour)).AddMinutes(so.TimeRequested.Value.Minute)).Subtract
                ((so.DateReceived.Value.AddHours(so.TimeReceived.Value.Hour)).AddMinutes(so.TimeReceived.Value.Minute)).Subtract
                (SLATotalDailyAH).Subtract(TotalWEHours)
                )   

select SLAHours