0
CREATE TABLE `Slot` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `starttime` datetime NOT NULL,
  `endtime` datetime NOT NULL,
  `shift_Id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

I have a stored procedure where I have a Temporary Table like :

TempSlot

| starttime              | endtime             |
| 2018-10-25 15:00:00    | 2018-10-25 16:00:00 |
| 2018-10-25 13:00:00    | 2018-10-25 14:00:00 |
| 2018-10-26 12:00:00    | 2018-10-26 13:00:00 |

Now, I need to check if any entry from TempSlot is present in the Slot table such that inserting it will create a conflict.

Am not sure how to write this query in stored procedure :

I want to write something like this :

SELECT COUNT(*)  FROM Slot
WHERE 
(((starttime >= '2018-10-25 15:00:00' and starttime < '2018-10-25 16:00:00') 
or
(endtime > '2018-10-25 15:00:00' and endtime <= '2018-10-25 16:00:00'))
OR
((starttime >= '2018-10-25 13:00:00' and starttime < '2018-10-25 14:00:00') 
or
(endtime > '2018-10-25 13:00:00' and endtime <= '2018-10-25 14:00:00'))
OR((starttime >= '2018-10-26 12:00:00' and starttime < '2018-10-26 13:00:00') 
or
(endtime > '2018-10-26 12:00:00' and endtime <= '2018-10-26 13:00:00'))
)

But I want a generic query. What will be a good way to write this ?

1
SELECT COUNT(*)
FROM Slots s, TempSlots t
WHERE (s.starttime < t.endtime AND t.starttime < s.endtime)
   OR (s.starttime > t.endtime AND t.starttime > s.endtime)

This query counts the overlapping pairs (one record from Slots overlapped one record from TempSlots) only.

To increase the performance you may try to divide this query by two UNIONed queries escaping OR.

1

For overlapping time ranges (a shorter expression, but still not indexable):

WHERE a.start < b.end AND a.end > b.start 

Don't count how many rows overlap, just ask whether any rows overlap. In a Stored Procedure:

IF ( EXISTS ( SELECT 1 FROM ... )) THEN
    ...
END IF;
  • Thanks Rick for the suggestion. I will integrate the shorter expression. – j10 Nov 2 '18 at 7:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.