Recorded for each user the datetime of access and the datetime of exit in MySQL

Hi all, I need your appreciated help.

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.

Each user it is recorded every time it enters and exit for current day.
Does a day begin after midnight, and end before midnight of the same day.

+-----------+---------------------+-------+---------------------+------+
| 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

I need to update the tSet column with the value 1 when the difference between date time entry and date time exit for each user is less than three minutes.

In this case I need this output, because for users 16920 and 43654 the difference between date time entry and date time exit is less than three minutes :

+-----------+---------------------+-------+---------------------+------+
| 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

I have tried this sql query :

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

Without success because the output is :

+-----------+---------------------+-------+---------------------+------+
| 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

How to do resolve this ?
Please, can you help me ?

My create table code below:

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);

this’ll be done better by an SQL guru… but roughly…

(basically pseudocode. Untested, probably broken, but it’s the theory.)

UPDATE `ttableuser` 
SET tSet = 1 
WHERE tID IN (
  SELECT a.tID,c.tID 
  FROM `ttableuser` a 
  LEFT JOIN (
      SELECT b.`tUser`,b.`tID` 
      FROM `ttableuser` b 
      WHERE b.`tUser` = a.`tUser` AND b.`tID` > a.`tID` 
      ORDER BY tID 
      LIMIT 1
  ) c 
  WHERE DATE(a.`tID`) = DATE(c.`tID`) AND TIMEDIFF(TIME(a.`tID`),TIME(c.`tID`)) < "00:03:00"
)

(and yes i know that where clause is horribly unsargable, hence it’ll be done better by someone :P)

Many thanks for help, but I have error.

mysql> UPDATE `ttableuser`
SET tSet = 1
WHERE
	tID IN (
		SELECT
			a.tID,
			c.tID
		FROM
			`ttableuser` a
		LEFT JOIN (
			SELECT
				b.`tUser`,
				b.`tID`
			FROM
				`ttableuser` b
			WHERE
				b.`tUser` = a.`tUser`
			AND b.`tID` > a.`tID`
			ORDER BY
				tID
			LIMIT 1
		) c
		WHERE
			DATE(a.`tID`) = DATE(c.`tID`)
		AND TIMEDIFF(
			TIME(a.`tID`),
			TIME(c.`tID`)
		) < '00:03:00'
	);
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE
			DATE(a.`tID`) = DATE(c.`tID`)
		AND TIMEDIFF(
			TIME(a.`tID`),
			' at line 23
mysql>