CODEWITHSUNDEEP
mysql
Hmerman6006
Hmerman6006

Reputation: 1931

MySQL calculate time difference where column has specific value?

I have been thinking to redesign my employee log table, because I noticed it may not be as effective at calculating the times and saving storing space. The reason I am saying it is not effective with space is because for one employee to clock in, out, tea in, tea out, lunch in, lunch out, the app inserts a record for each clock with only the times and in_out status changing. The reason for effectivity is because you have to query six different records (if they exist) to get one days time. But I am struggling a bit to get the last bit right.
My table looks as follows:

logemployeetable    CREATE TABLE `logemployeetable` (
  `log_id` int(11) NOT NULL AUTO_INCREMENT,
  `emp_id` int(11) NOT NULL,
  `emp_times` datetime NOT NULL,
  `in_out` enum('IN','OUT','TIn','TOut','LIn','LOut') NOT NULL,
  `longitude` double DEFAULT NULL,
  `latitude` double DEFAULT NULL,
  `ent_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  PRIMARY KEY (`log_id`),
  KEY `index_emplogtb_emp_id` (`emp_id`),
  KEY `index_emplog_ent_id` (`ent_id`)
) ENGINE=InnoDB AUTO_INCREMENT=798 DEFAULT CHARSET=latin1

Test data set:

emp_id  in_out  emp_times   
1       IN      2019-11-19 05:12:01 
1       OUT     2019-11-19 16:38:04 
1       IN      2019-11-20 05:09:19 
1       OUT     2019-11-20 16:19:47

Currently my sum time code looks as follows (I am currently just trying to sum the in - out times):

SELECT  
    l.emp_id, 
    emp.emp_names, 
    emp.emp_number, 
    SUM(TIME_TO_SEC(MAX(l.emp_times))/3600 - TIME_TO_SEC(MIN(l.emp_times))/3600)
FROM 
    employeetable AS emp 
INNER JOIN 
    logemployeetable AS l ON 
        emp.emp_id = l.emp_id 
WHERE DATE(l.emp_dtetime) = '2019-11-20' AND l.in_out  = ‘OUT’ AND l.in_out = ‘IN’ AND  l.emp_id = 1
GROUP BY l.emp_id

This gives invalid group argument. So I tried this:

SELECT  
    l.emp_id, 
    emp.emp_names, 
    emp.emp_number,
    TIMESTAMPDIFF(minute, MIN(l.emp_times), MAX(l.emp_times)) AS clock_time
FROM 
    employeetable AS emp 
INNER JOIN 
    logemployeetable AS l ON 
        emp.emp_id = l.emp_id 
WHERE DATE(l.emp_times) BETWEEN '2019-11-19' AND '2019-11-20' AND l.in_out  = 'OUT' AND l.in_out = 'OUT' AND  l.emp_id = 1
GROUP BY l.emp_id

This does not calculate per day times but does actually calculate overtime equaling 1421 minutes which is 23.6 hours.
Can someone please point me in the right direction to calculate per day times while checking if an in or out time does not exist?

Upvotes: 0

Views: 63

Answers (1)

GMB
GMB

Reputation: 222672

I am currently just trying to sum the in - out times

Assuming that there is one IN record and out OUT record per day, one solution would be to aggregate twice. First by empoyeen and day, then by employee. The following query gives you the sum of the shift duration per employee, in seconds. If, for a given employee and day, there is one IN record but no OUT (or the other way around, the sum ignores it.

select
    emp_id,
    sum(shift_duration) total_shift_duration
from (
    select
        emp_id,
        date(emp_times) emp_day,
        unix_timestamp(min(case when in_out = 'OUT' then emp_times end))
            - unix_timestamp(max(case when in_out = 'IN' then emp_times end))
            shift_duration
    from logemployeetable
    group by emp_id, date(emp_times)
) t
group by emp_id

Upvotes: 1

Related Questions