Compare time between consequent rows using MySQL 5.5

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;

I don’t understand the sense of your request.

What if the first logon is at 23:40 and the next on 0:20 the next day?

For what do you need this data?

Can you give us a little more detailed information?

The query you want to do would be very complicated and I am not sure if it is not better to change the process.

At the end whatever you need, it would be much easier if you only use one datetime column instead of two columns with date and time.

BTW

Do you post this question to all platforms you could find?

1 Like

the replies to that stackoverflow question are very interesting

but the main thing to derive from them is the rather strong suggestion to use a single datetime column, not separate date and time columns

please comment, @comunidadmexicanarom, on whether you can do this

1 Like

Hi r937 thanks for reply.

I have already edited the table to use a single datetime column.

What is your suggestion?

On the tangent of the database structure, I also suspect that the tIDUSER column should really be an integer, foreign key to the ID in a user table, as opposed to a name string.

1 Like

As you don’t know the type of unique identifier for users in his company this is a bad device.
Most big companies are working with single sign on, where the sso must not be an integer.

1 Like

can i make another suggesstion? upgrade your server so that you can use window functions

1 Like

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.