2

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?

Community
  • 1
  • 1
Rodolfo Berríos
  • 43
  • 1
  • 6
  • 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 Jun 24 '12 at 06:31
  • yes, is stored using datetime – Rodolfo Berríos Jun 24 '12 at 07:00

1 Answers1

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

user1474090
  • 675
  • 6
  • 5
  • 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. – Zane Bien Jun 24 '12 at 06:52
  • Zane is right, this is the actual COUNT: `COUNT(IF(date >= DATE_SUB(NOW(), INTERVAL 1 HOUR), 1, NULL) ) AS hourHits,` – Rodolfo Berríos Jun 24 '12 at 07:22