mysql - 通过跳过某些时间来计算 timediff

Question

投诉创建日期:2014-09-16T18:41:42.000Z

投诉截止日期:2014-09-17T10:41:42.000Z

如果投诉在创建日期后的第二天关闭，它将跳过(晚上 7 点至上午 10 点时间)

关闭时间:1小时(跳过晚上7点至上午10点的时间)

Answer 1

此选择查询是用 SQL Server 编写的。我不知道 MySql 语法。您可以从此查询中获取逻辑。试试这个，

select * 
    ,Datediff(dd,CreatedDate,ClosedDate) as D
    ,Datediff(hh,CreatedDate,ClosedDate) as H
    ,Case When Datediff(dd,CreatedDate,ClosedDate) > 0
            Then Datediff(hh,CreatedDate,ClosedDate) - (15 * Datediff(dd,CreatedDate,ClosedDate))
            else Datediff(hh,CreatedDate,ClosedDate)
    End [Time for closure]
from Request