MySQL Forums
Forum List  »  Newbie

Re: Date time help please
Posted by: Peter Brawley
Date: June 10, 2014 11:26AM

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.

Options: ReplyQuote


Subject
Written By
Posted
June 10, 2014 12:38AM
Re: Date time help please
June 10, 2014 11:26AM
June 10, 2014 01:25PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.