Yesterday I had this problem: I have a small application that logs when I switch my computer on and off. I have a webpage where I can change the times logged and also add remarks like what I did that day. So a simple query can calculate my workhours for that day. But I should work 8 hours a day at my current job. So what if I make more or less? I want to be able to have an extra column that is the sum of the daily diff.
So I build the following solution. These are sketches. Do not expect these things to work out of the box. But if you know a little about Linux and MySQL you should have no problem pasting it together.
So the MySQL table looks like this:
CREATE TABLE `hours` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`workday` date DEFAULT NULL,
`start` time DEFAULT NULL,
`stop` time DEFAULT NULL,
`worked` time DEFAULT NULL,
`saldo` time DEFAULT NULL,
`som` time DEFAULT NULL,
`remark` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `workday` (`workday`)
)
The startup/shutdown registration script looks like this:
case “$1” in
start)
echo -n “Registering workhour start: ”
mysql -u workedhours -pworkedhours -e ‘insert into workedhours.hours set workday=curdate(),start=now();’
echo “OK”
;;
stop)
echo -n “Registering workhour end: ”
mysql -u workedhours -pworkedhours -e “use workedhours;update hours set stop=now() where workday=curdate(); update hours set worked=timediff(timediff(stop,start),’0:30:0′) where workday=curdate();update hours set saldo=timediff(worked,’8:00:00′) where workday=curdate();”
echo “OK”
Challenge
Now how can I calculate the accumulated time diffs? I am not a DBA but I guessed I should use what they call ‘cursors’. That is the only thing to loop through a set of records. But they can only be used in MySQL in a stored procedure. Ah, two things I have never used before 🙂
Anyway, the stored procedure is as follows:
DELIMITER ;;
/*!50003 CREATE*/ /*!50020 DEFINER=`jbaten`@`localhost`*/ /*!50003 PROCEDURE `calcsum`()
BEGIN
declare l_proc_id varchar(100) default ’test_calcsum’;
declare done INT DEFAULT 0;
DECLARE id1 INT DEFAULT 0;
DECLARE som time default “0:0:0″ ;
DECLARE timeworked TIME;
DECLARE cur1 CURSOR FOR SELECT id,saldo FROM workedhours.hours order by workday;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
call debug.debug_on(l_proc_id);
OPEN cur1;
read_loop: LOOP
FETCH cur1 INTO id1,timeworked;
select ifnull(timeworked,”0:0:0”) into timeworked;
— if timeworked <> null then
call debug.debug_insert(l_proc_id,concat(“stap 1:id=”,id1,”->timeworked=”,timeworked,”som=”,som));
select addtime(som,timeworked) INTO som;
call debug.debug_insert(l_proc_id,concat(“stap 2:som->”,som));
— else
— call debug.debug_insert(l_proc_id,concat(“timeworked=null->”,timeworked));
— end if;IF done THEN
— call debug.debug_insert(l_proc_id,concat(“done:”,done));
LEAVE read_loop;
END IF;
— call debug.debug_insert(l_proc_id,concat(‘som=’,DATE_FORMAT(som, ‘%d-%b-%y %h:%i:%S %p’)));
update workedhours.hours set som=som where id=id1;
END LOOP;
CLOSE cur1;
— call debug.debug_insert(l_proc_id,’Ending procedure calcsum’);
— call debug.debug_off(l_proc_id);
END */;;
DELIMITER ;
More info about these ‘call debug’ things are found here. It helps you to debug stuff. Anyway, it all works. Have fun with it.