[Resolved] SELECT statement to exclude datetime range

hthighway

I'm having trouble with a select statement that I'm hoping someine can help with.

Here's is the situation

I have a table with a game schedule contain "hometeam","awayteam",gamestart","gameend"

where gamestart and gameend are datetime fields "yyyy-mm-dd hh:mm"

I would like to find teams for a give time period on a given day that would not have a game that conflicted with the selected time period.

i.e. on 09/06/2003 between 10:00 and 13:00 I want to find all hometeams that would not have game times that conficted with and part of the above time frame.

Thanks in advance,

-Paul

Sxooter

Note I use the "timestamp" type here, which in Postgresql and all other ANSI SQL databases is NOT autoupdating. In MySQL, you have to use the non-standard date_time or whatever type they use to name a timestamp...

=> create table test (team text, start timestamp, stop timestamp);
CREATE TABLE
=> insert into test values ('ravens','2003-06-06 12:00','2003-06-06 15:30');
INSERT 17289 1
=> insert into test values ('slatterns','2003-06-07 09:00','2003-06-07 11:30');
INSERT 17290 1
=> insert into test values ('trollops','2003-06-07 12:15','2003-06-07 14:45');
INSERT 17291 1
=> select * from test where '2003-06-07 10:00' not between start and stop;
team | start | stop
----------+---------------------+---------------------
ravens | 2003-06-06 12:00:00 | 2003-06-06 15:30:00
trollops | 2003-06-07 12:15:00 | 2003-06-07 14:45:00

How's that?

hthighway

select * from test where '2003-06-07 10:00' not between start and stop

That's good for a specific time but what if I want to exclude a time range (10:00 to 13:00) for a given date?

Thanks,

-Paul

Sxooter

Just add an "AND" and the same thing again, i.e.

select * from table where '$date_time_start' not between start and finish AND '$date_time_stop' not between start and finish

hthighway

select * from table where '$date_time_start' not between start and finish AND '$date_time_stop' not between start and finish

That's what I thought too but the result returns rows that fall between my time period.

Sxooter

Hmmm. Try putting the not outside parens like so

select * from table where not (big old ANDED between thing goes here);

hthighway

Here is the SQL statement so far.
It is still returning rows that fall in the excluded time range

SELECT ghome,gdate,gend FROM `schedules` 
WHERE NOT ('2003-09-06 10:00' BETWEEN gdate AND gend) 
AND NOT('2003-09-06 12:00' BETWEEN gdate AND gend) 
AND (TO_DAYS(gdate) = TO_DAYS('2003-09-06'))

Sxooter

Those fields are actual date_time fields right? Try using a smaller test, like

select * From table where '$date' not between start and stop;

and see if that works right.

hthighway

This is odd, it seems the BETWEEN is returning the values in the start column that equal the variable.

So I added an other AND to excudle games that start at the variable time

SELECT ghome,gdate,gend 
FROM `schedules` 
WHERE (('2003-09-06 10:00' NOT BETWEEN gdate AND gend) AND '2003-09-06 10:00' != gdate) 
AND (TO_DAYS(gdate) = TO_DAYS('2003-09-06'))

Now I'll try it with both parts of the time frame

tekky

would this be his problem...

WHERE NOT ('2003-09-06 10:00' BETWEEN gdate AND gend)  

AND NOT('2003-09-06 12:00' BETWEEN gdate AND gend)

should those be...

WHERE ('2003-09-06 10:00' NOT BETWEEN gdate AND gend)  

AND ('2003-09-06 12:00' NOT BETWEEN gdate AND gend)

😕

hthighway

Well if I only try a single time (not a time range) it works.

SELECT ghome,gdate,gend 
FROM `schedules` 
WHERE
(('2003-09-06 10:00' NOT BETWEEN gdate AND gend) 
AND '2003-09-06 10:00' != gdate) 
AND (TO_DAYS(gdate) = TO_DAYS('2003-09-06'))

But if I try to add another time into the mix I start getting rows that should be excluded

tekky

try enclosing the whole WHERE statement in ()'s?

WHERE 
((('2003-09-06 10:00' NOT BETWEEN gdate AND gend)  

AND '2003-09-06 10:00' != gdate)  

AND (TO_DAYS(gdate) = TO_DAYS('2003-09-06')))

I know its farfetched... but maybe its parsing it funny since you have half it grouped together and the other half not.... shrug

Sxooter

Yeah, I was saying to do it kinda more like this:

select *
from table
where
(
NOT
('1sttime' between start and finish)
AND
('2ndtime' between start and finish)
)
AND whatever else you need;

tekky

Originally posted by Sxooter
Yeah, I was saying to do it kinda more like this:

select *
from table
where
(
NOT
('1sttime' between start and finish)
AND
('2ndtime' between start and finish)
)
AND whatever else you need;

ahh i understand that 😉 (should work too I would think)

ultraslacker

If both 1sttime and 2ndtime should not fall between start and finish, then change the syntax, as this should be false unless both expressions are false -

NOT (('1sttime' between start and finish)
OR
('2ndtime' between start and finish))

hthighway

If both 1sttime and 2ndtime should not fall between start and finish, then change the syntax, as this should be false unless both expressions are false -

Im try to get all of the time between 1sttime and 2ndtime to return false if it falls bewteen the start and finish time of a row

hthighway

Here's a litle sample incase anyone want to give it try

Table Structure:

CREATE TABLE schedules (
  idx int(11) NOT NULL auto_increment,
  gdate datetime NOT NULL default '0000-00-00 00:00:00',
  gend datetime NOT NULL default '0000-00-00 00:00:00',
  ghome varchar(20) default NULL,
  gaway varchar(20) default NULL,
  UNIQUE KEY idx (idx),
  KEY gdate (gdate)
) TYPE=ISAM PACK_KEYS=1;

Sample Data:

INSERT INTO schedules VALUES (1, '2003-09-06 14:15:00', '2003-09-06 15:15:00', '581', '586');
INSERT INTO schedules VALUES (2, '2003-09-06 11:45:00', '2003-09-06 12:45:00', '579', '583');
INSERT INTO schedules VALUES (3, '2003-09-06 13:00:00', '2003-09-06 14:00:00', '580', '582');
INSERT INTO schedules VALUES (4, '2003-09-06 10:30:00', '2003-09-06 11:30:00', '578', '584');
INSERT INTO schedules VALUES (5, '2003-09-06 09:15:00', '2003-09-06 10:15:00', '577', '585');
INSERT INTO schedules VALUES (6, '2003-09-06 14:15:00', '2003-09-06 15:15:00', '569', '574');
INSERT INTO schedules VALUES (7, '2003-09-06 08:00:00', '2003-09-06 09:00:00', '575', '576');
INSERT INTO schedules VALUES (8, '2003-09-06 13:00:00', '2003-09-06 14:00:00', '568', '570');
INSERT INTO schedules VALUES (9, '2003-09-06 11:45:00', '2003-09-06 12:45:00', '567', '571');
INSERT INTO schedules VALUES (10, '2003-09-06 09:15:00', '2003-09-06 10:15:00', '565', '573');
INSERT INTO schedules VALUES (11, '2003-09-06 10:30:00', '2003-09-06 11:30:00', '566', '572');
INSERT INTO schedules VALUES (12, '2003-09-06 08:00:00', '2003-09-06 09:00:00', '563', '564');
INSERT INTO schedules VALUES (13, '2003-09-06 14:15:00', '2003-09-06 15:15:00', '557', '562');
INSERT INTO schedules VALUES (14, '2003-09-06 11:45:00', '2003-09-06 12:45:00', '555', '559');
INSERT INTO schedules VALUES (15, '2003-09-06 13:00:00', '2003-09-06 14:00:00', '556', '558');
INSERT INTO schedules VALUES (16, '2003-09-06 10:30:00', '2003-09-06 11:30:00', '554', '560');
INSERT INTO schedules VALUES (17, '2003-09-06 09:15:00', '2003-09-06 10:15:00', '553', '561');
INSERT INTO schedules VALUES (18, '2003-09-06 08:00:00', '2003-09-06 09:00:00', '551', '552');
INSERT INTO schedules VALUES (19, '2003-09-06 15:00:00', '2003-09-06 15:50:00', '629', '630');
INSERT INTO schedules VALUES (20, '2003-09-06 14:00:00', '2003-09-06 14:50:00', '622', '628');
INSERT INTO schedules VALUES (21, '2003-09-06 12:00:00', '2003-09-06 12:50:00', '620', '624');
INSERT INTO schedules VALUES (22, '2003-09-06 13:00:00', '2003-09-06 13:50:00', '621', '623');
INSERT INTO schedules VALUES (23, '2003-09-06 11:00:00', '2003-09-06 11:50:00', '619', '625');
INSERT INTO schedules VALUES (24, '2003-09-06 10:00:00', '2003-09-06 10:50:00', '618', '626');
INSERT INTO schedules VALUES (25, '2003-09-06 08:00:00', '2003-09-06 08:50:00', '615', '616');
INSERT INTO schedules VALUES (26, '2003-09-06 09:00:00', '2003-09-06 09:50:00', '617', '627');
INSERT INTO schedules VALUES (27, '2003-09-06 15:00:00', '2003-09-06 15:50:00', '608', '614');
INSERT INTO schedules VALUES (28, '2003-09-06 14:00:00', '2003-09-06 14:50:00', '607', '609');
INSERT INTO schedules VALUES (29, '2003-09-06 12:00:00', '2003-09-06 12:50:00', '605', '611');
INSERT INTO schedules VALUES (30, '2003-09-06 13:00:00', '2003-09-06 13:50:00', '606', '610');
INSERT INTO schedules VALUES (31, '2003-09-06 11:00:00', '2003-09-06 11:50:00', '604', '612');
INSERT INTO schedules VALUES (32, '2003-09-06 10:00:00', '2003-09-06 10:50:00', '603', '613');
INSERT INTO schedules VALUES (33, '2003-09-06 15:00:00', '2003-09-06 15:50:00', '638', '646');
INSERT INTO schedules VALUES (34, '2003-09-06 09:00:00', '2003-09-06 09:50:00', '601', '602');
INSERT INTO schedules VALUES (35, '2003-09-06 14:00:00', '2003-09-06 14:50:00', '637', '639');
INSERT INTO schedules VALUES (36, '2003-09-06 13:00:00', '2003-09-06 13:50:00', '636', '640');
INSERT INTO schedules VALUES (37, '2003-09-06 11:00:00', '2003-09-06 11:50:00', '634', '642');
INSERT INTO schedules VALUES (38, '2003-09-06 12:00:00', '2003-09-06 12:50:00', '635', '641');
INSERT INTO schedules VALUES (39, '2003-09-06 10:00:00', '2003-09-06 10:50:00', '633', '643');
INSERT INTO schedules VALUES (40, '2003-09-06 09:00:00', '2003-09-06 09:50:00', '632', '644');
INSERT INTO schedules VALUES (41, '2003-09-06 08:00:00', '2003-09-06 08:50:00', '631', '645');
INSERT INTO schedules VALUES (42, '2003-09-06 12:00:00', '2003-09-06 12:50:00', '722', '724');
INSERT INTO schedules VALUES (43, '2003-09-06 13:00:00', '2003-09-06 13:50:00', '723', '730');
INSERT INTO schedules VALUES (44, '2003-09-06 11:00:00', '2003-09-06 11:50:00', '721', '725');
INSERT INTO schedules VALUES (45, '2003-09-06 09:00:00', '2003-09-06 09:50:00', '719', '727');
INSERT INTO schedules VALUES (46, '2003-09-06 10:00:00', '2003-09-06 10:50:00', '720', '726');
INSERT INTO schedules VALUES (47, '2003-09-06 08:00:00', '2003-09-06 08:50:00', '718', '728');
INSERT INTO schedules VALUES (48, '2003-09-06 12:00:00', '2003-09-06 12:50:00', '715', '716');
INSERT INTO schedules VALUES (49, '2003-09-06 13:00:00', '2003-09-06 13:50:00', '717', '729');
INSERT INTO schedules VALUES (50, '2003-09-06 11:00:00', '2003-09-06 11:50:00', '708', '714');
INSERT INTO schedules VALUES (51, '2003-09-06 09:00:00', '2003-09-06 09:50:00', '706', '710');
INSERT INTO schedules VALUES (52, '2003-09-06 10:00:00', '2003-09-06 10:50:00', '707', '709');
INSERT INTO schedules VALUES (53, '2003-09-06 08:00:00', '2003-09-06 08:50:00', '705', '711');
INSERT INTO schedules VALUES (54, '2003-09-06 12:00:00', '2003-09-06 12:50:00', '703', '713');
INSERT INTO schedules VALUES (55, '2003-09-06 13:00:00', '2003-09-06 13:50:00', '704', '712');
INSERT INTO schedules VALUES (56, '2003-09-06 11:00:00', '2003-09-06 11:50:00', '701', '702');
INSERT INTO schedules VALUES (57, '2003-09-06 14:00:00', '2003-09-06 15:20:00', '308', '310');
INSERT INTO schedules VALUES (58, '2003-09-06 12:30:00', '2003-09-06 13:50:00', '307', '311');
INSERT INTO schedules VALUES (59, '2003-09-06 11:00:00', '2003-09-06 12:20:00', '306', '312');
INSERT INTO schedules VALUES (60, '2003-09-06 09:30:00', '2003-09-06 10:50:00', '305', '313');
INSERT INTO schedules VALUES (61, '2003-09-06 08:00:00', '2003-09-06 09:20:00', '304', '314');
INSERT INTO schedules VALUES (62, '2003-09-06 14:00:00', '2003-09-06 15:20:00', '303', '315');

ultraslacker

Originally posted by hthighway
Im try to get all of the time between 1sttime and 2ndtime to return false if it falls bewteen the start and finish time of a row

Then use OR rather than AND. A conflict occurs if at least one of the times fall between qdate and qend, or both straddle the time.

SELECT * FROM schedules
WHERE ( NOT (('2003-09-06 14:00:00' between gdate AND gend ) OR ('2003-09-06 15:20:00' between gdate AND gend) or ('2003-09-06 14:00:00' < gdate AND '2003-09-06 15:20:00' > gend) ) AND to_days('2003-09-06') = to_days(gdate));

hthighway

SELECT * FROM schedules
WHERE ( NOT (('2003-09-06 14:00:00' between gdate AND gend ) OR ('2003-09-06 15:20:00' between gdate AND gend) or ('2003-09-06 14:00:00' < gdate AND '2003-09-06 15:20:00' > gend) ) AND to_days('2003-09-06') = to_days(gdate));

This one did the trick!

Thanks for everyones help.

-Paul

Sxooter

You might wanna do a <= and >= in a few places to eliminate corner cases of the exact time at the end of the ranges...

Glad you got it working. Sometimes these date / time things are a lot uglier than they at first appear.