Help with a DATETIME query please!
Hi, I am trying to create a query that returns a users availability for the next ten days using version 4.1.11. A user has 8.5 hours available per day so a users availability is equal to 8.5 less any bookings they have on that day. I have a table called Bookings (see below), and each 'Booking' has a start date and time and an end date and time. Dates for bookings don't have to be the same, i.e. a user could be on holiday for a week. The follwing query produces an empty result: SELECT (8.5 - TIMEDIFF(B.Booking_End_Date, B.Booking_Start_Date)) AS 'Availability' FROM Bookings B WHERE B.User_ID = '1' AND NOT ( CURDATE() < DATE_FORMAT(Booking_Start_Date, "%Y-%m-%d") CURDATE() > DATE_FORMAT(Booking_End_Date, "%Y-%m-%d") ) GROUP BY Availability ORDER BY B.Booking_Start_Date; Here is the Definition for the bookings table: +-+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-++ | Booking_ID | int(11) | | PRI | NULL| auto_increment | | Booking_Type| varchar(15) | | | Unavailability || | User_ID | int(11) | | | 0 || | Project_ID | int(11) | YES | | NULL|| | Booking_Creator_ID | int(11) | YES | | NULL|| | Booking_Creation_Date | datetime| YES | | NULL|| | Booking_Start_Date | datetime| | | -00-00 00:00:00 || | Booking_End_Date| datetime| | | -00-00 00:00:00 || | Booking_Status | varchar(15) | | | Other || | Additional_Notes| text| YES | | NULL|| +-+-+--+-+-++ Thanks for any advice offered here -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help with a DATETIME Query Please Using 3.23.54
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 Developerwww.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]
Help with a DATETIME Query Please Using 3.23.54
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 Shaun -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with a DATETIME Query
Hi, I have a table called Bookings which holds start times and end times for appointments, these are held in Booking_Start_Date and Booking_End_Date. I have a page on my site that runs a query to produce a grid to show availiability per day for the next ten days for each user of the system. Users work 8.5 hours a day and the query shows how many hours available the user has on that day: 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 = '610' AND NOT ( '2004-07-08' < DATE_FORMAT(Booking_Start_Date, "%Y-%m-%d") OR '2004-07-08' > DATE_FORMAT(Booking_End_Date, "%Y-%m-%d") ) The problem here is I have to do this query to produce a result for each cell and then for each user so 10 users = 100 queries to load the page! Is there a way to produce the result so that I only need one query per user so it groups the result by day for the next ten days? Thanks for your help _ Express yourself with cool new emoticons http://www.msn.co.uk/specials/myemo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with a DATETIME Query PLEASE!
Shaun Not a 100 % sure if this is what you're looking for or if somebody's already suggested it also do not know how practical it is for your application If a booking spans more than one day (e.g. two) split it into two days - and write two records to the table one for each daythis fits with the spirit of your applicationa user is going to be interested in free time per day surely The problem with this is if you use a booking id which serves as a primary key and will therefore not not allow duplicates..but if that's the case, then there are workarounds for that too... Let me know if I'm warm:) Rory McKinley Nebula Solutions + 27 82 857 2391 [EMAIL PROTECTED] "There are 10 kinds of people in this world, those who understand binary and those who don't" (Unknown) - Original Message - From: "shaun thornburgh" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, October 10, 2003 12:37 PM Subject: Help with a DATETIME Query PLEASE! > Hi, > > I have a table called Bookings which has two important columns; > Booking_Start_Date and Booking_End_Date. These columns are both of type > DATETIME. The following query calculates 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). However, when a booking spans more than one > day the query doesn't work, for example if a user has a booking that starts > on day one at 09.00 and ends at 14.30 on the next day, the query returns 3.5 > hours for both days. Any help here would be greatly appreciated. > > 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 WMS_Bookings B WHERE B.User_ID = '16' > AND B.Booking_Status <> '1' > AND NOT ( > '2003-10-07' < > DATE_FORMAT(Booking_Start_Date, "%Y-%m-%d") > OR > '2003-10-07' > DATE_FORMAT(Booking_End_Date, "%Y-%m-%d") > ) > > > Thanks for your help > > _ > On the move? Get Hotmail on your mobile phone http://www.msn.co.uk/msnmobile > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with a DATETIME Query PLEASE!
Hi, I have a table called Bookings which has two important columns; Booking_Start_Date and Booking_End_Date. These columns are both of type DATETIME. The following query calculates 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). However, when a booking spans more than one day the query doesn't work, for example if a user has a booking that starts on day one at 09.00 and ends at 14.30 on the next day, the query returns 3.5 hours for both days. Any help here would be greatly appreciated. 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 WMS_Bookings B WHERE B.User_ID = '16' AND B.Booking_Status <> '1' AND NOT ( '2003-10-07' < DATE_FORMAT(Booking_Start_Date, "%Y-%m-%d") OR '2003-10-07' > DATE_FORMAT(Booking_End_Date, "%Y-%m-%d") ) Thanks for your help _ On the move? Get Hotmail on your mobile phone http://www.msn.co.uk/msnmobile -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help With a DATETIME Query PLEASE!
I'm under the impression that your over thinking the problem. LOOK-UP the functions DATE_SUB / INTERVAL / TIME_TO_SEC / TO_DAYS and the arithmetic should be easy. - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 -->-Original Message- -->From: shaun thornburgh [mailto:[EMAIL PROTECTED] -->Sent: Wednesday, October 08, 2003 1:52 AM -->To: [EMAIL PROTECTED] -->Subject: Help With a DATETIME Query PLEASE! --> -->Hi, --> -->I have a table called Bookings which has two important columns; -->Booking_Start_Date and Booking_End_Date. These columns are both of type -->DATETIME. The following query calculates 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). However, when a booking spans more than one -->day the query doesn't work, for example if a user has a booking that -->starts -->on day one at 09.00 and ends at 14.30 on the next day, the query returns -->3.5 -->hours for both days. Any help here would be greatly appreciated. --> -->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 WMS_Bookings B WHERE B.User_ID = '16' AND -->B.Booking_Status <> '1' AND NOT ( '2003-10-07' < -->DATE_FORMAT(Booking_Start_Date, "%Y-%m-%d") OR '2003-10-07' > -->DATE_FORMAT(Booking_End_Date, "%Y-%m-%d") ) --> -->Thanks for your help --> -->_ -->Find a cheaper internet access deal - choose one to suit you. -->http://www.msn.co.uk/internetaccess --> --> -->-- -->MySQL General Mailing List -->For list archives: http://lists.mysql.com/mysql -->To unsubscribe: -->http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help With a DATETIME Query PLEASE!
On Wed, 2003-10-08 at 09:52, shaun thornburgh wrote: > Hi, > > I have a table called Bookings which has two important columns; > Booking_Start_Date and Booking_End_Date. These columns are both of type > DATETIME. The following query calculates 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). However, when a booking spans more than one > day the query doesn't work, for example if a user has a booking that starts > on day one at 09.00 and ends at 14.30 on the next day, the query returns 3.5 > hours for both days. Any help here would be greatly appreciated. > > 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 WMS_Bookings B WHERE B.User_ID = '16' AND > B.Booking_Status <> '1' AND NOT ( '2003-10-07' < > DATE_FORMAT(Booking_Start_Date, "%Y-%m-%d") OR '2003-10-07' > > DATE_FORMAT(Booking_End_Date, "%Y-%m-%d") ) > > Thanks for your help > > _ > Find a cheaper internet access deal - choose one to suit you. > http://www.msn.co.uk/internetaccess It would be a good idea to format your SQL so it can be read more easily, I am sure people would be more inclined to help you if you did this. I am sure PHP has date time functions that help with this. Have a look at the online manual or download it. Can't quite remember but I think there is an hour between function, go to php.net and look at the date/time function bit. If not try looking on the net for someone who has already written such a function, there probably is someone who has. Ben -- * Ben Edwards Tel +44 (0)1179 553 551 ICQ 42000477 * * Homepage - nothing of interest here http://gurtlush.org.uk * * Webhosting for the masses http://www.serverone.co.uk * * Critical Site Builderhttp://www.criticaldistribution.com * * online collaborative web authoring content management system * * Get alt news/views films online http://www.cultureshop.org * * i-Contact Progressive Video http://www.videonetwork.org * * Fun corporate graphics http://www.subvertise.org * * Bristol Indymedia http://bristol.indymedia.org * * Bristol's radical news http://www.bristle.org.uk * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help With a DATETIME Query PLEASE!
Hi, I have a table called Bookings which has two important columns; Booking_Start_Date and Booking_End_Date. These columns are both of type DATETIME. The following query calculates 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). However, when a booking spans more than one day the query doesn't work, for example if a user has a booking that starts on day one at 09.00 and ends at 14.30 on the next day, the query returns 3.5 hours for both days. Any help here would be greatly appreciated. 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 WMS_Bookings B WHERE B.User_ID = '16' AND B.Booking_Status <> '1' AND NOT ( '2003-10-07' < DATE_FORMAT(Booking_Start_Date, "%Y-%m-%d") OR '2003-10-07' > DATE_FORMAT(Booking_End_Date, "%Y-%m-%d") ) Thanks for your help _ Find a cheaper internet access deal - choose one to suit you. http://www.msn.co.uk/internetaccess -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help With a DATETIME Query
I've worked on applications like this one in a court scheduling application. Since trials can run several days, this was an issue I had to deal with, too. The solution I set up was to have a table like your "bookings" table, then have another table containing available time slots for each eligible date. At the time a booking was created, my application then reserved appropriate time slots in the adjacent table for that particular booking row. The key is that the timeslots table had rows for each day involved and made subsequent queries much easier and more accurate. This one is my .02... :-) -Original Message- From: shaun thornburgh [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 07, 2003 3:51 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: Help With a DATETIME Query Thanks for your reply, but its not an option! >From: "Dan Greene" <[EMAIL PROTECTED]> >To: "shaun thornburgh" <[EMAIL PROTECTED]>, ><[EMAIL PROTECTED]> >Subject: RE: Help With a DATETIME Query >Date: Tue, 7 Oct 2003 16:41:04 -0400 > >I know it's not the answer you're looking for... :( but dealing with >overnights has caused me so much aggravation in past apps I've written, >I've tended to make the client create two (or more) 'bookings' for the >covered time... don't know if it's an option for you, but it's my >$0.02. > > > > > -Original Message- > > From: shaun thornburgh [mailto:[EMAIL PROTECTED] > > Sent: Tuesday, October 07, 2003 4:33 PM > > To: [EMAIL PROTECTED] > > Subject: Help With a DATETIME Query > > > > > > Hi, > > > > I have a table called Bookings which has two important columns; > > Booking_Start_Date and Booking_End_Date. These columns are both of > > type DATETIME. The following query calculates 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). However, when a booking spans > > more than one > > day the query doesn't work, for example if a user has a > > booking that starts > > on day one at 09.00 and ends at 14.30 on the next day, the > > query returns 3.5 > > hours for both days. The query is run for each day i.e day 1, > > day 2 day > > 10. > > > > Any help here would be greatly appreciated. > > > > 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 WMS_Bookings B > > WHERE B.User_ID = '16' > > AND B.Booking_Status <> '1' > > AND NOT ( '2003-10-07' < DATE_FORMAT(Booking_Start_Date, > > "%Y-%m-%d") OR > > '2003-10-07' > DATE_FORMAT(Booking_End_Date, "%Y-%m-%d") ) > > > > _ > > On the move? Get Hotmail on your mobile phone > > http://www.msn.co.uk/msnmobile > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > >-- >MySQL General Mailing List >For list archives: http://lists.mysql.com/mysql >To unsubscribe: >http://lists.mysql.com/[EMAIL PROTECTED] > _ Use MSN Messenger to send music and pics to your friends http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help With a DATETIME Query
Thanks for your reply, but its not an option! From: "Dan Greene" <[EMAIL PROTECTED]> To: "shaun thornburgh" <[EMAIL PROTECTED]>, <[EMAIL PROTECTED]> Subject: RE: Help With a DATETIME Query Date: Tue, 7 Oct 2003 16:41:04 -0400 I know it's not the answer you're looking for... :( but dealing with overnights has caused me so much aggravation in past apps I've written, I've tended to make the client create two (or more) 'bookings' for the covered time... don't know if it's an option for you, but it's my $0.02. > -Original Message- > From: shaun thornburgh [mailto:[EMAIL PROTECTED] > Sent: Tuesday, October 07, 2003 4:33 PM > To: [EMAIL PROTECTED] > Subject: Help With a DATETIME Query > > > Hi, > > I have a table called Bookings which has two important columns; > Booking_Start_Date and Booking_End_Date. These columns are > both of type > DATETIME. The following query calculates 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). However, when a booking spans > more than one > day the query doesn't work, for example if a user has a > booking that starts > on day one at 09.00 and ends at 14.30 on the next day, the > query returns 3.5 > hours for both days. The query is run for each day i.e day 1, > day 2 day > 10. > > Any help here would be greatly appreciated. > > 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 WMS_Bookings B > WHERE B.User_ID = '16' > AND B.Booking_Status <> '1' > AND NOT ( '2003-10-07' < DATE_FORMAT(Booking_Start_Date, > "%Y-%m-%d") OR > '2003-10-07' > DATE_FORMAT(Booking_End_Date, "%Y-%m-%d") ) > > _ > On the move? Get Hotmail on your mobile phone > http://www.msn.co.uk/msnmobile > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Use MSN Messenger to send music and pics to your friends http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help With a DATETIME Query
I know it's not the answer you're looking for... :( but dealing with overnights has caused me so much aggravation in past apps I've written, I've tended to make the client create two (or more) 'bookings' for the covered time... don't know if it's an option for you, but it's my $0.02. > -Original Message- > From: shaun thornburgh [mailto:[EMAIL PROTECTED] > Sent: Tuesday, October 07, 2003 4:33 PM > To: [EMAIL PROTECTED] > Subject: Help With a DATETIME Query > > > Hi, > > I have a table called Bookings which has two important columns; > Booking_Start_Date and Booking_End_Date. These columns are > both of type > DATETIME. The following query calculates 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). However, when a booking spans > more than one > day the query doesn't work, for example if a user has a > booking that starts > on day one at 09.00 and ends at 14.30 on the next day, the > query returns 3.5 > hours for both days. The query is run for each day i.e day 1, > day 2 day > 10. > > Any help here would be greatly appreciated. > > 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 WMS_Bookings B > WHERE B.User_ID = '16' > AND B.Booking_Status <> '1' > AND NOT ( '2003-10-07' < DATE_FORMAT(Booking_Start_Date, > "%Y-%m-%d") OR > '2003-10-07' > DATE_FORMAT(Booking_End_Date, "%Y-%m-%d") ) > > _ > On the move? Get Hotmail on your mobile phone > http://www.msn.co.uk/msnmobile > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help With a DATETIME Query
Hi, I have a table called Bookings which has two important columns; Booking_Start_Date and Booking_End_Date. These columns are both of type DATETIME. The following query calculates 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). However, when a booking spans more than one day the query doesn't work, for example if a user has a booking that starts on day one at 09.00 and ends at 14.30 on the next day, the query returns 3.5 hours for both days. The query is run for each day i.e day 1, day 2 day 10. Any help here would be greatly appreciated. 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 WMS_Bookings B WHERE B.User_ID = '16' AND B.Booking_Status <> '1' AND NOT ( '2003-10-07' < DATE_FORMAT(Booking_Start_Date, "%Y-%m-%d") OR '2003-10-07' > DATE_FORMAT(Booking_End_Date, "%Y-%m-%d") ) _ On the move? Get Hotmail on your mobile phone http://www.msn.co.uk/msnmobile -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help With a DATETIME Query
Hi,
I would do this in the application layer something like this:
$list_of_days_you_want_to_look_at = ('mon','tue','wed'..); Foreach ($day
in $list_of_days_you_want_to_look_at) {
use a similar query to below but geared to only look at $day instead; }
You should get an output like this:
Mon: 0 hrs
Tue: 3.5 hrs
Wed: .
Hope this helps,
Cheers,
Andrew
-Original Message-
From: shaun thornburgh [mailto:[EMAIL PROTECTED]
Sent: Monday 06 October 2003 20:29
To: [EMAIL PROTECTED]
Subject: Help With a DATETIME Query
Hi,
I have a table called Bookings which has two important columns;
Booking_Start_Date and Booking_End_Date. These columns are both of type
DATETIME. The following query calculates 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). However, when a booking spans more than one
day the query doesn't work, for example if a user has a booking that starts
on day one at 09.00 and ends at 14.30 on the next day, the query returns 3.5
hours for both days. Any help here would be greatly appreciated.
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 WMS_Bookings B
WHERE B.User_ID = '16'
AND B.Booking_Status <> '1'
AND NOT ( '2003-10-07' < DATE_FORMAT(Booking_Start_Date, "%Y-%m-%d") OR
'2003-10-07' > DATE_FORMAT(Booking_End_Date, "%Y-%m-%d") )
Thanks for your help
_
On the move? Get Hotmail on your mobile phone http://www.msn.co.uk/msnmobile
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help With a DATETIME Query
Hi,
I would do this in the application layer something like this:
$list_of_days_you_want_to_look_at = ('mon','tue','wed'..);
Foreach ($day in $list_of_days_you_want_to_look_at) {
use a similar query to below but geared to only look at $day instead;
}
You should get an output like this:
Mon: 0 hrs
Tue: 3.5 hrs
Wed: .
Hope this helps,
Cheers,
Andrew
-Original Message-
From: shaun thornburgh [mailto:[EMAIL PROTECTED]
Sent: Monday 06 October 2003 20:29
To: [EMAIL PROTECTED]
Subject: Help With a DATETIME Query
Hi,
I have a table called Bookings which has two important columns;
Booking_Start_Date and Booking_End_Date. These columns are both of type
DATETIME. The following query calculates 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). However, when a booking spans more than one
day the query doesn't work, for example if a user has a booking that starts
on day one at 09.00 and ends at 14.30 on the next day, the query returns 3.5
hours for both days. Any help here would be greatly appreciated.
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 WMS_Bookings B
WHERE B.User_ID = '16'
AND B.Booking_Status <> '1'
AND NOT ( '2003-10-07' < DATE_FORMAT(Booking_Start_Date, "%Y-%m-%d") OR
'2003-10-07' > DATE_FORMAT(Booking_End_Date, "%Y-%m-%d") )
Thanks for your help
_
On the move? Get Hotmail on your mobile phone http://www.msn.co.uk/msnmobile
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help With a DATETIME Query
Hi, I have a table called Bookings which has two important columns; Booking_Start_Date and Booking_End_Date. These columns are both of type DATETIME. The following query calculates 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). However, when a booking spans more than one day the query doesn't work, for example if a user has a booking that starts on day one at 09.00 and ends at 14.30 on the next day, the query returns 3.5 hours for both days. Any help here would be greatly appreciated. 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 WMS_Bookings B WHERE B.User_ID = '16' AND B.Booking_Status <> '1' AND NOT ( '2003-10-07' < DATE_FORMAT(Booking_Start_Date, "%Y-%m-%d") OR '2003-10-07' > DATE_FORMAT(Booking_End_Date, "%Y-%m-%d") ) Thanks for your help _ On the move? Get Hotmail on your mobile phone http://www.msn.co.uk/msnmobile -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help With a DATETIME Query
Hi, I have a table called Bookings which has two important columns; Booking_Start_Date and Booking_End_Date. These columns are both of type DATETIME. The following query calculates 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). However, when a booking spans more than one day the query doesn't work, for example if a user has a booking that starts on day one at 09.00 and ends at 14.30 on the next day, the query returns 3.5 hours for both days. Any help here would be greatly appreciated. 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 WMS_Bookings B WHERE B.User_ID = '16' AND B.Booking_Status <> '1' AND NOT ( '2003-10-07' < DATE_FORMAT(Booking_Start_Date, "%Y-%m-%d") OR '2003-10-07' > DATE_FORMAT(Booking_End_Date, "%Y-%m-%d") ) Thanks for your help _ Get Hotmail on your mobile phone http://www.msn.co.uk/msnmobile -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
