mysql - 如何查找 08 :00-16:00 on a resource 之间的总预订时间

Question

尝试找出一天中两个时间戳之间资源被预订的总时间 - 假设是 08:00 - 16:00。

到目前为止，我想出了这个解决方案，但它没有按预期工作。我知道它为什么不工作，但我不确定我如何从这里得到一个工作的 SQL。

SELECT bookingresource.resourceID, SEC_TO_TIME(SUM(TIME_TO_SEC(booking.bookingEndDatetime) - TIME_TO_SEC(booking.bookingStartDatetime))) AS totalTimeBooked 
FROM booking, bookingresource 
WHERE booking.bookingID =  bookingresource.bookingID
AND bookingresource.bookingStartDatetime BETWEEN '2019-03-11 00:00:00' AND '2019-03-11 23:59:59'
AND HOUR(bookingresource.bookingStartDatetime) BETWEEN 8 AND 16 
AND HOUR(bookingresource.bookingEndDatetime) BETWEEN 8 AND 16
AND booking.companyID = 2447 AND bookingresource.`resourceID` = 807
GROUP BY bookingresource.resourceID

通过这种方式，我不包括在 8 点到 16 点之外开始或结束的预订，这不是预期的。如何包含部分预订时间在 08:00-16:00 之间的预订，并且只计算 08:00 - 16:00 之间的预订时间。

编辑:我将尝试稍微简化 SQL，因此没有那么多不必要的代码。我现在的问题是总时间的计算方式。目前，如果预订部分在时间范围内，它将包括时间范围之外的时间。

SELECT bookingresource.resourceID, SEC_TO_TIME(SUM(TIME_TO_SEC(booking.bookingEndDatetime) - TIME_TO_SEC(booking.bookingStartDatetime))) AS timediff FROM booking, bookingresource 
WHERE booking.bookingID =  bookingresource.bookingID
AND (HOUR(bookingresource.bookingStartDatetime) BETWEEN 8 AND 16 
OR HOUR(bookingresource.bookingEndDatetime) BETWEEN 8 AND 16)
GROUP BY bookingresource.resourceID

编辑 2:所以我找到了解决方案，但我遇到了超过一天的预订问题。到目前为止，这是我的解决方案:

SELECT bookingresource.resourceID, SEC_TO_TIME(sum(LEAST(TIME_TO_SEC(TIME(booking.bookingEndDatetime)), TIME_TO_SEC('16:00')) - GREATEST(TIME_TO_SEC(TIME(booking.bookingStartDatetime)), TIME_TO_SEC('08:00')))) AS totalTimeBooked FROM booking, bookingresource 
WHERE booking.bookingID =  bookingresource.bookingID
AND bookingresource.bookingStartDatetime BETWEEN '2019-04-10 00:00:00' AND '2019-04-10 23:59:59'
AND (TIME(bookingresource.bookingStartDatetime) BETWEEN '08:00' AND '16:00' 
OR TIME(bookingresource.bookingEndDatetime) BETWEEN '08:00' AND '16:00')
  AND bookingresource.`resourceID` = 453
  group by bookingresource.resourceID

Answer 1

尝试将字符串日期转换为正确的日期时间值

如果您不想要 8 - 16 范围之外的值，那么您还应该检查 star 和 end 的日期是否相同

  SELECT bookingresource.resourceID
    , SEC_TO_TIME(SUM(TIME_TO_SEC(booking.bookingEndDatetime) - TIME_TO_SEC(booking.bookingStartDatetime))) AS totalTimeBooked 
  FROM booking
  INNER JOIN  bookingresource  ON booking.bookingID =  bookingresource.bookingID
  WHERE  bookingresource.bookingStartDatetime BETWEEN str_to_date('2019-03-11 00:80:00', '%Y-%m-%d %T') 
        AND str_to_date('2019-03-11 23:59:59', '%Y-%m-%d %T') 
  AND HOUR(bookingresource.bookingStartDatetime) BETWEEN 8 AND 16 
  AND HOUR(bookingresource.bookingEndDatetime) BETWEEN 8 AND 16
  AND DATE(bookingresource.bookingStartDatetime) = DATE(bookingresource.bookingEndDatetime)
  AND booking.companyID = 2447 
  AND bookingresource.`resourceID` = 807
  GROUP BY bookingresource.resourceID