Fetching rows added last hour

MysqlDatetimeDate Range

Mysql Problem Overview

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?

Mysql Solutions

Solution 1 - Mysql

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 - Mysql

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 - Mysql

it can be done easily using

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

Solution 4 - Mysql

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 - Mysql

You can also use CURDATE()function

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

Solution 6 - Mysql

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

(or Date_Sub() )

Categories
Recommended Mysql Solutions
Recommended Datetime Solutions
Recommended Date Range Solutions

Previous Solution:

Sharing a complex object between processes?

PythonProcessMultiprocessingSharing

Next Solution:

How to configure log4j to only keep log files for the last seven days?

JavaLoggingLog4j

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionMichael GronsView Question on Stackoverflow
Solution 1 - MysqlSDReyesView Answer on Stackoverflow
Solution 2 - MysqlphoenixView Answer on Stackoverflow
Solution 3 - MysqlVarun NathView Answer on Stackoverflow
Solution 4 - MysqlPaul SchreiberView Answer on Stackoverflow
Solution 5 - MysqlFaisalView Answer on Stackoverflow
Solution 6 - MysqlDRappView Answer on Stackoverflow