title: Recorded for each user the datetime of access and the datetime of exit in MySQL
body: <p>On this table of a mysql database are recorded for each user the date and time of access and the date and time of exit :</p>
  
  <pre><code>+-----------+---------------------+-------+---------------------+------+
  | tID       | tDate               | tUser | tDescription        | tSet |
  +-----------+---------------------+-------+---------------------+------+
  | 707951537 | 2020-02-11 11:17:13 | 13117 | STARTED ACCESS USER | NULL |
  | 707757026 | 2020-02-11 11:16:05 | 43409 | STARTED ACCESS USER | NULL |
  | 707765459 | 2020-02-11 09:28:42 | 16920 | ENDING  ACCESS USER | NULL |
  | 707765775 | 2020-02-11 09:27:13 | 16920 | STARTED ACCESS USER | NULL |
  | 707767122 | 2020-02-11 09:15:46 | 30303 | STARTED ACCESS USER | NULL |
  | 707789986 | 2020-02-11 09:13:18 | 49798 | STARTED ACCESS USER | NULL |
  | 707902406 | 2020-02-11 07:15:38 | 44333 | STARTED ACCESS USER | NULL |
  | 707917986 | 2020-02-11 07:05:57 | 43654 | ENDING  ACCESS USER | NULL |
  | 707918384 | 2020-02-11 07:03:30 | 43654 | STARTED ACCESS USER | NULL |
  | 707951609 | 2020-02-11 06:56:41 | 22429 | STARTED ACCESS USER | NULL |
  | 707965127 | 2020-02-11 06:08:16 | 41190 | STARTED ACCESS USER | NULL |
  +-----------+---------------------+-------+---------------------+------+
  11 rows in set
  </code></pre>
  
  <p>I need to update the <code>tSet</code> column with the value 1 when the difference between date time entry and date time exit for each user is less than three minutes.</p>
  
  <p>In this case I need this output, because for users <code>16920</code> and <code>43654</code> the difference between date time entry and date time exit is less than three minutes :</p>
  
  <pre><code>+-----------+---------------------+-------+---------------------+------+
  | tID       | tDate               | tUser | tDescription        | tSet |
  +-----------+---------------------+-------+---------------------+------+
  | 707951537 | 2020-02-11 11:17:13 | 13117 | STARTED ACCESS USER | NULL |
  | 707757026 | 2020-02-11 11:16:05 | 43409 | STARTED ACCESS USER | NULL |
  | 707765459 | 2020-02-11 09:28:42 | 16920 | ENDING  ACCESS USER |    1 |
  | 707765775 | 2020-02-11 09:27:13 | 16920 | STARTED ACCESS USER |    1 |
  | 707767122 | 2020-02-11 09:15:46 | 30303 | STARTED ACCESS USER | NULL |
  | 707789986 | 2020-02-11 09:13:18 | 49798 | STARTED ACCESS USER | NULL |
  | 707902406 | 2020-02-11 07:15:38 | 44333 | STARTED ACCESS USER | NULL |
  | 707917986 | 2020-02-11 07:05:57 | 43654 | ENDING  ACCESS USER |    1 |
  | 707918384 | 2020-02-11 07:03:30 | 43654 | STARTED ACCESS USER |    1 |
  | 707951609 | 2020-02-11 06:56:41 | 22429 | STARTED ACCESS USER | NULL |
  | 707965127 | 2020-02-11 06:08:16 | 41190 | STARTED ACCESS USER | NULL |
  +-----------+---------------------+-------+---------------------+------+
  11 rows in set
  </code></pre>
  
  <p>I have tried this sql query :</p>
  
  <pre><code>UPDATE `tTableUser` jjj
  JOIN (
      SELECT
          a.tID,
          a.tDate,
          a.tUser,
          a.tDescription,
          TIMEDIFF(
              SUBSTRING_INDEX(b.tDate, ' ' ,- 1),
              SUBSTRING_INDEX(a.tDate, ' ' ,- 1)
          ) AS strDiff
      FROM
          `tTableUser` a
      JOIN `tTableUser` b ON SUBSTRING_INDEX(a.tDate, ' ', 1) = SUBSTRING_INDEX(b.tDate, ' ', 1)
      AND TRIM(a.tUser) = TRIM(b.tUser)
      AND SUBSTRING_INDEX(a.tDate, ' ', 1) IN (
          DATE_SUB(CURDATE(), INTERVAL 1 DAY)
      )
      AND SUBSTRING_INDEX(a.tDate, ' ' ,- 1) BETWEEN '06:00:00'
      AND '12:00:00'
      AND a.tDate &gt; b.tDate
      AND (
          TIMEDIFF(
              SUBSTRING_INDEX(a.tDate, ' ' ,- 1),
              SUBSTRING_INDEX(b.tDate, ' ' ,- 1)
          ) BETWEEN '00:00:00'
          AND '00:03:00'
      )
      ORDER BY
          a.tDate DESC
  ) AS xsql
  SET jjj.tSet = 1
  WHERE
      jjj.tID = xsql.tID;
  
  SELECT * FROM `tTableUser`;
  
  +-----------+---------------------+-------+---------------------+------+
  | tID       | tDate               | tUser | tDescription        | tSet |
  +-----------+---------------------+-------+---------------------+------+
  | 707951537 | 2020-02-11 11:17:13 | 13117 | STARTED ACCESS USER | NULL |
  | 707757026 | 2020-02-11 11:16:05 | 43409 | STARTED ACCESS USER | NULL |
  | 707765459 | 2020-02-11 09:28:42 | 16920 | ENDING ACCESS USER  |    1 |
  | 707765775 | 2020-02-11 09:27:13 | 16920 | STARTED ACCESS USER | NULL |
  | 707767122 | 2020-02-11 09:15:46 | 30303 | STARTED ACCESS USER | NULL |
  | 707789986 | 2020-02-11 09:13:18 | 49798 | STARTED ACCESS USER | NULL |
  | 707902406 | 2020-02-11 07:15:38 | 44333 | STARTED ACCESS USER | NULL |
  | 707917986 | 2020-02-11 07:05:57 | 43654 | ENDING ACCESS USER  |    1 |
  | 707918384 | 2020-02-11 07:03:30 | 43654 | STARTED ACCESS USER | NULL |
  | 707951609 | 2020-02-11 06:56:41 | 22429 | STARTED ACCESS USER | NULL |
  | 707965127 | 2020-02-11 06:08:16 | 41190 | STARTED ACCESS USER | NULL |
  +-----------+---------------------+-------+---------------------+------+
  11 rows in set
  </code></pre>
  
  <p>Without success because the output is :</p>
  
  <pre><code>+-----------+---------------------+-------+---------------------+------+
  | tID       | tDate               | tUser | tDescription        | tSet |
  +-----------+---------------------+-------+---------------------+------+
  | 707951537 | 2020-02-11 11:17:13 | 13117 | STARTED ACCESS USER | NULL |
  | 707757026 | 2020-02-11 11:16:05 | 43409 | STARTED ACCESS USER | NULL |
  | 707765459 | 2020-02-11 09:28:42 | 16920 | ENDING ACCESS USER  |    1 |
  | 707765775 | 2020-02-11 09:27:13 | 16920 | STARTED ACCESS USER | NULL |
  | 707767122 | 2020-02-11 09:15:46 | 30303 | STARTED ACCESS USER | NULL |
  | 707789986 | 2020-02-11 09:13:18 | 49798 | STARTED ACCESS USER | NULL |
  | 707902406 | 2020-02-11 07:15:38 | 44333 | STARTED ACCESS USER | NULL |
  | 707917986 | 2020-02-11 07:05:57 | 43654 | ENDING ACCESS USER  |    1 |
  | 707918384 | 2020-02-11 07:03:30 | 43654 | STARTED ACCESS USER | NULL |
  | 707951609 | 2020-02-11 06:56:41 | 22429 | STARTED ACCESS USER | NULL |
  | 707965127 | 2020-02-11 06:08:16 | 41190 | STARTED ACCESS USER | NULL |
  +-----------+---------------------+-------+---------------------+------+
  11 rows in set
  </code></pre>
  
  <p>How to do resolve this ?
  Please, can you help me ?</p>
  
  <p>My create table code below:</p>
  
  <pre><code>DROP TABLE IF EXISTS `ttableuser`;
  CREATE TABLE `ttableuser` (
    `tID` int(11) NOT NULL,
    `tDate` datetime DEFAULT NULL,
    `tUser` int(11) DEFAULT NULL,
    `tDescription` varchar(255) DEFAULT NULL,
    `tSet` int(1) DEFAULT NULL,
    PRIMARY KEY (`tID`)
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  
  -- ----------------------------
  -- Records of ttableuser
  -- ----------------------------
  INSERT INTO `ttableuser` VALUES ('707951537', '2020-02-11 11:17:13', '13117', 'STARTED ACCESS USER', null);
  INSERT INTO `ttableuser` VALUES ('707757026', '2020-02-11 11:16:05', '43409', 'STARTED ACCESS USER', null);
  INSERT INTO `ttableuser` VALUES ('707765459', '2020-02-11 09:28:42', '16920', 'ENDING  ACCESS USER', null);
  INSERT INTO `ttableuser` VALUES ('707765775', '2020-02-11 09:27:13', '16920', 'STARTED ACCESS USER', null);
  INSERT INTO `ttableuser` VALUES ('707767122', '2020-02-11 09:15:46', '30303', 'STARTED ACCESS USER', null);
  INSERT INTO `ttableuser` VALUES ('707789986', '2020-02-11 09:13:18', '49798', 'STARTED ACCESS USER', null);
  INSERT INTO `ttableuser` VALUES ('707902406', '2020-02-11 07:15:38', '44333', 'STARTED ACCESS USER', null);
  INSERT INTO `ttableuser` VALUES ('707917986', '2020-02-11 07:05:57', '43654', 'ENDING  ACCESS USER', null);
  INSERT INTO `ttableuser` VALUES ('707918384', '2020-02-11 07:03:30', '43654', 'STARTED ACCESS USER', null);
  INSERT INTO `ttableuser` VALUES ('707951609', '2020-02-11 06:56:41', '22429', 'STARTED ACCESS USER', null);
  INSERT INTO `ttableuser` VALUES ('707965127', '2020-02-11 06:08:16', '41190', 'STARTED ACCESS USER', null);
  </code></pre>