6

I'm logging queries which have been sent to my API like this:

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 much queries have been made in the last 60 minutes, 24 hours and 30 days and group it like that:

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

Is this possible to do in one query?

Edit:

I've used the suggestions from here and played a bit with the 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 a lot for your help guys!

3 Answers3

8
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

juergen d
  • 195,137
  • 36
  • 275
  • 343
  • 1
    I think your logic is backwards here. You want `timestamp >= now() - interval 1 hour`. See this [SQL Fiddle](http://sqlfiddle.com/#!2/48502/9). – AdamMc331 Jan 23 '15 at 15:32
8
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...

paul
  • 21,148
  • 1
  • 51
  • 54
  • Beat me to it. Far more efficient than using sub queries. – Kirk Logan Jan 23 '15 at 14:36
  • Are you sure this works? I tried it in SQL Fiddle and it didn't produce the expected results. – AdamMc331 Jan 23 '15 at 15:29
  • Actually this shouldn't work, because nothing should be greater than `now()` unless your table is recording the future. – AdamMc331 Jan 23 '15 at 15:40
  • 1
    edited, now reads `timestamp + interval` rather than `timestamp - interval` – paul Jan 23 '15 at 15:42
  • That fixed the one issue, but now it doesn't protect against future dates. If OP has 01/24/2016 in there table, it is returned in this result set. Not being nit picky, just testing in SQL Fiddle trying to get the right one. – AdamMc331 Jan 23 '15 at 15:50
  • edited again, should not count any future events now – paul Jan 23 '15 at 15:53
0

Another neat trick you can use here is the SUM() function with just a boolean expression inside of it. When you do that, MySQL will effectively count the number of rows that meet the condition. So, by using something like:

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

It will just count the number of rows that have a timestamp within 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;

It worked for me in SQL Fiddle.

EDIT

The above solution doesn't work if your table does have future dates, so if you want to ensure that you're only getting values in 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 record to show that it works.

AdamMc331
  • 15,982
  • 9
  • 67
  • 128