Re: Hours available in a day

"Shaun" <shaun@mania.plus.com> wrote in message news:20031003110254.34029.qmail@pb1.pair.com... > Hi, > > I have a table called Bookings which has two important columns; > Booking_Start_Time and Booking_End_Time. These columns are both of type > DATETIME. Given any day how can I calculate how many hours are available > between the hours of 09.00 and 17.30 so a user can see at a glance how many > hours they have unbooked on a particular day (i.e. 8.5 hours less the time > of any bookings on that day), can this be done with a query > or do I have to work it out in my code? > > Thanks for your help Fixed: $query = "SELECT 8.5 - (SUM(((DATE_FORMAT(B.Booking_End_Date, '%k') * 60 ) + DATE_FORMAT(B.Booking_End_Date, '%i')) - ((DATE_FORMAT(B.Booking_Start_Date, '%k') * 60 ) + DATE_FORMAT(B.Booking_Start_Date, '%i'))) / 60) AS Available_Hours FROM Bookings B WHERE B.User_ID = '".$user_id."' AND DATE_FORMAT(Booking_Start_Date, '%Y-%m-%d') = '".date("Y-m-d", strtotime("$year-$month-$day"))."'";