Register / Login
this forum made possible by our volunteer staff, including ...
Stephan van Hulst
JDBC and Relational Databases
Calculating duration booked on a resource where the booking's range is more than one day
posted 5 months ago
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
I have made this SQL so far and it calculates the duration on bookings in a time range correctly as long as the bookings doesn't last more than one day.
Can someone please help or point me in the right direction?
Oh the stink of it! Smell my tiny ad!
Java file APIs (DOC, XLS, PDF, and many more)
Boost this thread!
Calculating a timespan between 2 timestamps
Where does the extra day come from in duration calculation?
Get only date in a select statement
Calculate Working hours
how do i compare row by row