From: shaun thornburgh [mailto:[EMAIL PROTECTED]
> Hi, > > The following query works out the amount of hours a user has > been booked for on a particular day: > > SELECT > (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 Hours_Booked > FROM Bookings B > WHERE B.User_ID = "1" > AND NOT ( "2004-11-01" < DATE_FORMAT(B.Booking_Start_Date, "%Y-%m-%d") > OR "2004-11-01" > DATE_FORMAT(B.Booking_End_Date, "%Y-%m-%d") ) ; > > However it fails if the booking spans more than one day. For > example I have a booking that starts at 9am on 2004-10-31 and > ends at 10am on 2004-11-02, and for this the query returns one > hour, when it should be 24! > > Any pointers regarding this would be most apprecited %k returns, simply, the hour value of the date in question. In this case, you're subtracting 9 from 10, which is where the 1 comes from. While there may be an easier way, I'd start with converting both dates to Unix format, subtracting, and then doing the division to get # of days. Something like... SELECT (UNIX_TIMESTAMP(B.Booking_End_Date) - UNIX_TIMESTAMP(B.Booking_Start_Date)) / (60 * 60); Given values of '2004-11-01 10:00:00' and '2004-10-31 09:00:00', you end up with 25 (which I think is what you were looking for, not 24). As I said, there may be a more elegant way to do that, but this is the brute-force method that should work. HTH! -- Mike Johnson Smarter Living, Inc. Web Developer www.smarterliving.com [EMAIL PROTECTED] (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
