MySQL: Fetching rows added last hour

Question

Hi, I keep a record of logins in a table. I have columns for id, ip, date and time. From that record of logins I wanna fetch logins made only in the last hour.

I'm sweaping through the MySQL docs on time and date functions, but I just can't seem to combine them correctly.

Can somebody help me?

Answer 1

Make use of the DATE_SUB() and Now() functions:

select count(*) as cnt
from  log
where date >= DATE_SUB(NOW(),INTERVAL 1 HOUR); 

Hope it helps you : )

Answer 2

without the specifics, I think Date_Add() would work.. by adding to your where clause an add of NOW negative hours

(or Date_Sub() )

Answer 3

I recommend have one datetime column instead of date and time columns.

Suppose you have a datetime column called last_login:

SELECT id, ip_address, last_login
FROM mytable
WHERE last_login >= DATE_SUB(NOW(), interval 1 hour);