CODEWITHSUNDEEP
mysqldatecountrange
Rodolfo Berríos
Rodolfo Berríos

Reputation: 43

MySQL count rows in multiple date ranges?

I want to count the rows in several date ranges (i.e: last hour, today, this week, last 30 days) from a given table.

I need to know how many entries are in this time/date periods to be able to tell if a given user has reach the limit for each one of this ranges. For instance, a user can have max 300 entries one month but with a (hourly/daily/weekly/monthly) limit.

So far I'm trying with a subquery approach using a SELECT CASE similar to this one: group by range in mysql

Which should be the best way of doing this?

Upvotes: 2

Views: 3974

Answers (1)

user1474090
user1474090

Reputation: 675

In mysql you could use a series of count functions with if statements so that only the required dates are counted, like so.

SELECT COUNT(IF(date >= DATE_SUB(NOW(), INTERVAL 1 HOUR), 1, null)) AS hourHits,

and so on

Edited as per comments

Upvotes: 4

Related Questions