CODEWITHSUNDEEP
mysql
bryanh
bryanh

Reputation: 13

MYSQL Query Count in time interval

My goal is to be able to graph the number of calls every X (5 minutes, or weekly or monthly) intervals and if there are no calls, indicate that as well. In the following example the interval is every 5 minutes. call_date and called_num are both fields in a mysql table. Call_interval and num_calls are what I am trying to extract with a MYSQL Query, and call_interval is NOT in my database table (does it need to be??).

What should my MYSQL QUERY look like?

Here is what I have, but I am unable to retrieve the time intervals and 0s in the result.

SELECT Count(*) AS num_calls 
FROM cdr 
WHERE DATE(call_date) = CURDATE() 
GROUP BY ( 12 * HOUR( call_date ) + FLOOR( MINUTE( call_date ) / 5 ))

mysql table:
    call_date               called_num
    3/26/2015 8:31:27 AM    555-987-6543
    3/26/2015 8:32:27 AM    555-987-6544
    3/26/2015 8:34:27 AM    555-987-6545
    3/26/2015 8:35:27 AM    555-987-6546
    3/26/2015 8:36:27 AM    555-987-6547
    3/26/2015 8:51:27 AM    555-987-6548
    3/26/2015 8:55:27 AM    555-987-6549

ideal mysql result:

    call_interval           num_calls
    3/26/2015 8:30:00 AM        3
    3/26/2015 8:35:00 AM        2
    3/26/2015 8:40:00 AM        0
    3/26/2015 8:45:00 AM        0
    3/26/2015 8:50:00 AM        1
    3/26/2015 8:55:00 AM        1

     ..... and so on 

     from 3/26/2015 00:00:00 AM 
     to   3/26/2015 11:55:00 PM

Thank You.

Upvotes: 1

Views: 458

Answers (1)

Mike Brant
Mike Brant

Reputation: 71384

The the problem you will face is that in the absence of a table that provides all the different interval values, you have no way to identify intervals where there are not entries. The data table only knows about data that it there, not data that doesn't exist.

A typical data warehouse type of solution to this is to have date and time dimensions tables that can be used to express all known date and time values (increments) that you may want to use in your application. In you main data table, you then write foreign keys to these tables rather than (or perhaps in addition to) the actual timestamp data you capture.

You then left join the date and time tables against the main data table when trying to query for certain intervals.

This can obviously mean a pretty drastic schema change. So the other option you have is to simply query the data according to your desired interval and then slot those count results into a data structure in your application that holds all possible interval values.

Upvotes: 2

Related Questions