Hi all, I need your help.
This is my table tusers on MySQL 5.5.1 database community version
mysql> SELECT * FROM `tusers`;
+------------+------------+----------+-----+
| tIDUSER | tDate | tHour | tID |
+------------+------------+----------+-----+
| Controneri | 2022-01-06 | 07:54:42 | 1 |
| Controneri | 2022-01-06 | 07:43:38 | 2 |
| Controneri | 2022-01-06 | 07:13:09 | 3 |
| Controneri | 2022-01-06 | 06:31:52 | 4 |
| Controneri | 2022-01-06 | 06:13:12 | 5 |
+------------+------------+----------+-----+
5 rows in set (0.13 sec)
I need select from the table tusers only these rows
+------------+------------+----------+-----+
| tIDUSER | tDate | tHour | tID |
+------------+------------+----------+-----+
| Controneri | 2022-01-06 | 07:43:38 | 2 |
| Controneri | 2022-01-06 | 06:13:12 | 5 |
+------------+------------+----------+-----+
Because the other rows are repeated for the same user Controneri within one hour compared to the previous row.
Each user access to the web page is stored on the table tusers for date and time. But I have to extract only the first access and exclude the repeated accesses in the time span of one hour. On this example the user Controneri on January 6 he was logged in 5 times. But the valid accesses are those at 06:13:12 and 07:43:38 , because after the access at 06:13:12 there were other accesses before 07:13:12 , i.e. before the end of the hour compared to the hours 06:13:12 ( 06:31:52 and 07:13:09 , rows 4 and 3).
I have tried without success.
My table structure and the Select query below on db-fiddle.com , which offers MySQL 5
Any suggestion?
-- ----------------------------
-- Table structure for tusers
-- ----------------------------
DROP TABLE IF EXISTS `tusers`;
CREATE TABLE `tusers` (
`tIDUSER` varchar(255) NULL DEFAULT NULL,
`tDate` date NULL DEFAULT NULL,
`tHour` time NULL DEFAULT NULL,
`tID` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`tID`) USING BTREE
) ENGINE = InnoDB;
-- ----------------------------
-- Records of tusers
-- ----------------------------
INSERT INTO `tusers` VALUES ('Controneri', '2022-01-06', '07:54:42', 1);
INSERT INTO `tusers` VALUES ('Controneri', '2022-01-06', '07:43:38', 2);
INSERT INTO `tusers` VALUES ('Controneri', '2022-01-06', '07:13:09', 3);
INSERT INTO `tusers` VALUES ('Controneri', '2022-01-06', '06:31:52', 4);
INSERT INTO `tusers` VALUES ('Controneri', '2022-01-06', '06:13:12', 5);
SELECT
a.tID,
a.tDate,
a.tHour,
a.tIDUSER,
TIMEDIFF( a.tHour, b.tHour ) AS tDif
FROM
`tusers` a
JOIN `tusers` b ON
a.tDate = b.tDate
AND a.tIDUSER = b.tIDUSER
AND a.tID > b.tID
WHERE
( TIMEDIFF( a.tHour, b.tHour ) BETWEEN '00:00:00' AND '01:00:00' )
ORDER BY
a.tIDUSER,
a.tDate,
a.tHour ASC;