CODEWITHSUNDEEP
mysqltime
Jose Miguel Ledón
Jose Miguel Ledón

Reputation: 309

Compare time values correctly

I am having trouble comparing MySQL Time fields, I have a NOEVENTS row starting from 09:00 to 10:00 and I am trying to insert a row into another table, but I need to compare the NOEVENTS time, to the incoming Time fields. example:

I want to insert a event from 09:15 to 09:30, and another from 10:00 to 11:00, so how can I compare that? im trying this:

select count(idNOEVENTS) into existNOEVENT from NOEVENTS  b where eventInitialTime && eventFinalTime between b.initialTime and b.finalTime;
if existNOEVENT>0
SET MESSAGE_TEXT = 'Error! no events allowed at this time';

The problem is when I try to insert the 09:15 to 09:30, it throws the error message shown above. Thats fine, it works, but when I try to insert the 10:00 to 11:00 event it throw the msg, and it shouldnt, how can I fix it? or compare it better?

Thanks, and sorry for my poor english.

Upvotes: 1

Views: 92

Answers (1)

peterm
peterm

Reputation: 92845

You can do it this way

SELECT *
  FROM noevents
 WHERE (? > initialTime AND ? < finalTime)
    OR (? > initialTime AND ? < finalTime)

Now don't use COUNT(*) to check for existence of a row(s). Use EXIST() instead.

That being said to give an idea this how you procedure code might look like

DELIMITER $$
CREATE PROCEDURE add_event(IN _start TIME, IN _end TIME)
BEGIN  
  IF EXISTS(SELECT *
              FROM noevents
             WHERE (_start > initialTime AND _start < finalTime)
                OR (_end > initialTime AND _end < finalTime)) THEN
    SIGNAL sqlstate '45000' 
      SET message_text = 'Error! no events allowed at this time';
  ELSE
    -- Do whatever you have to do to insert an event
    SELECT 'Success: event added';
  END IF;
END$$
DELIMITER ;

Here is SQLFiddle demo

Upvotes: 1

Related Questions