CODEWITHSUNDEEP
mysqlsql
Parham Doustdar
Parham Doustdar

Reputation: 2039

How can I get the number of records for today in five-minute intervals?

Assuming I have a column named creation_timestamp on a table named bank_payments, I would like to break today into five-minute intervals, and then query the database for the count in each of those intervals.

I'm going to read this manually (i.e. this is not for consumption by an application), so the output format does not matter as long as I can use it to get the five-minute time period, and the count of records in that period.

Is it possible to do this entirely on the side of the database?

Upvotes: 2

Views: 866

Answers (3)

Praveen
Praveen

Reputation: 9345

If you are expecting an output like 

|          begin_time |            end_time | cnt |
|---------------------|---------------------|-----|
|          2015-12-28 | 2015-12-28 00:05:00 |   1 |
| 2015-12-28 01:00:00 | 2015-12-28 01:05:00 |   4 |
| 2015-12-28 01:05:00 | 2015-12-28 01:10:00 |   1 |
| 2015-12-28 02:55:00 | 2015-12-28 03:00:00 |   4 |
| 2015-12-28 03:05:00 | 2015-12-28 03:10:00 |   1 |
| 2015-12-28 03:10:00 | 2015-12-28 03:15:00 |   1 |

Then

select
    begin_time,
    end_time,
    sum(case when creation_timestamp between begin_time and end_time then 1 else 0 end) cnt
from (
    select @r begin_time, @r := @r + INTERVAL 5 MINUTE end_time
    from (select @r := curdate()) r,
    (select 1 union all select 1 union all select 1 union all select 1) a1, #4
    (select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1) a2, #6 * 4 = 24
    (select 1 union all select 1 union all select 1 union all select 1) a3, #4 * 24 =  96
    (select 1 union all select 1 union all select 1) a4 #3 * 96 =  288
) x,
(select creation_timestamp from bank_payments) y
group by begin_time, end_time

If you only need those intervals having count > 0 then

select
    begin_time,
    end_time,
    sum(case when creation_timestamp between begin_time and end_time then 1 else 0 end) cnt
from (
    select @r begin_time, @r := @r + INTERVAL 5 MINUTE end_time
    from (select @r := curdate()) r,
    (select 1 union all select 1 union all select 1 union all select 1) a1, #4
    (select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1) a2, #6 * 4 = 24
    (select 1 union all select 1 union all select 1 union all select 1) a3, #4 * 24 =  96
    (select 1 union all select 1 union all select 1) a4 #3 * 96 =  288
) x,
(select creation_timestamp from bank_payments) y
group by begin_time, end_time 
having sum(case when creation_timestamp between begin_time and end_time then 1 else 0 end) > 0

sql fiddle demo

Upvotes: 1

Rahul Tripathi
Rahul Tripathi

Reputation: 172448

If you want to group by your records in table on 5 min interval then you can try this:

SELECT col1, count(col1), creation_timestamp
FROM bank_payments
WHERE DATE(`creation_timestamp`) = CURDATE()
GROUP BY UNIX_TIMESTAMP(creation_timestamp) DIV 300, col1

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Yes. Here is one method:

select sec_to_time(floor(time_to_sec(time(datetimecol)*5/60))), count(*)
from t
where t.datetimecol >= curdate() and
      t.dattimeecol < curdate() + interval 1 day
group by 1
order by 1;

Upvotes: 1

Related Questions