'Fetching rows added last hour

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 sweeping through the MySQL docs on time and date functions, but I just can't seem to combine them correctly.

Can somebody help me?

mysqldatetimedate-range


Solution 1:[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 : )

Solution 2:[2]

If you want to implement this into a cronjob, you need to specify the start and end.

For example, at 2pm, if you want to get the data for the past hour from 13:00:00 until 13:59:59, here's how to do it:

dateField BETWEEN 
DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 HOUR), '%Y-%m-%d %H:00:00') 
AND 
DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 HOUR), '%Y-%m-%d %H:59:59')

Solution 3:[3]

it can be done easily using 

select count(*) from logins where datetime>= NOW()- INTERVAL 1 HOUR

Solution 4:[4]

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);

Solution 5:[5]

You can also use CURDATE()function

SELECT
    count(*) AS TotalCount
FROM
    tblName
WHERE
    datetime >= DATE_SUB(CURDATE(), INTERVAL 1 HOUR)

Solution 6:[6]

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

(or Date_Sub() )

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 pevik
Solution 2 phoenix
Solution 3 Varun Nath
Solution 4 Paul Schreiber
Solution 5 Faisal
Solution 6 DRapp

Related Questions