Between set times?


#1

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


#2

would not run? really?

what error message did you get?


#3

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


#4

could you do a SHOW CREATE TABLE for this table please


#5

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 ;


#6

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


#7

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);


#8

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 &lt;= '2011-01-<font color='"Red"'>11</font> 12:30:00' 
   AND estimated_end_time   &gt;= '2011-01-<font color='"red"'>11</font> 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


#9

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


#10

finally, we arrive at the real problem 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=664040
http://www.sitepoint.com/forums/showthread.php?t=631475
http://www.sitepoint.com/forums/showthread.php?t=607741

notice a pattern? smiley


#11

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

SELECT *
FROM `reps_schedule`
WHERE estimated_end_time &gt;= '2011-01-11 12:00:00'
AND estimated_start_time &lt;= '2011-01-11 12:30:00'
AND user_id =12

Thanks for helping me (again!)