Calculate duration for bookings in a period between two time slots

19
April 16, 2019, at 01:50 AM

In a booking system I want to calculate the sum of hours a resource has been booked between two dates (2019-01-01 to 2019-01-02) from 8am to 4pm.

I managed to calculate the sum of hours a resource has been booked if the booking doesn't last more than one day. The problem arises if the booking last more than one day.

With the following booking I expect the result to be 9.5 hours.

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

This is the code I have tried:

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
Answer 1

You can use the following solution to get the booked_time for each 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

You can use the following query to get the result for each row (useful to test the above query):

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

demo on dbfiddle.com

Note: You have to be careful using the SEC_TO_TIME function. On a large amount of data or big date ranges you can reach the maximum value of the TIME datatype:

MySQL retrieves and displays TIME values in 'HH:MM:SS' format (or 'HHH:MM:SS' format for large hours values). TIME values may range from '-838:59:59' to '838:59:59'. The hours part may be so large because the TIME type can be used not only to represent a time of day (which must be less than 24 hours), but also elapsed time or a time interval between two events (which may be much greater than 24 hours, or even negative).
source: https://dev.mysql.com/doc/refman/8.0/en/time.html

READ ALSO
MYSQL: Copy all data and structure if not exist

MYSQL: Copy all data and structure if not exist

i have two database new and old database

25
Mysql Select find third week and day of week from table date

Mysql Select find third week and day of week from table date

I have a MySQL select query that I am trying to get the third Thursday from a date in my table here is what I have for the query any help would be greatly appreciated

30
SQL query for joining four tables

SQL query for joining four tables

I am a newbie to writing SQL queries, Can anyone please help me write SQL query for below conditions and joins?

38
Versioning on a table with foreign keys

Versioning on a table with foreign keys

Let's suppose I have 3 tables,

49