CODEWITHSUNDEEP
mysqlsql
Muki
Muki

Reputation: 61

How to check if datetime is x seconds different for the same id

I got the following table: https://i.gyazo.com/0ff46d51d29aed52d92434df9e91e178.png

I got the following query which doesn't work:

select * from viewed u1 inner join viewed u2 on (u2.user_id = u1.user_id) where TIMESTAMPDIFF(SECOND, u1.date_reg, u2.date_reg) < 5;

I am trying to find out which user got entries where the date_reg time is less than x seconds. If we look at the image, both of the users should be selected since they got rows where the date_reg differs 0-1 seconds.

The problem is that the query doesn't work as intended since it selects almost all rows in the table. Does someone have any idea how I find out which user has x seconds differencte between their entires in the table?

Upvotes: 0

Views: 60

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270431

You need another condition so timestampdiff() is always positive. I would suggest:

select *
from viewed u1 inner join
     viewed u2
     on u2.user_id = u1.user_id
where u1.date_reg < u2.date_reg and
      TIMESTAMPDIFF(SECOND, u1.date_reg, u2.date_reg) < 5;

Upvotes: 2

Related Questions