Mysql result set for last 2 hours

user_3733831 0
Viewed 45

Look at this query:

SELECT attempt_time, u.username
  FROM login_attempts l 
  JOIN user u ON u.user_id = l.user_id AND  u.username = 'jhone' 

+---------------------+---------------+
| attempt_time        | username      |
+---------------------+---------------+
| 2021-07-27 16:06:48 | jhone         |
| 2021-07-27 16:34:33 | jhone         |
| 2021-07-27 16:34:46 | jhone         |
| 2021-07-27 16:39:53 | jhone         |
| 2021-07-27 18:33:30 | jhone         |
| 2021-07-27 18:34:13 | jhone         |
+---------------------+---------------+

Just I need to get attempt_time for last 2 hours:

This is How I tried, But it always giving empty set as result.

SELECT attempt_time, u.username
  FROM login_attempts l 
    JOIN user u ON u.user_id = l.user_id AND  u.username = 'jhone' 
WHERE NOW() BETWEEN l.attempt_time AND NOW() - INTERVAL 2 HOUR;

Can anyone tell why my query doesn't work?

between select date mysql
2 Answers

For the last 2 hours means:

WHERE l.attempt_time BETWEEN NOW() - INTERVAL 2 HOUR AND NOW();

Or maybe:

WHERE l.attempt_time >= NOW() - INTERVAL 2 HOUR;

if there are no rows after NOW().

user_104988280

I think the logic in the WHERE clause is off. Try this version:

SELECT attempt_time, u.username
FROM login_attempts l 
INNER JOIN user u ON u.user_id = l.user_id
WHERE u.username = 'jhone'  AND
      l.attempt_time BETWEEN NOW() AND NOW() - INTERVAL 2 HOUR;

Note that if there is no data in the futute, the WHERE clause can be simplified to:

WHERE u.username = 'jhone'  AND l.attempt_time > NOW() - INTERVAL 2 HOUR
user_18632290
Related