Know time difference in mysql query

Navigation
0

I have a table in mysql to see the wrong accesses of a login. If there are 3 records in less than 60 minutes I want to be able to block access to the ip address that you are trying to enter. In the database I keep 2 fields: ip: varchar format hour: timestamp format

The idea is to make a query to mysql that returns all the records where the same ip has a time difference with respect to the current time that is 60 minutes.

I have no idea where to start. Looking at the mysql reference manual I see this function 

mysql> SELECT TIMESTAMPDIFF(MINUTE,'2003-02-01','2003-05-01 12:05:55');
        -> 128885

But I do not really know what data to put on it or if that's what I need.

How could I do it?

    
asked by Killpe 08.04.2018 в 21:13
source

1 answer

1

If you want to calculate the time difference in minutes using the TIMESTAMPDIFF the syntax it is adopting is correct. The parameters you accept is

  • Unit of Time to calculate the difference as MONTH , YEAR , MINUTE
  • The date of your table in your database that you want to compare
  • The Actual Date that can be obtained with now() (take into account the time zone of the bd)

    • Beware that if you invert parameters 2 and 3 you will possibly get negative values.

    Then your query may look like this. 

    SELECT COUNT(*) from mitabla where TIMESTAMPDIFF(MINUTE, nombrecolumnafecha ,now()) < 60

    Demo

        
    answered by 08.04.2018 / 21:47
    source
    Warning: mysqli_query () expects at least 2 parameters, 1 given in php on line 160 Join SQL columns data