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
``````

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

POPULAR ONLINE

Laravel S3 image upload creates a folder with the filename automatically 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

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

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

Let's suppose I have 3 tables,

49