How to get data for the last hour, last day and last month with one request?

Question

I am logging requests that have been sent to my API as follows:

id | timestamp ----+--------------------- 1 | 2015-01-19 18:01:47 2 | 2015-01-19 20:41:37 3 | 2015-01-20 14:15:06 4 | 2015-01-21 13:02:51 5 | 2015-01-23 05:02:12 

Now I need to know how many requests have been made in the last 60 minutes, 24 hours and 30 days and group them like this:

 HOURLY | DAILY | MONTHLY -------+-------+-------- 0 | 1 | 5 

Can this be done in one request?

Edit:

I used the suggestions here and worked a bit with logical things until it worked the way I want it to.

 SELECT SUM(CASE WHEN DATE_SUB(NOW(), interval 1 HOUR) <= `TIMESTAMP` THEN 1 ELSE 0 END) HOURLY, SUM(CASE WHEN DATE_SUB(NOW(), interval 1 DAY) <= `TIMESTAMP` THEN 1 ELSE 0 END) DAILY, SUM(CASE WHEN DATE_SUB(NOW(), interval 1 MONTH) <= `TIMESTAMP` THEN 1 ELSE 0 END) MONTHLY FROM `REQUESTS`; 

Thanks so much for helping the guys!

Answer 1

 select sum(case when timestamp between now() - interval 1 hour and now() then 1 else 0) hourly, sum(case when timestamp between now() - interval 1 day and now() then 1 else 0) daily, sum(case when timestamp between now() - interval 1 month and now() then 1 else 0) monthly from your_table 

edited ...

Answer 2

 select sum(timestamp >= now() - interval 1 hour) as hour, sum(timestamp >= now() - interval 1 day) as day, sum(timestamp >= now() - interval 1 month) as month from your_table 

SQLFiddle demo

Answer 3

Another neat trick you can use here is the SUM() function, which contains only the logical expression inside it. When you do this, MySQL will efficiently count the number of rows matching the condition. So using something like:

 SUM(timeCol >= (NOW() - INTERVAL 1 HOUR)) 

It will simply count the number of rows that have a timestamp in the last hour. Try this query:

 SELECT SUM(timecol >= (NOW() - INTERVAL 1 HOUR)) AS hourly, SUM(timeCol >= (NOW() - INTERVAL 1 DAY)) AS daily, SUM(timeCol >= (NOW() - INTERVAL 1 MONTH)) AS monthly FROM myTable; 

This worked for me in SQL Fiddle .

EDIT

The above solution does not work if your table has future dates, so if you want you to get only values ​​for the last hour, day or month that do not exceed the current time, just add a where clause:

 SELECT SUM(timecol >= (NOW() - INTERVAL 1 HOUR)) AS hourly, SUM(timeCol >= (NOW() - INTERVAL 1 DAY)) AS daily, SUM(timeCol >= (NOW() - INTERVAL 1 MONTH)) AS monthly FROM myTable WHERE timeCol <= NOW(); 

Here is an updated Fiddle that has an invalid entry to show that it works.