Register / Login
Win a copy of
Serverless Applications with Node.js
this week in the
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 2 weeks 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?
If you try to please everybody, your progress is limited by the noisiest fool. And this tiny ad:
global solutions you can do at home or in your backyard
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