Muki Muki - 11 months ago 39
MySQL Question

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

I got the following table:

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?

Answer Source

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;