How to calculate the total working hours of an employee from a set or records

**krishnaanu** · Oct 31, 2009 01:21

hi everybody i am chaitanya. i have a problem, please help me with it.

i have a table named emp_attendance. it contains emp_id,in_time,out_time.

emp_id varchar(20)
att_date date
in_time timestamp
out_time timestamp

the sample details are

emp_id] att_date in_time out_time
1C00A5FD44 2009-10-31 2009-10-31 11:30:00 2009-10-31 12:00:00
1C00A5FD44 2009-10-31 2009-10-31 12:30:00 2009-10-31 13:00:00
1C00A5FD44 2009-10-31 2009-10-31 13:30:00 2009-10-31 14:00:00
1C00A5FD44 2009-10-31 2009-10-31 14:30:00 2009-10-31 15:00:00
1C00A5FD44 2009-10-31 2009-10-31 15:30:00 2009-10-31 16:00:00

i want the employee total working hours:minutes:seconds for a given date. for example for 2009-10-31 the employee working hours is 02:30:00.

please help me with this. thank you all in advance.

**r937** · Oct 31, 2009 02:38

Code:

SELECT emp_id 
     , SEC_TO_TIME(SUM(TIME_TO_SEC(out_time)
                      -TIME_TO_SEC(in_time))) AS working_hours
  FROM emp_attendance
GROUP
    BY emp_id

**krishnaanu** · Oct 31, 2009 05:59

r937: thank you very much sir

**krishnaanu** · Oct 31, 2009 06:25

r937: 1C00A5FD44 2009-10-31 2009-10-31 15:30:00 2009-01-11 15:30:00

sir, if the in_time and out_time are two different dates the query is not showing the time difference.

for example if the employee logged in on 2009-10-31 15:30:00 and logged out on 2009-11-01 15:30:00, then the time difference is 24:00:00. but this query is showing 00:00:00.

please help me sir.

**r937** · Nov 1, 2009 07:05

Code:

SELECT emp_id 
     , CONCAT(
       CASE WHEN SUM(UNIX_TIMESTAMP(out_time)
                    -UNIX_TIMESTAMP(in_time)) > 86400
            THEN CONCAT('',FLOOR(
                 SUM(UNIX_TIMESTAMP(out_time)
                    -UNIX_TIMESTAMP(in_time)) / 86400 )
                          , ' days, ' )
            ELSE '' END
        , SEC_TO_TIME(MOD(
                 SUM(UNIX_TIMESTAMP(out_time)
                    -UNIX_TIMESTAMP(in_time)) , 86400 )
             ) ) AS working_hours
  FROM emp_attendance
GROUP
    BY emp_id

**krishnaanu** · Nov 1, 2009 23:11

r973: sir, i am sorry to say. this query is not working. my operating system is windows xp. please help me.

**r937** · Nov 2, 2009 03:24

Originally Posted by krishnaanu

please help me.

sure

please provide a dump of some data -- the CREATE TABLE and INSERT statements

**krishnaanu** · Nov 2, 2009 13:04

r937: sit i am currently doin a project, which tracks the employee attendance using a RFID module. when ever an employee swipes his card, if he is in then the out time is noted, if he is out then the in coming time will be noted.

based on the employee attendance at the end of the month his salary will be calculated. salary will be calculated on hourly basis. so I need to know the exact worked hours for a particular employee.

i calculated the worked hours with java coding. but i thought that it will not be fine.... java calculating the hours...it is not fine. it will become an extra burden for the application. so i decided to do it in the database level. please help me sir.

sir this is the sample data and the table structure.

Code:

create table emp_attendance(trans_id bigint primary key not null,
                                         emp_card_id varchar(20) not null,
                                         att_date date not null,
                                         in_time timestamp not null,
                                         out_time timestamp null,
                                         status smallint not null,
foreign key(emp_card_id) references emp_details(emp_card_id))

;

if employee is entering:

Code:

insert into emp_attendance values(1,<<emp_card_id>>,current_date,now(),null,0);

if employee is leaving:

Code:

update emp_attendance set out_time=now() where emp_card_id=<<emp_card_id>> and out_time is null and att_date=current_date;

sample data:

Code:

trans_id   emp_card_id      in_time                       out_time                      status
1            1C00EDF12D      2009-11-03 10:30:21    2009-11-03 12:30:31     0
2            1C00EDF12D      2009-11-03 13:40:00    2009-11-03 13:50:00     0
3            1C00EDF12D      2009-11-03 14:10:00    2009-11-03 14:25:00     0
4            1C00EDF12D      2009-11-03 14:30:21    2009-11-03 14:40:31     0
5            1C00EDF12D      2009-11-03 16:30:21    2009-11-03 17:30:31     0

the difference in the 1st record is 02:00:10
the difference in the 2nd record is 00:10:00
the difference in the 34d record is 00:15:00
the difference in the 4th record is 00:10:10
the difference in the 5th record is 01:00:10

finally the employee worked 03:35:30(three hours 35 minutes 30 seconds. i want the output like this.

please help me sir. waiting for your reply. thank you in advance sir.

**r937** · Nov 2, 2009 22:51

okay, i used your sample data, and my first query worked fine --

Code:

CREATE TABLE emp_attendance
( trans_id BIGINT PRIMARY KEY NOT NULL
, emp_card_id VARCHAR(20) NOT NULL
, att_date DATE NOT NULL
, in_time TIMESTAMP NOT NULL
, out_time TIMESTAMP NULL
, status SMALLINT NOT NULL
);
INSERT INTO emp_attendance 
( trans_id,emp_card_id,in_time,out_time,status)
VALUES
 (1,'1C00EDF12D','2009-11-03 10:30:21','2009-11-03 12:30:31',0)
,(2,'1C00EDF12D','2009-11-03 13:40:00','2009-11-03 13:50:00',0)
,(3,'1C00EDF12D','2009-11-03 14:10:00','2009-11-03 14:25:00',0)
,(4,'1C00EDF12D','2009-11-03 14:30:21','2009-11-03 14:40:31',0)
,(5,'1C00EDF12D','2009-11-03 16:30:21','2009-11-03 17:30:31',0)
;
SELECT emp_card_id 
     , SEC_TO_TIME(SUM(TIME_TO_SEC(out_time)
                      -TIME_TO_SEC(in_time))) AS working_hours
  FROM emp_attendance
GROUP
    BY emp_card_id;
/* results  --------
emp_card_id  working_hours
1C00EDF12D    03:35:30
*/

i modified my second query slightly and it works fine too --

Code:

SELECT emp_card_id 
     , CONCAT(CAST(
       CASE WHEN SUM(UNIX_TIMESTAMP(out_time)
                    -UNIX_TIMESTAMP(in_time)) > 86400
            THEN FLOOR(
                 SUM(UNIX_TIMESTAMP(out_time)
                    -UNIX_TIMESTAMP(in_time)) / 86400 )
            ELSE 0 END AS CHAR)
	       , ' days' ) AS working_days
        , SEC_TO_TIME(MOD(
                 SUM(UNIX_TIMESTAMP(out_time)
                    -UNIX_TIMESTAMP(in_time)) , 86400 )
             )  AS working_hours
  FROM emp_attendance
GROUP
    BY emp_card_id;
/* results --------
emp_card_id  working_days  working_hours
1C00EDF12D   0 days        03:35:30
*/

**krishnaanu** · Nov 2, 2009 22:57

r937: thank you sir, i will tyr this one and definitely inform u

**krishnaanu** · Nov 3, 2009 01:33

r937: sir, i have executed the query. it is working fine when the in_time and out_time are from the same date.

but when in_time is '2009-11-03 12:00:12' ans out_time is '2009-11-04 12:00:24', the time difference is 24:00:12(twenty four hours,twelve seconds).

the query is subtracting only the time portion of timestamp. it is not considering the date part. the query is showing 00:00:12, instead if 24:00:12.

**r937** · Nov 3, 2009 06:14

Code:

/* add the 24-hour example */

INSERT INTO emp_attendance 
( trans_id,emp_card_id,in_time,out_time,status)
VALUES
 (22,'1937937937','2009-11-03 12:00:12','2009-11-04 12:00:24',0)
;

/* try the query again */

SELECT emp_card_id 
     , CONCAT(CAST(
       CASE WHEN SUM(UNIX_TIMESTAMP(out_time)
                    -UNIX_TIMESTAMP(in_time)) > 86400
            THEN FLOOR(
                 SUM(UNIX_TIMESTAMP(out_time)
                    -UNIX_TIMESTAMP(in_time)) / 86400 )
            ELSE 0 END AS CHAR)
         , ' days' ) AS working_days
        , SEC_TO_TIME(MOD(
                 SUM(UNIX_TIMESTAMP(out_time)
                    -UNIX_TIMESTAMP(in_time)) , 86400 )
             )  AS working_hours
  FROM emp_attendance
GROUP
    BY emp_card_id;

/* results --------
emp_card_id  working_days  working_hours
1937937937   1 days        00:00:12
1C00EDF12D   0 days        03:35:30

*/

now if you feel you need to convert 1 day + 00:00:12 into 24:00:12, then you will have to do this in your front end language

**krishnaanu** · Nov 4, 2009 01:28

r937: thank you very much sir for all your help. thank u thank u thank u veryyyy much.

User Tag List

Thread: How to calculate the total working hours of an employee from a set or records

Thread Tools

Display

How to calculate the total working hours of an employee from a set or records

Bookmarks

Bookmarks

Posting Permissions