MySQL count rows in multiple date ranges?

Question

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?

How do you currently store this information? Is it in a datetime field? You should be able to select pertinent parts using datetime functions and grouping. Providing structure for what you have and the result you want would help greatly. — Ilion
– Ilion, Commented Jun 24, 2012 at 6:31

user1474090 · Accepted Answer · 2012-06-24 10:02:37Z

4

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

edited Jun 24, 2012 at 10:02

answered Jun 24, 2012 at 6:44

user1474090

6756 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Zane Bien Over a year ago

You'll want to use NULL as the false condition since COUNT() will still count 0 into its calculation, otherwise, use SUM(). Also, I believe < should be switched to > or it will count all hits that weren't within the hour.

Rodolfo Berríos Over a year ago

Zane is right, this is the actual COUNT: COUNT(IF(date >= DATE_SUB(NOW(), INTERVAL 1 HOUR), 1, NULL) ) AS hourHits,

Collectives™ on Stack Overflow

MySQL count rows in multiple date ranges?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related