Between set times?

Hello,

Wondering if anyone can help as i’m really confused. I want to run two dynamic queries in the same format for half an hour time slots to output all records in the database where the start time is between:

  • 12.00pm and 12.30pm.
    and
  • 12.30pm and 13.00pm.

So I have a record in my database where the estimated_start_time is 11:45pm and estimated_end_time is 12:45pm

Now if I run the query I have written for between 12.00pm and 12.30pm a result is returned:


SELECT * FROM `reps_schedule` WHERE estimated_start_time <= '2011-01-10 12:00:00' AND estimated_end_time >= '2011-01-10 12:30:00' AND user_id = 12

But that same style query it would not run for my 12.30pm and 13.00pm?


SELECT * FROM `reps_schedule` WHERE estimated_start_time <= '2011-01-10 12:30:00' AND estimated_end_time >= '2011-01-10 13:00:00' AND user_id = 12

Anyone kindly help?

Thanks

would not run? really?

what error message did you get?

Sorry, when I say will not run I mean returns no results, even though a record shows the estimated_end_time is 11:45:00 and estimated_end_time is 12:45:00

SELECT * FROM `reps_schedule` WHERE estimated_start_time <= '2011-01-10 12:30:00' AND estimated_end_time >= '2011-01-10 13:00:00' AND user_id = 12

Thanks

could you do a SHOW CREATE TABLE for this table please

Hi, Thanks for this. Is this what you need?


CREATE TABLE IF NOT EXISTS `reps_schedule` (
  `schedule_id` int(8) NOT NULL auto_increment,
  `user_id` int(4) NOT NULL,
  `for_title` int(4) default NULL,
  `appointment_type` int(1) default NULL,
  `company` varchar(600) NOT NULL,
  `meeting` varchar(400) NOT NULL,
  `location` varchar(600) NOT NULL,
  `postcode` varchar(255) default NULL,
  `the_date` date default NULL,
  `estimated_start_time` datetime default NULL,
  `estimated_end_time` datetime default NULL,
  `about` text NOT NULL,
  `feedback` text NOT NULL,
  `live` int(1) NOT NULL COMMENT '1 = Happening, 2= Cancelled',
  PRIMARY KEY  (`schedule_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1008 ;

yeah, that looks okay

do you know how to dump rows? dump a few for me, and make sure the one you mentioned in post #3 is included

Yep - sure thanks again. The last one shows 11:45 - 12:45pm:


INSERT INTO `reps_schedule` (`schedule_id`, `user_id`, `for_title`, `appointment_type`, `company`, `meeting`, `location`, `postcode`, `the_date`, `estimated_start_time`, `estimated_end_time`, `about`, `feedback`, `live`) VALUES
(1020, 12, 51, 1, 'Another Place', 'jack', 'Area', 'PO5', '2011-01-11', '2011-01-11 09:40:00', '2011-01-11 10:15:00', 'details here', '', 1),
(1021, 12, 51, 1, 'Some Place', '.', 'Area', 'PO5', '2011-01-11', '2011-01-11 10:30:00', '2011-01-11 12:15:00', 'details here', '', 1),
(1022, 12, 51, 6, 'Place Again', 'julian', 'Area', 'PO5', '2011-01-11', '2011-01-11 12:30:00', '2011-01-11 12:45:00', 'details here', '', 1),
(1023, 12, 51, 1, 'New Place', 'george', 'Area', 'PO5', '2011-01-11', '2011-01-11 12:55:00', '2011-01-11 13:15:00', 'details here', '', 1),
(1024, 12, 51, 1, 'Round Road', 'emma', 'Area', 'PO5', '2011-01-11', '2011-01-11 13:40:00', '2011-01-11 14:15:00', 'details here', '', 1),
(1025, 12, 51, 1, 'Some Place', 'mr Jones', 'Area', 'P02', '2011-01-11', '2011-01-11 14:30:00', '2011-01-11 15:30:00', 'details here', '', 1),
(1026, 12, 51, 1, 'Fred Jones', 'george', 'Area', 'PO5', '2011-01-11', '2011-01-11 11:45:00', '2011-01-11 12:45:00', 'details here', '', 1);

okay, i tried the query from post #3

SELECT * 
  FROM `reps_schedule` 
 WHERE estimated_start_time <= '2011-01-10 12:30:00' 
   AND estimated_end_time   >= '2011-01-10 13:00:00' 
   AND user_id = 12

and of course it returned 0 rows on the data you provided, which was all for the next day

so i tried it with this query instead –

SELECT * 
  FROM `reps_schedule` 
 WHERE estimated_start_time <= '2011-01-[COLOR="Red"]11[/COLOR] 12:30:00' 
   AND estimated_end_time   >= '2011-01-[COLOR="red"]11[/COLOR] 13:00:00' 
   AND user_id = 12

and again it returned no rows, because none of them fit that particular range

so then i did

UPDATE reps_schedule
SET estimated_start_time = estimated_start_time - INTERVAL 3 HOUR

and ran the query again and it worked fine

so i guess your problem was that none of the data matched your query requirements

Thanks and sorry to be a pain. I apologise I don’t think i’ve explained this too well. I want to check if half an hour slots are filled for meetings on 2011-01-11:

So for example:

11:30:00 - 12:00:00
12:00:00 - 12:30:00
12:30:00 - 13:00:00

So with this row:


(1026, 12, 51, 1, 'Fred Jones', 'george', 'Area', 'PO5', '2011-01-11', '2011-01-11 11:45:00', '2011-01-11 12:45:00', 'details here', '', 1);

The meeting starts at 11:45:00 and runs till 12:45:00 so each of the above timeslots would return a row, as it falls in each of those slots.

If that makes sense?

But I need to create a query for each of those timeslots which allows for this, which is where i’m stuck?

Thanks

finally, we arrive at the real problem :slight_smile:

you’re trying to determine if there are overlaps, not just retrieving row where some value is between two values

have a look at one of thes threads…

http://www.sitepoint.com/forums/showthread.php?t=607741

notice a pattern? :smiley:

Thanks, I get it so I pretty much reverse it looking for:


SELECT *
FROM `reps_schedule`
WHERE estimated_end_time >= '2011-01-11 12:00:00'
AND estimated_start_time <= '2011-01-11 12:30:00'
AND user_id =12

Thanks for helping me (again!)