SQL Query to check rate limit

Lets say I have a table of messages that users have sent, each with a timestamp.

I want to make a query that will tell me (historically) the most number of messages a user ever sent in an hour.

So i开发者_运维知识库n other words, in any given 1 hour period, what was the most number of messages sent.

Any ideas?

Assuming timestamp to be a DATETIME - otherwise, use FROM_UNIXTIME to convert to a DATETIME...

For a [rolling] count within the last hour:

  SELECT COUNT(*) AS cnt
    FROM MESSAGES m
   WHERE m.timestamp BETWEEN DATE_SUB(NOW(), INTERVAL 1 HOUR) 
                         AND NOW()
GROUP BY m.user
ORDER BY cnt DESC
   LIMIT 1

If you want a specific hour, specify the hour:

  SELECT COUNT(*) AS cnt
    FROM MESSAGES m
   WHERE m.timestamp BETWEEN '2011-06-06 14:00:00'
                         AND '2011-06-06 15:00:00'
GROUP BY m.user
ORDER BY cnt DESC
   LIMIT 1

Need more details on table structure etc. but something like:

  select date(timestmp), hour(timestmp) , count(*) 
    from yourtable group by date(timestmp) , hour(timestmp)
    order by count(*) DESC
    limit 100; 

would give you hte desired result.

Something like this should work:

SELECT MAX(PerHr) FROM 
  (SELECT COUNT(*) AS PerHr FROM messages WHERE msg_uid=? 
   GROUP BY msg_time/3600) t

I suspect this would be horribly slow, but for an arbitrary historical max hour, something like this might work (downvote me if I'm way off, I'm not a MySQL person):

SELECT base.user, base.time, COUNT(later.time)
FROM messages base
INNER JOIN messages later ON later.time BETWEEN base.time AND DATE_ADD(base.time, INTERVAL 1 HOUR) AND base.user = later.user
WHERE base.user = --{This query will only work for one user}
GROUP BY base.user, base.time
ORDER BY COUNT(later.time) DESC
LIMIT 1