Group and count by minute of day using MySQL

Question

I want to get the count for each minute of the day from a large database of timestamps. I would like to be able build a graph from the data.

So there is 1440 minutes in a day, how would I get the count for each minute?

I can get the count for each minute within an hour,I'm stumped on how to expand to get the count for every minute in the day.

I've got this code that I'm using at the moment,

 SELECT FROM_UNIXTIME(
         CEILING(UNIX_TIMESTAMP(`timestamp`)/900)*900
                    ) AS timeslice
     , COUNT(*) AS mycount
  FROM visitor
 WHERE `timestamp` >= '20012-01-01'
   AND `timestamp`  < '20012-02-14'
GROUP BY timeslice

I hope this makes sense and thanks for any help!

Any questions please ask me

Dan

What do you want to do on days with 1380 or 1500 minutes? (Happens in my time zone.) — pilcrow, Feb 16 '12 at 02:57

Cheery · Answer 1 · 2012-02-16T03:54:39.607

Depending on the number of records you could try to use the following query (check its execution time).

select d.everyminute, 
      (select count(*) from visitor 
              where `timestamp` between d.everyminute and 
                                        d.everyminute + interval 59 second) cnt 
      from 
(
 select @rownum:=@rownum+1, 
       date('2012-02-15') + interval (@rownum-1) minute everyminute from

(select 0 union all select 1 union all select 2) t,

(select 0 union all select 1 union all select 2 union all select 3 union all 
 select 4 union all select 5 union all select 6 union all select 7 union all 
 select 8 union all select 9) t1,

(select 0 union all select 1 union all select 2 union all select 3 union all 
 select 4 union all select 5 union all select 6 union all select 7 union all 
 select 8 union all select 9) t2,

(select 0 union all select 1 union all select 2 union all select 3 union all 
 select 4 union all select 5 union all select 6 union all select 7 union all 
 select 8 union all select 9) t3,

(select @rownum:=0) r where @rownum < 24*60
) d

The result is (I put some timestamps into the test DB)

2012-02-15 00:00:00 0
2012-02-15 00:01:00 0
2012-02-15 00:02:00 2
2012-02-15 00:03:00 1
2012-02-15 00:04:00 0
2012-02-15 00:05:00 1
2012-02-15 00:06:00 0
2012-02-15 00:07:00 0
2012-02-15 00:08:00 0
2012-02-15 00:09:00 0
2012-02-15 00:10:00 0

and so on until 2012-02-15 23:59:00

the existing records had timestamps 2012-02-15 00:02:00, 2012-02-15 00:02:00, 2012-02-15 00:03:00 and 2012-02-15 00:05:00

ps: to take into account summer/winter time jump you can rewrite where @rownum < 24*60 as having everyminute < (date('2012-02-15') + interval 1 day - interval 1 minute)

As you can see, it counts every minute even when there are no records in db within this minute.

thanks very much, this was a very in-depth answer on a way to solve it! — dj10dj100, Feb 26 '12 at 04:53

score 2 · Answer 2 · answered Feb 16 '12 at 01:53

2

The easiest way to do this is create a utility table that already has minutes in it. If you're clever, you'd also put the hour and meridian.

You can find some more information by searching for datawarehouse time dimension.

answered Feb 16 '12 at 01:53

Brian Hoover

7,861
2
28
41

I ended up using your way, I created a separate table to pass the data into for each minute of the day! thanks – dj10dj100 Feb 26 '12 at 04:52

Mosty Mostacho · Accepted Answer · 2012-02-16T05:13:42.987

It looks like you don't want it grouped by day/month/year, just minutes:

select minute, sum(if(adate is null, 0, 1)) DateAmount from (
  select @num := @num + 1 as minute from
    (select 1  union all select 2  union all select 3  union all
     select 4  union all select 5  union all select 6  union all
     select 7  union all select 8  union all select 9  union all
     select 10 union all select 11 union all select 12) as t1,
    (select 1  union all select 2  union all select 3  union all
     select 4  union all select 5  union all select 6  union all
     select 7  union all select 8  union all select 9  union all
     select 10 union all select 11 union all select 12) as t2,
    (select 1  union all select 2  union all select 3  union all
     select 4  union all select 5  union all select 6  union all
     select 7  union all select 8  union all select 9  union all
     select 10) as t3,
    (select @num := - 1) as t4
) as MinuteInDay
left join visitors v
on minute(v.aDate) = MinuteInDay.minute
group by minute

For this set of data:

+---------------------+
|        aDate        |
+---------------------+
| 2012-01-01 00:00:00 |
| 2012-01-01 00:01:00 |
| 2012-01-01 00:02:00 |
| 2012-01-01 00:02:00 |
| 2012-01-02 00:02:00 |
| 2012-01-03 00:03:00 |
+---------------------+

This will result in:

+--------+------------+
| MINUTE | DATEAMOUNT |
+--------+------------+
| 0      | 1          |
| 1      | 1          |
| 2      | 3          |
| 3      | 1          |
| 4      | 0          |
| 5      | 0          |
| ...    | ...        |
| 1439   | 0          |
+--------+------------+

Hope this helps.

PS: It would be much easier if you don't needed the minutes in 0 and just ommit them!

thanks very much, this was a very in-depth answer on a way to solve it! — dj10dj100, Feb 26 '12 at 04:53

Joshua Martell · Answer 4 · 2012-02-16T04:16:20.160

1

I'd do something like this:

SELECT MOD(FLOOR(UNIX_TIMESTAMP(timestamp)/60), 1440) AS timeslice, COUNT(*) AS mycount FROM visitor WHEREtimestamp>= '20012-01-01' ANDtimestamp< '20012-02-14' GROUP BY timeslice

It'll count batches of 1440 minutes since the unix epoch, ignoring daylight savings time, etc.

edited Feb 16 '12 at 04:16

answered Feb 16 '12 at 03:48

Joshua Martell

7,074
2
30
37

Group and count by minute of day using MySQL

4 Answers4