Блог

В системе бронирования я хочу рассчитать сумму часов, в течение которых ресурс был забронирован между двумя датами (2019-01-01 — 2019-01-02) с 8 утра до 4 вечера.

Мне удалось рассчитать сумму часов, в течение которых ресурс был забронирован, если бронирование длится не более одного дня. Проблема возникает, если бронирование длится более одного дня.

При следующем бронировании я ожидаю, что результат составит 9,5 часов.

 startDatetime           endDatetime
2019-01-01 14:30:00     2019-01-02 18:30:00
 

Это код, который я пробовал:

 SELECT resourceID, 
       SEC_TO_TIME(sum(LEAST(TIME_TO_SEC(TIME(endDatetime)), TIME_TO_SEC('16:00')) - GREATEST(TIME_TO_SEC(TIME(startDatetime)), TIME_TO_SEC('08:00')))) AS totalTimeBooked 
FROM booking 
WHERE startDatetime BETWEEN '2019-01-01 00:00:00' AND '2019-01-02 23:59:59'
  AND (TIME(startDatetime) BETWEEN '08:00' AND '16:00' 
    OR TIME(endDatetime) BETWEEN '08:00' AND '16:00')
group by resourceID
 

Вы можете использовать следующее решение, чтобы получить booked_time для каждого resourceID :

 SELECT
  resourceID,
  SEC_TO_TIME(
    SUM(
      CASE WHEN DATEDIFF(DATE(endDatetime), DATE(startDatetime)) = 0 THEN
        GREATEST(TIME_TO_SEC(TIMEDIFF(LEAST('16:00:00', TIME(endDatetime)), LEAST(GREATEST('08:00:00', TIME(startDatetime)), '16:00:00'))), 0)
      ELSE
        TIME_TO_SEC(TIMEDIFF('16:00:00', LEAST(GREATEST('08:00:00', TIME(startDatetime)), '16:00:00')))
          TIME_TO_SEC(TIMEDIFF(GREATEST('08:00:00', LEAST('16:00:00', TIME(endDatetime))), '08:00:00'))
          ((DATEDIFF(DATE(endDatetime), DATE(startDatetime)) - 1) * TIME_TO_SEC(TIMEDIFF('16:00:00', '08:00:00')))
      END
    )
  ) AS booked_time
FROM table_name
GROUP BY resourceID
 

Вы можете использовать следующий запрос, чтобы получить результат для каждой строки (полезно для тестирования приведенного выше запроса):

 SELECT
  resourceID, startDatetime, endDatetime,
  SEC_TO_TIME(
    CASE WHEN DATEDIFF(DATE(endDatetime), DATE(startDatetime)) = 0 THEN
      GREATEST(TIME_TO_SEC(TIMEDIFF(LEAST('16:00:00', TIME(endDatetime)), LEAST(GREATEST('08:00:00', TIME(startDatetime)), '16:00:00'))), 0)
    ELSE
      TIME_TO_SEC(TIMEDIFF('16:00:00', LEAST(GREATEST('08:00:00', TIME(startDatetime)), '16:00:00')))
        TIME_TO_SEC(TIMEDIFF(GREATEST('08:00:00', LEAST('16:00:00', TIME(endDatetime))), '08:00:00'))
        ((DATEDIFF(DATE(endDatetime), DATE(startDatetime)) - 1) * TIME_TO_SEC(TIMEDIFF('16:00:00', '08:00:00')))
    END
  ) AS booked_time
FROM table_name
ORDER BY resourceID, startDatetime, endDatetime
 

демонстрация на dbfiddle.com

Примечание: вы должны быть осторожны при использовании SEC_TO_TIME функции. При большом объеме данных или больших диапазонах дат вы можете достичь максимального значения TIME типа данных:

MySQL извлекает и отображает TIME значения в HH:MM:SS формате » (или « HHH:MM:SS формат для больших значений часов). TIME значения могут варьироваться от « -838:59:59 до» 838:59:59 . Часть часов может быть настолько большой, потому TIME что тип может использоваться не только для представления времени суток (которое должно быть меньше 24 часов), но также прошедшего времени или временного интервала между двумя событиями (который может быть намного больше 24 часов или даже отрицательным).
source: https://dev.mysql.com/doc/refman/8.0/en/time.html

Вы также спросили, как получить заполняемость. Вы можете использовать следующее решение, используя приведенный выше запрос:

 SELECT
  resourceID,
  booked_time,
  CONCAT((TIME_TO_SEC(booked_time) / TIME_TO_SEC(TIMEDIFF('16:00:00', '08:00:00')) * 100), '%') AS 'occupancy_rate'
FROM (
  SELECT
    resourceID,
    SEC_TO_TIME(
      SUM(
        CASE WHEN DATEDIFF(DATE(endDatetime), DATE(startDatetime)) = 0 THEN
          GREATEST(TIME_TO_SEC(TIMEDIFF(LEAST('16:00:00', TIME(endDatetime)), LEAST(GREATEST('08:00:00', TIME(startDatetime)), '16:00:00'))), 0)
        ELSE
          TIME_TO_SEC(TIMEDIFF('16:00:00', LEAST(GREATEST('08:00:00', TIME(startDatetime)), '16:00:00')))
            TIME_TO_SEC(TIMEDIFF(GREATEST('08:00:00', LEAST('16:00:00', TIME(endDatetime))), '08:00:00'))
            ((DATEDIFF(DATE(endDatetime), DATE(startDatetime)) - 1) * TIME_TO_SEC(TIMEDIFF('16:00:00', '08:00:00')))
        END
      )
    ) AS booked_time
  FROM table_name
  GROUP BY resourceID
) t_occupancy_rate
 

демонстрация на dbfiddle.uk