0

I have a column name called last_activity that is of type DATETIME. (so. 2021-01-03 00:00:00). I want to find all rows which are within 5 minutes of now. I attempted the following:

SELECT * 
FROM users 
WHERE last_activity >  DATE_SUB(NOW(),INTERVAL 5 MINUTE)

I get 0 rows, even though the activity is within 5 minutes (I know because the database says so). How can I fix this?

Dharman
  • 30,962
  • 25
  • 85
  • 135
newbie12345
  • 29
  • 4
  • @Dharman I asked it with php tag because the query is done through php and thought it would maybe cause different results. Also i did search and all the options did not work so i thought id ask the question with my problem. – newbie12345 Jan 03 '21 at 12:42
  • Are you ABSOLUTELY SURE that `last_activity` column is defined as DATETIME. Please show us the schema. Do a `SHOW CREATE TABLE users;` and copy/paste the output to your question – RiggsFolly Jan 03 '21 at 12:55
  • 1
    RE: Your AM and PM comment, DATETIME columns are 24Hr not 12Hr based always. Hence my question about data type? Also on a test datebase I run your code and get the right result, So whats really going on here is wierd to say the least – RiggsFolly Jan 03 '21 at 13:03

1 Answers1

0

Use the function TIMESTAMPDIFF() to get the difference in minutes:

SELECT * 
FROM users 
WHERE ABS(TIMESTAMPDIFF(MINUTE, last_activity, NOW())) <= 5
forpas
  • 160,666
  • 10
  • 38
  • 76
  • it still returns 0. It says in my databse in mysql 08:35 which is correct, but it wont work. even if teh time is 08:35 – newbie12345 Jan 03 '21 at 12:36
  • @newbie12345 My query returns the rows that have a max difference of 5 mins from NOW(). The function NOW() returns *the current date and time as a value in 'YYYY-MM-DD hh:mm:ss' or YYYYMMDDhhmmss format, depending on whether the function is used in string or numeric context. The value is expressed in the session time zone.* Did you check what NOW() returns in your MySql server? Execute `SELECT NOW();` – forpas Jan 03 '21 at 12:40
  • i did this ``$n= mysqli_query($conn, "SELECT NOW()");`` and it returned an error. – newbie12345 Jan 03 '21 at 12:46
  • Wouldn't this be bad performance? Does it use INDEX? – Dharman Jan 03 '21 at 12:46
  • Do you have access to phpMyAdmin or Workbench. Its easier to do it there – RiggsFolly Jan 03 '21 at 12:47
  • @RiggsFolly I do will try it there – newbie12345 Jan 03 '21 at 12:48
  • @Dharman that the query returned false – newbie12345 Jan 03 '21 at 12:48
  • @newbie12345 That is not an error. That means you didn't enable error reporting. [How to get the error message in MySQLi?](https://stackoverflow.com/a/22662582/1839439) – Dharman Jan 03 '21 at 12:49
  • @RiggsFolly thanks man, I know the issue i think. Now is returning 22:50 which is correct as its pm here, where the datetime i am inserting is 08:30. I assume to get it to 24 hour i need to do ``date('Y-m-d H:i:s');``? – newbie12345 Jan 03 '21 at 12:51
  • @newbie12345 None of this makes sense. 24h/12h is only important when you display the date not when you compare. – Dharman Jan 03 '21 at 12:52
  • @newbie12345 what is the data type of `last_activity`? – forpas Jan 03 '21 at 12:53
  • @RiggsFolly Thanks for being patient i really appreciate it. approved ticked :) – newbie12345 Jan 03 '21 at 12:54
  • @forpas I don't want to sound rude but its clearly stated in the question that its ``DATETIME`` – newbie12345 Jan 03 '21 at 12:55
  • 1
    @newbie12345 this is what I read in your question. But you mentioned something about pm or am which is not relevant when the data type is DATETIME and you compare it with NOW(). – forpas Jan 03 '21 at 12:56
  • I don't get it. What was the problem? Was it NOW() +5 minutes like the answer suggests to solve or was it something unrelated? What did RiggsFolly say that helped to fix it? – Dharman Jan 03 '21 at 13:03
  • 1
    @Dharman I get the feeling that the OP was assuming the 8:30 and 22:30 were the same thing, so this is really just a bit of a non-issue brought about by lack of .... sleep maybe :) – RiggsFolly Jan 03 '21 at 13:07