65

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?

codeforester
  • 39,467
  • 16
  • 112
  • 140
Michael Grons
  • 685
  • 1
  • 5
  • 4

6 Answers6

177

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

pevik
  • 4,523
  • 3
  • 33
  • 44
SDReyes
  • 9,798
  • 16
  • 53
  • 92
  • Yes, I did something like that, only with TIMESUB (or SUBTIME?). But does this require that the date-field is a datetime-type? – Michael Grons Sep 09 '10 at 23:48
  • Yes using TIMESUB is more appropiate. yes, the first expression has to be a datetime expression. the second one should be a time expression. check this out http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_subtime – SDReyes Sep 10 '10 at 00:14
  • 4
    Saving data in UTC, it is necessary to use UTC_TIMESTAMP() instead of NOW(). – Alex Poca May 06 '17 at 16:57
  • That would work great but only for quick queries. This would be tricky if you want to make sure you're getting data precisely. For example, if I have a script that runs every hour, I can't just say get me the last hour data because the script might have slight variations in the time when it starts. So I have to set the time range from 13:00:00 to 13:59:59 instead of date > 1 hour ago when it is 2pm – phoenix Jun 08 '18 at 20:05
14

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')
phoenix
  • 1,629
  • 20
  • 11
8

it can be done easily using 

select count(*) from logins where datetime>= NOW()- INTERVAL 1 HOUR
Varun Nath
  • 5,570
  • 3
  • 23
  • 39
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);
Paul Schreiber
  • 12,531
  • 4
  • 41
  • 63
  • What if the date for a particular day needs to be printed ?. I dont wanna fetch date for the previous day, rather the present day dates only. Can you help me on this ? – Sap Mar 04 '15 at 06:50
0

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

(or Date_Sub() )

DRapp
  • 47,638
  • 12
  • 72
  • 142
0

You can also use CURDATE()function

SELECT
    count(*) AS TotalCount
FROM
    tblName
WHERE
    datetime >= DATE_SUB(CURDATE(), INTERVAL 1 HOUR)
Faisal
  • 4,591
  • 3
  • 40
  • 49