Mysql – Calculate Total Working Minutes in MySql

datetimeMySQLreportingtimestamp

I have a design a table for attendance and here are the genral fileds

  1. AttendanceMachineLoginId
  2. EmpId
  3. AttendanceDateTime

Whenever user will come to office, he has to make his attendance.

The first attendance will consider login and the second will consider the logout. Each time a record will be added with the time. A user can make multiple entries (login logout in a single day. Like this

enter image description here

EmpId 81 has login and logout two times in same day.

Now, My aim is to generate per day employee report that how many minutes he has given to the company. I just came to know that TIMESTAMPDIFF() can provide the minutes but i am unable to understand that how can I apply it to my table. Additionally, I want to ask that, do the table Fields are right for the desired report or I need to change it?

One Another Strategy: I was also thinking that I should add minutes column in the table and whenever user logout I should calculate the minutes and add that minutes with logout entry.

Sample Data:

INSERT INTO `attendancemachinelogin` (`AttendanceMachineLoginId`, `EmpId`, `TimeTrackId`, `AttendanceDateTime`, `RecordAddDateTime`) VALUES
(0, 81, 315079, '2018-8-15 14:8:46', '2018-08-15 14:09:25'),
(0, 81, 315079, '2018-8-15 14:20:38', '2018-08-15 14:21:17'),
(0, 81, 315079, '2018-8-15 14:21:9', '2018-08-15 14:21:47'),
(0, 81, 315079, '2018-8-15 14:28:37', '2018-08-15 14:29:16'),
(0, 81, 315079, '2018-8-15 14:28:58', '2018-08-15 14:29:36'),
(0, 81, 315079, '2018-8-15 14:36:42', '2018-08-15 14:37:21'),
(0, 81, 315079, '2018-8-15 15:36:34', '2018-08-15 15:37:13'),
(0, 81, 315079, '2018-8-15 15:52:39', '2018-08-15 15:53:17'),
(0, 81, 315079, '2018-8-15 16:5:38', '2018-08-15 16:06:17'),
(0, 81, 315079, '2018-8-15 16:6:50', '2018-08-15 16:07:29'),
(0, 81, 315079, '2018-8-15 16:8:49', '2018-08-15 16:09:29'),
(0, 81, 315079, '2018-8-15 16:18:28', '2018-08-15 16:19:08'),
(0, 81, 315079, '2018-8-15 16:20:49', '2018-08-15 16:21:28'),
(0, 81, 315079, '2018-8-15 16:23:18', '2018-08-15 16:23:58'),
(0, 81, 315079, '2018-8-15 16:24:3', '2018-08-15 16:24:42'),
(0, 81, 315079, '2018-8-15 16:24:47', '2018-08-15 16:25:26'),
(0, 81, 315079, '2018-8-15 16:24:58', '2018-08-15 16:25:37'),
(0, 81, 315079, '2018-8-15 16:25:54', '2018-08-15 16:26:33'),
(0, 81, 315079, '2018-8-15 16:56:47', '2018-08-15 16:57:27'),
(0, 101, 417092, '2018-8-15 17:37:53', '2018-08-15 17:38:32'),
(0, 101, 417092, '2018-8-15 18:4:34', '2018-08-15 18:05:14'),
(0, 101, 417092, '2018-8-15 18:7:43', '2018-08-15 18:08:22'),
(0, 81, 315079, '2018-8-15 18:13:15', '2018-08-15 18:13:54'),
(0, 81, 315079, '2018-8-17 10:50:16', '2018-08-17 10:50:54'),
(0, 101, 417092, '2018-8-17 10:51:54', '2018-08-17 10:52:31'),
(0, 4, 413034, '2018-8-17 11:45:16', '2018-08-17 11:45:54'),
(0, 91, 916086, '2018-8-17 11:59:34', '2018-08-17 12:00:12'),
(0, 81, 315079, '2018-8-17 12:0:19', '2018-08-17 12:00:56'),
(0, 81, 315079, '2018-8-17 15:7:41', '2018-08-17 15:08:17'),
(0, 101, 417092, '2018-8-17 15:9:54', '2018-08-17 15:10:32'),
(0, 101, 417092, '2018-8-17 15:10:9', '2018-08-17 15:10:45'),
(0, 101, 417092, '2018-8-17 15:10:23', '2018-08-17 15:10:59'),
(0, 101, 417092, '2018-8-17 15:10:25', '2018-08-17 15:11:02'),
(0, 101, 417092, '2018-8-17 15:11:6', '2018-08-17 15:11:43'),
(0, 101, 417092, '2018-8-17 15:11:15', '2018-08-17 15:11:52'),
(0, 101, 417092, '2018-8-17 15:11:17', '2018-08-17 15:11:54'),
(0, 81, 315079, '2018-8-17 15:11:32', '2018-08-17 15:12:09'),
(0, 81, 315079, '2018-8-17 15:12:32', '2018-08-17 15:13:09'),
(0, 81, 315079, '2018-8-17 15:35:33', '2018-08-17 15:36:10'),
(0, 81, 315079, '2018-8-17 15:41:58', '2018-08-17 15:42:34'),
(0, 81, 315079, '2018-8-17 15:42:17', '2018-08-17 15:42:54'),
(0, 81, 315079, '2018-8-17 16:8:25', '2018-08-17 16:09:01'),
(0, 81, 315079, '2018-8-17 16:8:32', '2018-08-17 16:09:08'),
(0, 101, 417092, '2018-8-17 16:8:53', '2018-08-17 16:09:30'),
(0, 101, 417092, '2018-8-17 16:9:20', '2018-08-17 16:09:57'),
(0, 4, 413034, '2018-8-17 16:10:16', '2018-08-17 16:10:53'),
(0, 36, 413037, '2018-8-17 16:10:46', '2018-08-17 16:11:23'),
(0, 81, 315079, '2018-8-17 16:22:21', '2018-08-17 16:22:58'),
(0, 101, 417092, '2018-8-17 16:22:45', '2018-08-17 16:23:21'),
(0, 4, 413034, '2018-8-17 16:23:12', '2018-08-17 16:23:49'),
(0, 81, 315079, '2018-8-17 16:23:35', '2018-08-17 16:24:12'),
(0, 81, 315079, '2018-8-17 16:44:4', '2018-08-17 16:44:42'),
(0, 101, 417092, '2018-8-17 16:44:22', '2018-08-17 16:44:58'),
(0, 81, 315079, '2018-8-17 17:6:51', '2018-08-17 17:07:28'),
(0, 101, 417092, '2018-8-17 17:7:8', '2018-08-17 17:07:45'),
(0, 4, 413034, '2018-8-17 17:7:52', '2018-08-17 17:08:28'),
(0, 81, 315079, '2018-8-17 17:9:25', '2018-08-17 17:10:02'),
(0, 101, 417092, '2018-8-17 17:9:46', '2018-08-17 17:10:22'),
(0, 4, 413034, '2018-8-17 17:10:6', '2018-08-17 17:10:42'),
(0, 81, 315079, '2018-8-17 17:10:24', '2018-08-17 17:11:01'),
(0, 81, 315079, '2018-8-17 17:10:39', '2018-08-17 17:11:15'),
(0, 101, 417092, '2018-8-17 17:10:47', '2018-08-17 17:11:24'),
(0, 101, 417092, '2018-8-17 17:10:58', '2018-08-17 17:11:35'),
(0, 81, 315079, '2018-8-17 17:11:10', '2018-08-17 17:11:46'),
(0, 101, 417092, '2018-8-17 17:11:31', '2018-08-17 17:12:09'),
(0, 4, 413034, '2018-8-17 17:40:40', '2018-08-17 17:41:18'),
(0, 101, 417092, '2018-8-17 17:41:23', '2018-08-17 17:41:59'),
(0, 36, 413037, '2018-8-17 17:41:37', '2018-08-17 17:42:14'),
(0, 81, 315079, '2018-8-17 17:42:9', '2018-08-17 17:42:45'),
(0, 3, 213020, '2018-8-17 17:47:34', '2018-08-17 17:48:11'),
(0, 81, 315079, '2018-8-17 17:48:16', '2018-08-17 17:48:52'),
(0, 4, 413034, '2018-8-17 17:48:59', '2018-08-17 17:49:36'),
(0, 4, 413034, '2018-8-17 17:49:59', '2018-08-17 17:50:36'),
(0, 36, 413037, '2018-8-17 17:52:36', '2018-08-17 17:53:13'),
(0, 101, 417092, '2018-8-17 17:52:53', '2018-08-17 17:53:29'),
(0, 6, 213016, '2018-8-17 17:53:30', '2018-08-17 17:54:06'),
(0, 81, 315079, '2018-8-17 17:53:44', '2018-08-17 17:54:20'),
(0, 4, 413034, '2018-8-17 17:54:27', '2018-08-17 17:55:03'),
(0, 3, 213020, '2018-8-17 17:54:49', '2018-08-17 17:55:27'),
(0, 4, 413034, '2018-8-17 17:55:23', '2018-08-17 17:56:00'),
(0, 36, 413037, '2018-8-17 17:58:33', '2018-08-17 17:59:10'),
(0, 101, 417092, '2018-8-17 17:58:47', '2018-08-17 17:59:24'),
(0, 102, 517094, '2018-8-17 17:59:4', '2018-08-17 17:59:40'),
(0, 81, 315079, '2018-8-17 17:59:33', '2018-08-17 18:00:09'),
(0, 4, 413034, '2018-8-17 18:0:16', '2018-08-17 18:00:52'),
(0, 3, 213020, '2018-8-17 18:0:40', '2018-08-17 18:01:17'),
(0, 6, 213016, '2018-8-17 18:1:30', '2018-08-17 18:02:06'),
(0, 36, 413037, '2018-8-17 18:26:24', '2018-08-17 18:27:01'),
(0, 101, 417092, '2018-8-17 18:26:38', '2018-08-17 18:27:14'),
(0, 6, 213016, '2018-8-17 18:27:9', '2018-08-17 18:27:45'),
(0, 81, 315079, '2018-8-17 18:27:24', '2018-08-17 18:28:00'),
(0, 102, 517094, '2018-8-17 18:27:38', '2018-08-17 18:28:14'),
(0, 4, 413034, '2018-8-17 18:28:13', '2018-08-17 18:28:49'),
(0, 81, 315079, '2018-8-17 19:36:49', '2018-08-17 19:37:26'),
(0, 101, 417092, '2018-8-17 19:37:17', '2018-08-17 19:37:54'),
(0, 102, 517094, '2018-8-17 19:37:30', '2018-08-17 19:38:07'),
(0, 36, 413037, '2018-8-17 19:38:13', '2018-08-17 19:38:50'),
(0, 4, 413034, '2018-8-17 19:38:54', '2018-08-17 19:39:32'),
(0, 3, 213020, '2018-8-17 19:39:58', '2018-08-17 19:40:35'),
(0, 101, 417092, '2018-8-18 10:21:26', '2018-08-18 10:22:03'),
(0, 81, 315079, '2018-8-18 10:30:23', '2018-08-18 10:31:09'),
(0, 4, 413034, '2018-8-18 10:31:46', '2018-08-18 10:32:27'),
(0, 102, 517094, '2018-8-18 10:32:15', '2018-08-18 10:32:53'),
(0, 6, 213016, '2018-8-18 10:32:44', '2018-08-18 10:33:22'),
(0, 3, 213020, '2018-8-18 10:33:23', '2018-08-18 10:34:03'),
(0, 81, 315079, '2018-8-18 10:42:49', '2018-08-18 10:43:27'),
(0, 101, 417092, '2018-8-18 10:43:25', '2018-08-18 10:44:03'),
(0, 81, 315079, '2018-8-18 10:48:51', '2018-08-18 10:49:30'),
(0, 102, 517094, '2018-8-18 10:49:9', '2018-08-18 10:49:49'),
(0, 81, 315079, '2018-8-18 10:56:46', '2018-08-18 10:57:25'),
(0, 1, 1211003, '2018-8-18 10:57:0', '2018-08-18 10:57:38'),
(0, 4, 413034, '2018-8-18 10:57:51', '2018-08-18 10:58:38'),
(0, 3, 213020, '2018-8-18 10:58:43', '2018-08-18 10:59:26');

Best Answer

Well, I've made an assumption that first event each day for each employee is the login.

For mysql 8.x having CTEs the query can be the next:

WITH cteB (EID, mins) AS 
(
  WITH cteA (EID, TS, cnt) AS 
  ( 
    SELECT w.EmpId
         , w.AttendanceDateTime
         , IF( @prevEID != @prevID := w.EmpID, @cnt := 1, @cnt := @cnt+1 ) AS cnt
      FROM table AS w
     ORDER BY w.EmpId ASC
            , w.AttendanceDateTime ASC
  )
  SELECT z.EID
       , TIMESTAMPDIFF(MINUTE, z.TS, q.TS) AS mins
    FROM cteA AS z
    JOIN cteA AS q  ON q.EID = z.EID
                   AND z.cnt = q.cnt+1
                   AND z.cnt % 2 = 1
)
SELECT x.EID AS EmpID
     , SUM(x.mins) AS Eminutes
  FROM cteB AS x
 GROUP BY x.EID
 ORDER BY x.EID ASC 
;

The same result can be achieved in the earlier versions of mysql with subqueries.

Related Question