SQL is pretty bad with time. There's no easy remedy---you just have to slog through the logical twists and turns.
Your problem is period arithmetic; for examples see that topic, also 'Scheduling', at
http://www.artfulsoftware.com/queries.php.
Assuming you meant not to duplicate `clockdate` data in `clocktime`, we have ...
drop table if exists tbl;
create table tbl( empno smallint, ClockDate date, ClockTime time, ClockType char(1) );
insert into tbl values
(1, '2014-05-15', '09:00:00', 'I' ),
(1, '2014-05-15', '11:00:00', 'O' ),
(1, '2014-05-15', '12:30:00', 'I' ),
(1, '2014-05-15', '19:00:00', 'O' );
Use a self-join to play the ins and outs against the given datetime ...
select if(count(1),'Yes','No' ) as InStore
from tbl a
join tbl b using(empno, clockdate)
where empno=1
and a.clockdate='2014-5-15'
and a.clocktime<='12:30:00' and a.clocktype='I'
and b.clocktime>'12:30:00' and b.clocktype='O';
+---------+
| InStore |
+---------+
| Yes |
+---------+
Edited 1 time(s). Last edit at 06/10/2014 11:34AM by Peter Brawley.