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

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

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.

2. 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```

3. r937: thank you very much sir

4. 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.

5. 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```

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

7. Originally Posted by krishnaanu
sure

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

8. 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.

9. 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
*/```

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

11. 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.

12. 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

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•